Applying subtotals to a report

Subtotals reflect data rolled up to different attribute levels on a report. They can be applied dynamically to any report. You can apply subtotals using one of many standard predefined subtotal functions such as total, count, minimum, maximum, standard deviation, and others. If these standard functions do not meet your needs, you can create user-defined subtotals employing any aggregation functions. These allow you to combine functions, create nested subtotals, and use constants, for example. User-defined subtotals work similarly to the predefined subtotal functions.

The behavior of subtotal aggregations is based on the types of data included in the metric to which the subtotal is applied.

The example report below contains subtotals and a grand total. It shows subtotals for each region, an additional subtotal for each quarter (encompassing all regions), and a grand total at the bottom of the report. This is just a sample of the report; not all data is displayed in the image below.

You can select a level for subtotal calculation, which defines the level at which the metric is calculated. You can apply the subtotal by position, across a level, or using group by. This level does not affect the location of the subtotal on the report, but rather how the subtotal is calculated. For detailed descriptions of the level options, see Selecting a level for subtotal calculation.

You can change the display location of the subtotals. For example, you can select to have the subtotals for a given fact or attribute shown along the bottom of the report, those for a different metric displayed along the right margin, and so on. This is described in the procedure below.

You can also select the value that is displayed in place of a null value. For instructions, see Changing the display of null values.

The following procedure applies subtotals to a report. You can also select subtotals for a specific metric in the Metric Editor. For more information, see Making subtotals available to a metric.

To apply subtotals to a report

Open the report.
- Open the Report Editor
- Open the Report Viewer
From the Data menu, select Subtotals. The Subtotals dialog box opens.

You can also right-click the word Metrics in the report grid, select Subtotals, and then select the subtotal function and whether it is used for All Subtotals, Grand Total, or Across Levels. This provides a shortcut to the functionality described in steps 3 and 4 below.

Define a subtotal: Select the function, calculation level, and display location
On the Definition tab, select a function to use calculate subtotals.

The list of functions available is determined when the metric is created. If no functions are listed, subtotals have been disabled for the metric. For steps to enable subtotals, see Steps to make subtotals available to a metric.
Set the level at which to calculate the function. How?
To select where the subtotals are displayed, click the Display Options tab.
- For row subtotals, select whether to position them at the Top of each level or the Bottom of each level.
  
  The Subtotal positions by row section is grayed out and unavailable if metrics are sorted hierarchically. Hierarchical metric sorting takes precedence over subtotal display. To display subtotals at the top or bottom of the level when metrics are sorted hierarchically, specify the Total Position in the Sorting dialog box. For more detailed instructions, see Advanced sorting: hierarchical sorting.
- For column subtotals, select whether to position them at the Left of each level or the Right of each level.
- For page subtotals, select whether to position them at the Top of each level or the Bottom of each level.
You can use more than one subtotal function on a report. Simply repeat the process, beginning by selecting a subtotal function.
For more control over the display of subtotals, including different subtotal functions for different metrics, you can create a custom subtotal. (See Defining a custom subtotal.)
Click OK. You are returned to your report or template.

Applying subtotals to a report

To apply subtotals to a report

Related Topics