Selecting a level for subtotal calculation

Selecting a level for subtotal calculation allows you to choose the level at which the metric is calculated. Your options are:

Across level, which displays the subtotal at selected levels. The subtotal is applied to particular levels-rows, columns, and pages. This effectively means "group by attributes to the left of the selected attribute".

For example, consider a report that has Region and Employee, in that order, on the grid. Selecting to subtotal across Employee means group by Region. A subtotal for each Region, totaling the individual Employee-Region values, displays on the report. Likewise, across Region means group by none since there is nothing to the left of it on the report. The result is a grand total. However, if the report is pivoted and the order of the attributes changes, the totals also change. If Employee is pivoted to the left of Region, the across Employee subtotal means group by none.

By default, consolidations available on the report are not displayed. Any objects placed to the left of the consolidation on the report cannot be subtotaled. They are displayed in this dialog box but cannot be chosen. To change this behavior, enable consolidation subtotals.
By position, which applies the subtotal based on its location on the report. The subtotal is calculated across all attributes and hierarchies available on the report. It provides the same behavior as Across level, but without selecting a level. Instead, the level is selected dynamically so these subtotals can change if you alter the layout of the template is altered. The two choices for by position are All subtotals, meaning "across all attributes," and Grand Total, translating to "across the leftmost attribute."

For example, you can choose to subtotal on rows and/or columns. A report contains the columns Region, Employee, Revenue, Cost, and Profit. You can subtotal by both rows and columns, which provides totals at the Employee and Region level for each metric.
Group by, which displays the subtotal by the selected attribute across all other attributes on the report, regardless of position. Group by effectively allows you to use both subtotal and sort by attributes that are not the furthest to the left. The Grand Total check box allows you to also add a subtotal grouped by nothing, effectively calculating a total of all attributes on the template.

For example, a report contains Region, Category, and Quarter, and subtotals are grouped by Region. A Region subtotal always appears, regardless of where Category and Quarter are located with respect to Region. You can also group by multiple attributes. For example, grouping by Region-Category on that report provides a subtotal every time a new Region-Category combination occurs.

Group by works best if the report sort corresponds to the group by attribute, regardless of position.

For more detailed examples of subtotal levels, see the Advanced Reporting Help.

To select a level for subtotal calculation

From the Subtotals dialog box, click Advanced. The Advanced Subtotals dialog box opens.

To access the Subtotals dialog box, open the report (Open the Report Editor or Open the Report Viewer). From the Data menu, select Subtotals. The Subtotals dialog box opens.
Select the subtotal function to use from the Subtotals list.
In the Applied levels section, specify the desired level on the template at which to calculate the selected subtotal. The levels are:
- By position, to apply the subtotal to particular levels, including rows, columns, and pages.
- Across level, to apply the subtotal across all attributes and hierarchies available on the template.
  
  By default, consolidations available on the report are not displayed. Any objects placed to the left of the consolidation on the report cannot be subtotaled. They are displayed in this dialog box but cannot be chosen. To change this behavior,enable consolidation subtotals.
- Group by, to apply the subtotal by the selected attribute across all other attributes on the template, regardless of position.
  
  Group by works best if the report sort corresponds to the group by attribute, regardless of position.
If you selected By position, for each level (rows, columns, and pages), select one of the following options:
- Grand Total to apply only the subtotal across the whole axis.
- All Subtotals to apply the subtotal across all levels on the axis.
- None to not apply the subtotal to any level on the axis.
If you selected Across level:

From the list of report objects, select the attribute(s).
If you selected Group by:
- Click Add to add a new group by level. The Group By Selection dialog box opens.
- From the list of attributes on the report, create a group by level. A group by level can be a single attribute or a combination of attributes. Click OK to return to the Advanced Subtotals dialog box.
- If desired, create more group by levels. Each level must be created individually, otherwise the attributes are combined into one level. For example, the template contains region and category, and you want to group by region and group by category. If you select both attributes in the Group By Selection dialog box, one level, region-category, is created.
You can use more than one subtotal function on a report. Simply repeat the process, beginning by selecting a subtotal function.
Click OK to return to the Subtotals dialog box. The subtotal is selected in the Available Subtotals list and its definition displayed in the Description box.
You can do any of the following:
- Create a custom subtotal, which allows more control over the display of subtotals, including different subtotal functions for different metrics. (See Defining a custom subtotal for instructions.)
- Specify where to display the subtotals on the report. For instructions, see Selecting a subtotal function.
- Save the subtotals, by clicking OK to return to the report.

Selecting a level for subtotal calculation

To select a level for subtotal calculation

Related Topics