Overview
Pivot Tables offer increased runtime functionality for end-users without requiring heavy customization by developers. While Pivot Tables are easy to use, one common question received is “How can I add runtime calculations to my subtotal and grand total levels?”
To do so, begin by creating your Pivot Table as normal, being sure to select as many Dimension keys and Filters as needed. Below is a sample Pivot Table:
In the example above, we want to see what percent of total sales was spent on cost of goods sold. To accomplish this, navigate to calculations and add a new Application type calculation, as shown below:
Selecting Application as the calculation type within Pivot Tables will create a total level /margin calculation automatically at runtime. In the context of this report, this means the calculation for sales divided by cost will be computed at the subtotal and grand total lines, rather than at the detail level.
After adding this calculation, be sure to compile/recompile. Now, at runtime, the margin calc can be selected and displayed, just like all other non-sequence key, displayed numeric fields:
