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(Dimensionality()=0,'',
If( D1 = 'Grand Total',
If( Dimensionality() > 1 , Null() , Sum(TOTAL N) )
, Sum(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 ...
