Difference between revisions of "Subtotals"
m (Text replacement - "XLCubed" to "FluenceXL") |
|||
| (3 intermediate revisions by 3 users not shown) | |||
| Line 1: | Line 1: | ||
Subtotalling by hierarchy is an option in Grid based reports where a crossjoin has been used. Subtotalling can use any one of the {{Code|Sum}}, {{Code|Avg}}, {{Code|Min}} or {{Code|Max}} functions. | Subtotalling by hierarchy is an option in Grid based reports where a crossjoin has been used. Subtotalling can use any one of the {{Code|Sum}}, {{Code|Avg}}, {{Code|Min}} or {{Code|Max}} functions. | ||
| − | To add a | + | 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 FluenceXL Grid ribbon, select the type of calculation to apply. This can also be accessed through the right click menu {{Menu|FluenceXL|Apply|Show Subtotals}}. |
| − | To remove the Subtotal, select the {{Menu| | + | [[File:SubtotalRibbon9.PNG|center]] |
| + | |||
| + | This will then add a new row/column next to each member of the selected hierarchy. | ||
| + | |||
| + | [[File:SubtotalInGrid.PNG|center|500px]] | ||
| + | |||
| + | To remove the Subtotal, select the {{Menu|FluenceXL|Apply|Show Subtotals|None}} menu item. | ||
==Cube vs Excel mode== | ==Cube vs Excel mode== | ||
| Line 10: | Line 16: | ||
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. | 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 FluenceXL (see [[Formatting Grids]]). Right click one of the subtotals and select {{Menu|FluenceXL|Formatting|Format this level}}. | ||
| + | |||
| + | [[File:SubtotalFormatMenu.PNG|center|500px]] | ||
| + | |||
| + | |||
| + | Alternatively you can write the line directly to the format sheet using LEVEL:SUBTOTAL as the member. | ||
| + | |||
| + | [[File:SubtotalFormatSheet.PNG|center|600px]] | ||
| + | |||
| + | This will then format only the subtotal rows: | ||
| + | |||
| + | [[File:SubtotalFormatted.PNG|center|500px]] | ||
[[Category:Grid Reporting]] | [[Category:Grid Reporting]] | ||
[[Category:OLAP Operations]] | [[Category:OLAP Operations]] | ||
Latest revision as of 09:38, 4 July 2023
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 FluenceXL Grid ribbon, select the type of calculation to apply. This can also be accessed through the right click menu FluenceXL > 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 FluenceXL > 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 FluenceXL (see Formatting Grids). Right click one of the subtotals and select FluenceXL > 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:
