Subtotals

Subtotalling by hierarchy is an option in Grid based reports where a crossjoin has been used. Subtotalling can use any one of the Sum, Avg, Min or Max functions.

To add a subtotal, select a cell in the grid containing a member of the hierarchy you want to all the subtotal to. In the XLCubed Grid ribbon, select the type of calculation to apply. This can also be accessed through the right click menu XLCubed > Apply > Show Subtotals.

This will then add a new row/column next to each member of the selected hierarchy.

To remove the Subtotal, select the XLCubed > Apply > Show Subtotals > None menu item.

Cube vs Excel mode

The default is Excel mode, this will use an Excel formula to calculate the totals - this would mean any calculated members will be totaled after the query is run and may not return the number you want (summing the average or percent numbers etc.).

Cube mode performs the sub-totaling as part of the query, such that (if the cube calculations support it) any "Average" or "Percent" calculations will remain consistent.

You can use the "Grid" ribbon to switch between the 2 modes:

Formatting Subtotals

Subtotals can be formatted as with any member in an XLCubed (see Formatting Grids). Right click one of the subtotals and select XLCubed > Formatting > Format this level.

Alternatively you can write the line directly to the format sheet using LEVEL:SUBTOTAL as the member.

This will then format only the subtotal rows: