Share via

2.2.5.2.5 Calculated Fields

  • Article

Calculated fields allow users to add calculations to a PivotTable (section 2.2.5) report. 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.2.2) that does not correspond to a column (1) in the source data (section 2.2.5.2.1). The values for a calculated field are calculated based on the formula specified for the calculated field. A calculated field is specified by the fLoadFmla field of the BrtBeginPCDField (section 2.4.136) record being equal to 1. The formula is specified by the fldFmla field of the BrtBeginPCDField record.

The sequence of records that conforms to the PNAMES rule (defined in section 2.1.7.38) specifies any cache field (section 2.2.5.2.2) referenced by the formula.

For calculated fields, the fSrcField record of the BrtBeginPCDField record MUST be 0.

A pivot field (section 2.2.5.3.2) associated with a calculated field MUST NOT appear on the row axis, column (1) axis or page axis (section 2.2.5.3.7.1) of a PivotTable view (section 2.2.5.3).

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