2.2.5.3.7.4.2 Subtotaling
A nested item group, as specified in section 2.2.5.3.7.4, 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, fVar, and fVarp fields of the BrtBeginSXVD (section 2.4.272) record of the pivot field (section 2.2.5.3.2). If none of the fields are equal to 1, then no subtotals exist for the pivot field. 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.3.7.5.1) on the data axis (section 2.2.5.3.7.5), as specified by the iiftab field of the BrtBeginSXDI (section 2.4.243) record for each data item.
For example, the subtotal is calculated as the sum of the relevant values of the nested item group for a data item 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 with an average aggregation function.
The other subtotal fields are called custom subtotals because they override the data item (section 2.2.5.3.7.5.1) aggregation function when calculating subtotals. In some cases, such as for certain OLAP PivotTable views (section 2.2.5.3), the source data (section 2.2.5.2.1) is not able to provide a requested subtotal.
The fOutline field of the BrtBeginSXVD (section 2.4.272) record specifies that an extra pivot line (section 2.2.5.3.8.3) is added at the logical top of the nested item groups if the pivot field (section 2.2.5.3.2) is on the row axis. This pivot line contains the item and any items of member property pivot fields, if they are shown, but no other items of pivot fields inner of this pivot field.
The fOutlineData field of the BrtBeginSXView (section 2.4.275) record specifies that an extra pivot line is added at the logical top of the nested item groups if the data field (section 2.2.5.3.7.5.2) is on the row axis. This pivot line contains the data item, but no other items for inner pivot fields of this data field.
If the fDefault field of the BrtBeginSXVD (section 2.4.272) record of the pivot field (section 2.2.5.3.2) is equal to 1, and the fOutline field of the BrtBeginSXVD record of the pivot field is equal to 1, the pivot field is on the row axis, and the data field is not placed inner of the pivot field on the row axis, then the fSubtotalAtTop field of the BrtBeginSXVD record of the pivot field has a value as specified in the following table:
|
Value of fSubtotalAtTop |
Meaning |
|---|---|
|
0x0 |
Specifies that subtotal pivot lines (section 2.2.5.3.8.3) are added at the bottom of the nested item groups.
If the irstSub field of the BrtBeginSXVD (section 2.4.272) record is present, it specifies details for the subtotal label used.
If the irstSub field of the BrtBeginSXVD record is not present, an application specific subtotal label is used. |
|
0x1 |
Specifies that the pivot lines added, as specified by the fOutline field of the BrtBeginSXVD record being equal to 1, are used for displaying the subtotals in the data area (section 2.2.5.3.8.1.4). |
In the following figure, the Category, Subcategory, and Product columns (1) represent pivot fields (section 2.2.5.3.2) on the row axis and the Color column (1) represents a member property pivot field associated with the Product pivot field. 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 BrtBeginSXVD records equal to 1