Automatic Totals (Subtotals)
- 2 minutes to read
Automatic Totals and Custom Totals allow subtotals to be calculated against groups of data. These totals are calculated against a specific data field and they are displayed as an additional columns or rows for grouping field values which have nested field values. So, totals are not calculated for the values of the innermost column or row fields. If a specific field is neither a column field nor a row field, totals will not be calculated.
An automatic total is calculated for each data field. By default, if there are multiple data fields the ExpressPivotGrid displays multiple column totals and a single row total for each field value. In the image below the ExpressPivotGrid control contains two data fields and it displays two column totals and a single row total for appropriate field values.
To reverse the layout and display multiple row totals and a single column total, set the pivot grid’s OptionsDataField.Area property to faRow. Note that this will also display the data field anchor in the row header area.
The summary function used to calculate automatic totals is the same as the summary function used in data cells (these cells are painted in white in the image above). This summary function is determined by the SummaryType property of a corresponding data field. To calculate a total using a different summary function or to calculate multiple subtotals, use the Custom Totals feature.
If a specific field value has a single nesting field value, a corresponding subtotal is not displayed by default. For instance, in the image above subtotals are not displayed for the ‘Qtr 2’ column as it contains only a single nesting item (the ‘April 2002’ column). To display totals for such field values, set the pivot grid’s OptionsView.TotalsForSingleValues property to True.
To hide column and row subtotals, use the OptionsView.ColumnTotals and OptionsView.RowTotals properties.
To specify the position of grand totals and subtotals, use the OptionsView.ColumnTotalsLocation, OptionsView.RowTotalsLocation, and OptionsView.TotalsLocation properties.