Summary for Calculated Columns

[mindbender]

Hi,

I have a calculated column which consists of a division : Measure2/Measure1 which has been formatted as a percentage value.

When i`m summarizing this column , i`m gettting the value as 0.00% even though Measure1 and Measure 2 both have values .. to illustrate :

                  Measure1     Measure2       Calculation : Msure2/Msure1
                       100                    20                      20.00%
                         25                     10                     40.00%
Summary         125                    30                     0.00%

Instead of 24%

I`ve kept the aggregation as Automatic.

What could be the solution

[workdan]

I've found the following solutions for summarizing calculated columns:

1. Set the column's Roll-up Aggregation Type to "Calculated"

If that doesn't work, try

2. Set the column's Aggregation to Custom (under the Aggregation button on the toolbar) and enter an expression like this: "TOTAL([Msure2]) / TOTAL([Msure1])". If you are doing this at the group level, you can use TOTAL([Msure2] FOR [Group Name])

I believe solution 1 should work if the data comes from one query, but solution 2 is needed if you're using data from joined queries. I'm not sure that's true, but it seems to be what I've experienced.

Another thing to keep in mind (if you're not doing this already) is that Cognos will generate an error (I think) if it encounters a division by zero in a calculated column. Whenever I do division calculations I throw in a check for zeroes.

Hope this helps!

Dan

[mindbender]

Thanks , I tried using both your solutions but they dont seem to work despite having a checking for 0 using the filter function and by explicitly filtering for Measure<>0

Its still giving me the #!Error problem

[MDXpressor]

What is your source data?  Is this Relational or Dimensional data?

[mindbender]

I`m sorry I shud`ve mentioned that earlier ... We`re referring to dimensional data i.e the source of data is a cognos cube.

[ole115]

I had something similar in my report and I filed case with Cognos. Here is what I had: I have Report Studio report that is based on PowerPlay cube data source. I have nested levels as my rows and multiple measures as my columns, one of them is percentage calculation. When I create subtotals for each level, percentage is not calculating properly. I followed KB document 1022193.1, which describes my problem. But after I tried to add aggregate summary and ran the report, I got the following error message: OP-ERR-0025 The following OLAPPlanner internal error occurred: 'OPASSERT(OQP_DYNAMIC_CAST(OPExpressionItem*,pAggr->GetChild(1))) in OPExpressionItem::ExtractMeasureFromCalcMeasureForContext at OLAPPlannerExpression.cpp:2317'.
Cognos confirmed that it is a bug and there is no workaround for that. You can try to look at the document that I referenced above, but I am afraid it is an issue with cube sources.

[MDXpressor]

measure1/measure2 needs to have a higher solve order than that of the Summary line.  Have you set any solve orders for this query?  Default solve order is 0.  0's are processed first, then 1's, then 2's, and so on.

Give it a shot and let me know.

[Chicken Head]

I've found the following solutions for summarizing calculated columns:

1. Set the column's Roll-up Aggregation Type to "Calculated"

If that doesn't work, try

2. Set the column's Aggregation to Custom (under the Aggregation button on the toolbar) and enter an expression like this: "TOTAL([Msure2]) / TOTAL([Msure1])". If you are doing this at the group level, you can use TOTAL([Msure2] FOR [Group Name])

I believe solution 1 should work if the data comes from one query, but solution 2 is needed if you're using data from joined queries. I'm not sure that's true, but it seems to be what I've experienced.

Another thing to keep in mind (if you're not doing this already) is that Cognos will generate an error (I think) if it encounters a division by zero in a calculated column. Whenever I do division calculations I throw in a check for zeroes.

Hope this helps!

Dan

Thank you very much for this post, i've had trouble with this for a couple of days. The second suggestion was perfect.

[ramreddysb]

Solution 2 worked for me. I set the column aggregation to calculated though. Thanks a lot for the solution.

2. Set the column's Aggregation to Custom (under the Aggregation button on the toolbar) and enter an expression like this: "TOTAL([Msure2]) / TOTAL([Msure1])". If you are doing this at the group level, you can use TOTAL([Msure2] FOR [Group Name])

[Imre]

It also worked for me in Cognos 10 this way with Aggregation set to Calculated. (I haven't found then 'Custom' option)

[Goodholiday]

I have a slightly different problem. I have a calculated median field for different locations. I used a crosstab in my dashboard. The crosstab looks like below:

              Latest 12M median time    Previous 12M median time
Location 1        a1                             b1
Location 2        a2                             b2
Location 3        a3                             b3
...
Location n        an                             bn
Summary           S1                             S2

All results are correct for Location 1, 2, 3 and n, but the results for summary are wrong. I'm not sure what caused the issue. My version is 11.1.7. Any suggestion for troubleshot? Thanks.

[bus_pass_man]

calculated median field = a calculation?

the results for summary are wrong.

Can you elaborate on what you mean by wrong? 

What's the setting for calculate after aggregation?

[Goodholiday]

I meant that the displayed results for Summary are wrong, comparing the results I got from Excel using the same data. For example, the results for Summary from Excel are 20 and 23 for Latest and previous 12M median time respectively, however in the Cognos Dashboard, it may show 18 and 22 respectively. I don't know how Summary is calculated in Cognos dashboard.

[Goodholiday]

calculated median field = a calculation? Yes, it's a field calculation using a formula

 Can you elaborate on what you mean by wrong? 

What's the setting for calculate after aggregation?

The settting is below:
Aggregate: Calculated (this is greyed out)
Data type: Decimal (you can't change it)
Represents: Default, you can change this to geographic location or time. I changed this to time and it wouldn't make any difference to the results.