I haven't explained much about the properties I had to set and the expressions ... I will post an explanation within a couple of hours ... listing what needs to be set in the Pivot Chart ...
0. In your data table you have to create an additional Grand Total row so you have a place holder
to calculate the Grand Total and get it to show in the Pivot Table.
It can be done like this in the load script:
CONCATENATE LOAD 'Grand Total' AS D1, '' AS D2, '' AS D2, '' AS D3, '' AS N AUTOGENERATE 1;
Place it after your main table so it will force concatenate an extra row to the main table.
1. Create A Pivot Table with the three dimensions D1, D2, D3
2. A single expression:
If( D1 = 'Grand Total',
If( Dimensionality() > 1 , Null() , Sum(TOTAL N) )
3. Presentation tab select Subtotals: Subtotals on Top
4. Style tab: check the check-box Indent Mode and Use Only First Dimension Label
5. Layout tab: Border Width 0 pt
6. Caption remove check on Show Caption
7. For all dimensions check-mark on Supress When Value Is Null
8. On Expression tab - expand the expression on the left and then click Background Color
9. Fill in the Definition (expression): If( D1='Grand Total', RGB(210,210,255))
10. Click on the Text Format and press Edit button
11. Fill in the Definition (expression): If( D1='Grand Total', '<B>')
10. On Dimensions tab: expand the D1 dimension - click on Background Color and then press Edit button and press OK
11. Fill in the same definition as in step 9: If( D1='Grand Total', RGB(210,210,255))
12. click on Text Format and then press Edit button
13. Fill in the same expression as in step 11: If( D1='Grand Total', '<B>') and press OK
That is basically it ... although there are some additional cosmetic steps ... I leave it to you to figure out ...
Peter, I just got a chance to look at this - this is my second app that im doing. I have multiple (10ish )amount columns that I need a grand total on. Im not sure how I would change the above code for that. Any suggestions.