September 16, 2014 at 10:27 am
Hello all,
Having a little problem here with a simple percentage value i need to include in a matrix from a column total against a total from another dataset.
Have a table:
T1:
FEP| LocalBus
January| 5,775|9,290
February| 5,923|9,796
March| 6,113|11,103
April | 5,163|11,021
May | 6,119|10,838
June | 9,665|14,006
July | 6,644|14,969
August| 5,694|10,174
T1 Total | 51,096|91,197
% | 4%| 4%
T2:
T2 Total | 1,236,827|3,527,875
I need the % values from T1 based on the T1 Total to be based on the percentage from T2 Total. I used the following expression:
= SUM(Fields!Count.Value, "T1 Dataset")/SUM(Fields!Count.Value, "T2 Dataset")
But the percentage values across the table are all the same no matter what, in this case 4%.
Any ideas why and if a better way to do this.
Thanks!
R.
September 16, 2014 at 10:45 am
If you cant resolve this via SQL code or use any kind of Groupings in the rdl then you could try something like this "Psuedo Code"
[Code="sql"]SUM(IIF(Fields!Month.Value = "January" , SUM(Fields!Count.Value, "T1 Dataset"), 0))/SUM(IIF(Fields!Month.Value = "January" , SUM(Fields!Count.Value, "T2 Dataset"), 0)) [/code]
***SQL born on date Spring 2013:-)
September 16, 2014 at 11:32 am
I'm not sure I understand what you want for a final outcome.
Do you want to show a % per month of the total from T2 like:
January | 5,775 | 9,290 | 0.47% (5775/1236827) | 0.26% (9290/3527875)
...
Total | 51,096 | 91,197 | 4.13% (51096/1236827) | 2.59% (91197/3527875)
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 16, 2014 at 12:09 pm
Thanks for the reply!
You are correct! I need to Total per column (FEP) percentages :
Total FEP for the Year to date (50,072) divided by T2 Total (1,226,640) for each colum (FEP/LocalBus, etc)
AKA: 50,072/1,226,640 | 89,861/3,551, 902
Right now they are all showing me 4% across all different columns when i try to use the expression i used before.
Thank you for the help!
September 16, 2014 at 12:18 pm
Do you need the percentage only for the total row or for each detail row?
Is the data region a table or a matrix?
Can you post what the data in the data sets look like along with your queries?
Your current expression is going to return the same thing because you have no criteria telling it what to sum by? It is summing the entire result set for each data set and then doing the division.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 16, 2014 at 12:18 pm
One way to do it might be to store the T1 total in a variable, and then just divide by that variable in your T2 tablix.
September 16, 2014 at 1:12 pm
hi,
I just need the percentage for the total row. Both data regions are Tablix. Basically the SUM of year to day TOTAL divided by the T2 Total. They both sit on two separate tablix.
Thanks!
R
September 16, 2014 at 1:19 pm
If you just need the one result percentage then use a calculated field possible?
***SQL born on date Spring 2013:-)
September 16, 2014 at 1:27 pm
Calculated fields do not allow you to use aggregations on other fields from the same data set. Tried an failed.
September 16, 2014 at 1:32 pm
I could have sworn I have done this before. Let me look at some of my old rdl's. I don't think I used look up either. I may have done it inside just a plain text box. I'll look
***SQL born on date Spring 2013:-)
September 17, 2014 at 6:28 am
I think the way to do it is:
FEP:
=ReportItems!T1_FEPTotalTextBoxName.Value/ReportItems!T2_FEPTotalTextBoxName.Value
LocalBus:
=ReportItems!T1_LocolBusTotalTextBoxName.Value/ReportItems!T2_LocalBusTotalTextBoxName.Value
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 17, 2014 at 10:23 am
Unfortunately this is not possible with a Matrix and the ReportItems collection does not go outside the scope of the current data region. 🙁
September 17, 2014 at 10:37 am
RenzoSQL (9/17/2014)
Unfortunately this is not possible with a Matrix and the ReportItems collection does not go outside the scope of the current date region. 🙁
That really makes ReportItems a lot less useful, doesn't it, especially since each item on a report is required to have a name unique to the report, not the data region.
I think you may have to go with the Lookup function to get the T2 Total.
I'm still not sure that we have enough details to come up with a solution, because you have to get your 2 sums to be limited to the grouping you are working with as well.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 17, 2014 at 10:49 am
Jack Corbett (9/17/2014)
RenzoSQL (9/17/2014)
Unfortunately this is not possible with a Matrix and the ReportItems collection does not go outside the scope of the current date region. 🙁That really makes ReportItems a lot less useful, doesn't it, especially since each item on a report is required to have a name unique to the report, not the data region.
I think you may have to go with the Lookup function to get the T2 Total.
I'm still not sure that we have enough details to come up with a solution, because you have to get your 2 sums to be limited to the grouping you are working with as well.
It is a bit complicated to try to accomplish what I want with the detail data that i currently get from the SQL. I am thinking about trying to find a way to get the main total from the SQL query and incorporate it in the first dataset. I will look into the Lookup as well.
Thank you very much for your help!
September 17, 2014 at 11:36 am
Not sure why its giving you a hard time. I had built a dashboard that compares the current year vs the past year two different datasets. Here is from one of my cells
=CountDistinct(Fields!HCPC_Enabling_pt.Value, "dsMain")/CountDistinct(Fields!HCPC_Enabling_pt.Value, "dsPast") for some reason I have no problem with using two datasets.
***SQL born on date Spring 2013:-)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply