2.2.5.3.8 Calculated Fields

  • Article

Calculated fields allow users to add calculations to a PivotTable report (section 2.2.5). For example, if a PivotTable report contains values for sales and cost by products, but no profit values, a calculated field with the formula "=sales-cost" can be added so that profit values are calculated and can be analyzed in the PivotTable report.

A calculated field is a cache field (section 2.2.5.3.5) and does not correspond to a column in the source data (section 2.2.5.3.2). The values for a calculated field are calculated based on the formula specified for the calculated field. A calculated field is specified by the fCalculatedField field of the SXFDB record (section 2.4.283) being equal to 1. The formula is specified by the SXFormula record (section 2.4.288) following the SXFDB record.

A pivot field associated with a calculated field MUST NOT appear on the row axis (section 2.2.5.4.9.2), column axis (section 2.2.5.4.9.3), or page axis (section 2.2.5.4.9.1) of a PivotTable view (section 2.2.5.4).

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