Good morning,
I have an active report which uses the "Summarize in Output" automated total to summarize a crosstab based on various parameters. The problem is that two of the objects are a % calculation, not a total and I've been unable to add the correct calculation to that line.
Here is an example
Prompt: Select Segment A, B, C
Crosstab:
| Revenue | Labor Cost | Margin | Margin % (Labor/ Rev)
Segment A
Segment B
Segment C
Total summarized in output based on prompt.
Everything is correct except the Margin % adds the numbers instead of doing the % calc
Thanks all for your insight! :)
Tess
Quote from: Tess on 03 Dec 2015 10:26:06 AM
Good morning,
I have an active report which uses the "Summarize in Output" automated total to summarize a crosstab based on various parameters. The problem is that two of the objects are a % calculation, not a total and I've been unable to add the correct calculation to that line.
Here is an example
Prompt: Select Segment A, B, C
Crosstab:
| Revenue | Labor Cost | Margin | Margin % (Labor/ Rev)
Segment A
Segment B
Segment C
Total summarized in output based on prompt.
Everything is correct except the Margin % adds the numbers instead of doing the % calc
Thanks all for your insight! :)
Tess
It's probably not the answer you're hoping to get, but here it is anyway :) There is only a limited selection of "Summarize in output" summaries available. The one you need here is "Calculated", but this isn't in the list, sadly. This means you're not going to be able to do it this way.
The alternative is to add a data deck driven by the value(s) you are currently filtering on in the crosstab. Add the crosstab to the data deck, and use a master/detail relationship to link it to the data deck. Change your Active Report controls so that they Select the data deck (instead of Filter the crosstab). Change the crosstab summary/summaries to regular ones instead of "Summarize in output", and use the Calculated summary for your percents.
Cheers!
MF.
Hi Tess
If you want your total '% Margin' must edit the "total Summarized in output"
before = total(currentMeasure within set ....)
must be = aggregate(currentMeasure within set ....)
Quote from: MFGF on 04 Dec 2015 05:18:59 AM
It's probably not the answer you're hoping to get, but here it is anyway :) There is only a limited selection of "Summarize in output" summaries available. The one you need here is "Calculated", but this isn't in the list, sadly. This means you're not going to be able to do it this way.
The alternative is to add a data deck driven by the value(s) you are currently filtering on in the crosstab. Add the crosstab to the data deck, and use a master/detail relationship to link it to the data deck. Change your Active Report controls so that they Select the data deck (instead of Filter the crosstab). Change the crosstab summary/summaries to regular ones instead of "Summarize in output", and use the Calculated summary for your percents.
Cheers!
MF.
Do you know if we will be able to use a calculated total in the new version? It seems like I should be able to just create a total object and insert it into that line like in a regular report, but like you said - to get the correct answer you have to do a complicated work around (that sucks up processing time).
Quote from: Tess on 12 Jan 2016 02:07:41 PM
Do you know if we will be able to use a calculated total in the new version? It seems like I should be able to just create a total object and insert it into that line like in a regular report, but like you said - to get the correct answer you have to do a complicated work around (that sucks up processing time).
Hi,
By "new version" do you mean Cognos Analytics? Sadly the answer is no - active reports have the same functionality in Cognos Analytics (Cognos 11) as in Cognos 10.2.2).
Cheers!
MF.