2.2.5.4.9.4.2 Subtotalling

A nested item group, as specified in the Nesting section, can have summaries of the values for the items in the nested item group, called subtotals. A subtotal is typically an aggregation such as a sum, count or average of the values of the items.

The creation of subtotals is specified by the fDefault, fSum, fCounta, fAverage, fMax, fMin, fProduct, fCount, fStdev, fStdevp, fVariance and fVariancep fields of the Sxvd record (section 2.4.309) of the pivot field (section 2.2.5.4.3). If none of the fields are equal to 1, then no subtotals exist for the pivot field (section 2.2.5.4.3). If the fDefault field is equal to 1 the subtotal calculation for each item is done according to the aggregation functions of the data items (section 2.2.5.4.9.5.1) on the data axis (section 2.2.5.4.9.5), as specified by the iiftab field of the SXDI record (section 2.4.278) for each data item (section 2.2.5.4.9.5.1).

For example, the subtotal is calculated as the sum of the relevant values of the nested item group for a data item (section 2.2.5.4.9.5.1) with a sum aggregation function and subtotal is calculated as the average of the relevant values of the nested item group for a data item (section 2.2.5.4.9.5.1) with an average aggregation function.

The other subtotal fields are called custom subtotals because they override the data item (section 2.2.5.4.9.5.1) aggregation function when calculating subtotals.

In some cases, such as for certain OLAP PivotTable views (section 2.2.5.4.2), the source data (section 2.2.5.3.2) is not able to provide a requested subtotal.

The fOutline field of the SXVDEx record (section 2.4.310) specifies that an extra pivot line (section 2.2.5.4.10.3) is added at the logical top of the nested item groups if the pivot field (section 2.2.5.4.3) is on the row axis (section 2.2.5.4.9.2). This pivot line (section 2.2.5.4.10.3) contains the item and any items of member properties (section 2.2.5.4.6) pivot fields (section 2.2.5.4.3), if they are shown, but no other items for inner pivot fields (section 2.2.5.4.3) of this pivot field (section 2.2.5.4.3).

The fOutlineData field of the SXAddl_SXCView_SXDVer12Info record (section 2.4.273.109) specifies that an extra pivot line (section 2.2.5.4.10.3) is added at the logical top of the nested item groups if the data field (section 2.2.5.4.9.5.2) is on the row axis (section 2.2.5.4.9.2). This pivot line (section 2.2.5.4.10.3) contains the data item (section 2.2.5.4.9.5.1), but no other items for inner pivot fields (section 2.2.5.4.3) of this data field (section 2.2.5.4.9.5.2).

If the fDefault field of the Sxvd record (section 2.4.309) of the pivot field (section 2.2.5.4.3) is equal to 1, the fOutline field of the SXVDEx record (section 2.4.310) of the pivot field (section 2.2.5.4.3) is equal to 1, the pivot field (section 2.2.5.4.3) is on the row axis (section 2.2.5.4.9.2), and the data field (section 2.2.5.4.9.5.2) is not placed inner of the pivot field (section 2.2.5.4.3) on the row axis (section 2.2.5.4.9.2), then the following is specified for the fSubtotalAtTop field of the SXVDEx record (section 2.4.310) of the pivot field (section 2.2.5.4.3):

Value of fSubtotalAtTop	Meaning
0x0	Specifies that subtotal pivot lines (section 2.2.5.4.10.3) are added at the bottom of the nested item groups. See the subName.stSubName field of the SXVDEx record (section 2.4.310) for details of the label used.
0x1	Specifies that the pivot lines (section 2.2.5.4.10.3) added as specified by the fOutline flag of the SXVDEx record (section 2.4.310) being equal to 1 are used for displaying the subtotals in the data area (section 2.2.5.4.10.1.4).

In the following figure, the Category, Subcategory, and Product columns represent pivot fields (section 2.2.5.4.3) on the row axis (section 2.2.5.4.9.2) and the Color column represents a member properties (section 2.2.5.4.6) pivot field (section 2.2.5.4.3) associated with the Product pivot field (section 2.2.5.4.3). Subtotals are displayed at the logical top of the nested item groups for Clothing, Caps, and Gloves.

Figure 14: PivotTable report with Category and Subcategory pivot fields with fOutline and fSubtotalAtTop fields of the SXVDEx record equal to 1