Incorrect Summary for calculated field in Top 5 report

[CognosAdmn]

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

[nblank]

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

[CognosAdmn]

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

[spmaganti]

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

[CognosAdmn]

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.

[CognosAdmn]

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?

[CognosAdmn]

anyone please....