If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Summary for Calculated Columns

Started by mindbender, 10 Apr 2007 09:17:43 AM

Previous topic - Next topic

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?
No, a proof is a proof. What kind of a proof? It's a proof. A proof is a proof, and when you have a good proof, it's because it's proven.

-Jean Chretien

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.
No, a proof is a proof. What kind of a proof? It's a proof. A proof is a proof, and when you have a good proof, it's because it's proven.

-Jean Chretien

Chicken Head

Quote from: WorkDan on 10 Apr 2007 11:06:02 AM
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)