2.2.5.2.6 Calculated Items
Calculated items allow users to add cache items (section 2.2.5.2.3) to a cache field (section 2.2.5.2.2) that do not exist in a column (1) in the source data (section 2.2.5.2.1). For example, consider a PivotTable report displaying sales for all four quarters of 2007. If there are no source data rows for sales in 2008, a calculated item can be used to add an additional cache item as a calculated item that calculates the projected sales for the first quarter of the year 2008 as being 25% higher than the sales for the fourth quarter of 2007. The following figure illustrates a PivotTable report with such a calculated item ("2008 Q1 projected").
Figure 12: PivotTable report with calculated item
A calculated item is a cache item (section 2.2.5.2.3) that does not correspond to values in the source data (section 2.2.5.2.1). The values for a calculated item are calculated based on the formula specified for the calculated item. The fFmla field of the PCDIAddlInfo (section 2.5.100) record specifies if a cache item specified by the PCDIA rule (defined in section 2.1.7.38) is a calculated item.
The sequence of records that conforms to the PCDCALCITEMS rule (defined in section 2.1.7.38) specifies the calculations for all calculated items of a PivotCache. Each PCDCALCITEM rule (defined in section 2.1.7.38) in the sequence of records that conforms to the PCDCALCITEMS rule specifies one calculation for a specific calculated item. Each calculated item can have multiple calculations associated with it and in that case, there are multiple elements in the PCDCALCITEMS rule corresponding to the same calculated item. The calculated item that a calculation is associated with is specified by the sequence of records that conforms to the PIVOTRULE rule (defined in section 2.1.8) in the PCDCALCITEM rule. The PIVOTRULE rule can also specify additional scoping information; for example, if one calculation for a calculated item named "2008 Q1 projected" only applies to the "Cars" product group, the PIVOTRULE rule (defined in section 2.1.8) will specify the cache field (section 2.2.5.2.2) corresponding to "product group" and the cache item (section 2.2.5.2.3) corresponding to "Cars".
The fmla field of the BrtBeginPCDCalcItem (section 2.4.124) record specifies the formula that is used for a calculation.
Any cache field (section 2.2.5.2.2) and associated cache items (section 2.2.5.2.3), or pivot fields (section 2.2.5.3.2) and associated pivot items (section 2.2.5.3.3) , that are referenced by the formula of a calculation are specified by the sequence of records that conforms to the PNAMES rule (defined in section 2.1.7.38) in each PCDCALCITEM rule (defined in section 2.1.7.38).
An OLAP PivotCache (section 2.2.5.2) MUST NOT have calculated items (section 2.2.5.2.6).