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

[c8] - Idea about running percentage in Report Studio.

Started by Suraj, 31 Aug 2006 10:05:57 AM

Previous topic - Next topic

Suraj

 ???
Hi all,
I am having an issue here.
I have a list report (I'll try crosstab later if its not possible with list report)
that has Division, Region, Territory and Amount columns.
Division has one or more region andÃ,  Region has one or more territory.
Amount is sub-totaled depending on region and division.
I'm trying to get a percentage of amount depending on the group level.
So the percentage column should be like this.
Suppose Div1 has Reg1 and Reg2.
Similarly, Reg1 has Ter1,Ter2,Ter3 and Reg2 has Ter1,Ter2.
Percentage column should give % for territory for total of that region.
Percentage for region should give % for total of that division.
Percentage for division should give % for total of report.

I can get one level of percentage with this formula, Amount/Total_Amount
but that doesn't give % depending on each group level.
It just gives percentage of each territory depending on the Total for report.

I tried Amount/Region_Total and that messes up in division level.

Is there a way that gives % dynamically depending on the grouping?
thank you kindly for any suggestions...
--
suraj

Blue

I believe the nature of a cross-tab report will aggregate values into the row/column headers selected. 

If you want to display duplicates either you have to introduce another row/column distinction or create a standard list report.
Robert Edis
Principal
Robert Edis Consulting
Rotorua, New Zealand

MFGF

If you want to do this in a list report, try the following approach:

Bring in Division, Region, Territory and Amount items to your list.
Group on Division and Region.
Select the Amount column, and use the Aggregate button to add totals to the group footings.

From the toolbox, drag in a Query Calculation and drop it as a new column to the right of Amount.
For the syntax, use Percentage([Amount] for [Region])

Unlock the structure of your report, and drag another Query Calculation to the Region footing below the percentage column.  For the syntax, use Percentage([Amount] for [Division])

Drag another Query Calculation into the Division footing below the percentage total.  For the syntax, use Percentage([Amount] for Report)

Re-lock your structure and run, and this should hopefully give you the result you are looking for.

Best regards,

MF.
Meep!