???
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
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.
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.