Hello All,
Could you please help me out with the following issue:
I've got a table similar to the one shown at the bottom, which is displaying the
Top 5 OccurrenceType by Cases.
I have the two Measures: [Cases] and [Expected]. I need the sum of Cases. The problem is that the number of [Cases] for an [OccurrenceType] has to be set to zero when the second measure
[Expected] is blank. I wrote a Custom query calculation ([Cust_Cases]) which looks like the following:
If ([Expected] is null) then (0) else ([Cases]) When I do a total on the [Cust_Cases] I am receiving the same total value as the total of [Cases] field. It seems to ignore my zeroes. Could someone please tell me why it is and maybe suggest a solution?
OccurrenceType | Cases | Expected | Cust_Cases |
A | 50 | 25 | 50 |
B | 30 | | 0 |
C | 30 | 20 | 30 |
D | 25 | 30 | 25 |
E | 15 | | 0 |
------- | ------- | ------- | ------- |
Total | 150 | 75 | 150 ?? should be 105 |
Hi,
Try AGGREGATE instead of TOTAL
or
Use your case in the Framework Manager project to create an extra measure instead of using the case in the report.
Regards,
Nanno
Hi Nanno, Thank you for taking the time to help me out.
I tried aggregate and its still giving me the same as Total.....Actually, Aggregate is what i used initially.....If i do a total then i get a total of all OccurrenceTypes, even the ones not in Top5.
Your second option sounds like a good solution. The problem is that I have about 10 measures, similar to Expected that the Cases field depends on. Not sure if my FM Modeller would be cool with adding so many fields for the purpose of this one report....But I'll give it a shot.
Thanks again Nanno.....
Any other suggestions to solve this in Report studio will be greatly appreciated.... :) ;) :D
What's the data type of [Expected]? Please also try the following:
1. if([Expected] is missing)?
2. Set the "Solve Order" property?
3. Do an "Automatic Summary" rather than "Total" or "Average"?
My 2 Cents
Thanks a million for your input Coggie.
the data type [Expected] is a number with two decimals.
I have tried your suggestions below; but it does not make a difference; still getting the incorrect answer. I've also tried starting from scratch again and taking your route, but still not working.....
I tried a running-total on the [Cust_Cases] field, which adds up properly. :-\
OccurrenceType | Cases | Expected | Cust_Cases | Rolling-Total_Cust_Cases |
A | 50 | 25 | 50 | 50 |
B | 30 | | 0 | 50 |
C | 30 | 20 | 30 | 80 |
D | 25 | 30 | 25 | 105 |
E | 15 | | 0 | 105 |
------- | ------- | ------- | ------- | ------- |
Total | 150 | 75 | 150 ?? should be 105 | |
Thanks again Coggie.
ultimately what i am trying to achieve is: In the summary field I need to display the average of [Expected] , excluding Cases when [Excepted] is blank what my report is doing now:
OccurrenceType | Cases | Expected |
A | 50 | 25 |
B | 30 | |
C | 30 | 20 |
D | 25 | 30 |
E | 15 | |
------- | ------- | ------- |
Total | 150 | 17.33 |
17.33 = ((50 * 25) + (30 * 0) + (30 * 20) + (25 * 30) + (15 * 0) )/
150 <== WRONG
What my report is supposed to display:
OccurrenceType | Cases | Expected |
A | 50 | 25 |
B | 30 | |
C | 30 | 20 |
D | 25 | 30 |
E | 15 | |
------- | ------- | ------- |
Total | 150 | 24.76 |
24.76 = ((50 * 25) + (30 * 0) + (30 * 20) + (25 * 30) + (15 * 0) )/ (
150-30-15) <== CORRECT
Could you please tell me how to achieve this?
anyone please....