2.2.5.3.9 Calculated Items

Calculated items allow users to add a cache item (section 2.2.5.3.6) that does not exist in the source data (section 2.2.5.3.2) to a cache field (section 2.2.5.3.5). For example, consider a PivotTable report (section 2.2.5) displaying sales for the four quarters of 2007. If there are 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 a calculated item

The values for a calculated item are calculated based on the formula specified for the calculated item.

The sequence of records that conform to the SXFORMULA rule (section 2.1.7.12) 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 sequences of records that conform to the SXFORMULA rule corresponding to the same calculated item. The calculated item that a calculation is associated with is specified by the sequence of records that conform to the PIVOTRULE rule (section 2.1.7.20.6) in the SXFORMULA 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 will specify the cache field corresponding to "product group" and the cache item corresponding to "Cars".

An OLAP PivotCache (section 2.2.5.3.4) MUST NOT have calculated items.