on 10-18-2011 5:28 AM
I have a subtotal in my crosstab where there are 3 rows of totals from previous rows. I would like to create a row that takes the subtotals and divides the 1st row by the 2nd row.
Subtotal1
Subtotal2
Subtotal3
Subtotal1/Subtotal2
How does one insert a row into the existing subtotal?
I can't seem to get the Calculated Member to work. Can someone give me a hand?
Thanks in advance.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Eric,
Totals and Subtotals are not assigned a separate row/column index, so, you might have to manually remember their indexes before creating the formula.
For creating a new row of Subtotal, just right click the subtotal after which you want the new one to appear and select Calculated Member > Insert Row. To insert a calculation formula for this subtotal, right click the zero value cell of this new row and select Calculated Member > Edit Calculation Formula and use this formula:
gridvalueat(8,currentcolumnindex,currentsummaryindex)/gridvalueat(11,currentcolumnindex,currentsummaryindex)
In the above code, nos. 8 and 11 are the row indexes of the subtotal 1 and subtotal 2 respectively. How did I find this?
Right-click one of the subtotal fields and select Format Field > Common tab > Display String and type this formula:
totext(currentrowindex).
This will list the row indexes of all the subtotals and you just need to note down the ones you need! Once you have them, just undo the formula in the Display String.
Hope this helps!
-Abhilash
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
93 | |
11 | |
10 | |
9 | |
9 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.