Author Topic: Summary for Calculated Columns  (Read 17273 times)

Offline mindbender

  • Full Member
  • ***
  • Join Date: Mar 2007
  • Posts: 12
  • Forum Citizenship: +0/-0
Summary for Calculated Columns
« on: 10 Apr 2007 09:17:43 am »
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


Offline WorkDan

  • Full Member
  • ***
  • Join Date: Nov 2006
  • Posts: 22
  • Forum Citizenship: +0/-0
Re: Summary for Calculated Columns
« Reply #1 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

Offline mindbender

  • Full Member
  • ***
  • Join Date: Mar 2007
  • Posts: 12
  • Forum Citizenship: +0/-0
Re: Summary for Calculated Columns
« Reply #2 on: 17 Apr 2007 04:48:09 am »
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

Offline MDXpressor

  • Global Moderator
  • Community Leader
  • *****
  • Join Date: Jan 2007
  • Posts: 219
  • Forum Citizenship: +16/-2
Re: Summary for Calculated Columns
« Reply #3 on: 17 Apr 2007 08:34:42 am »
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

Offline mindbender

  • Full Member
  • ***
  • Join Date: Mar 2007
  • Posts: 12
  • Forum Citizenship: +0/-0
Re: Summary for Calculated Columns
« Reply #4 on: 18 Apr 2007 01:15:07 am »
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.

Offline ole115

  • Full Member
  • ***
  • Join Date: Oct 2005
  • Posts: 8
  • Forum Citizenship: +0/-0
Re: Summary for Calculated Columns
« Reply #5 on: 18 Apr 2007 04:48:51 pm »
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.

Offline MDXpressor

  • Global Moderator
  • Community Leader
  • *****
  • Join Date: Jan 2007
  • Posts: 219
  • Forum Citizenship: +16/-2
Re: Summary for Calculated Columns
« Reply #6 on: 18 Apr 2007 10:37:04 pm »
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

Offline Chicken Head

  • Full Member
  • ***
  • Join Date: Nov 2005
  • Posts: 14
  • Forum Citizenship: +0/-0
Re: Summary for Calculated Columns
« Reply #7 on: 24 Jan 2008 08:30:48 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.

Offline ramreddysb

  • Associate
  • **
  • Join Date: Mar 2016
  • Posts: 1
  • Forum Citizenship: +0/-0
Re: Summary for Calculated Columns
« Reply #8 on: 08 Apr 2016 03:59:39 pm »
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])

Offline Imre

  • Associate
  • **
  • Join Date: Mar 2019
  • Posts: 4
  • Forum Citizenship: +0/-0
Re: Summary for Calculated Columns
« Reply #9 on: 11 Jul 2019 03:29:41 am »
It also worked for me in Cognos 10 this way with Aggregation set to Calculated. (I haven't found then 'Custom' option)

 



       
Twittear