<calculatedItem> (Calculated Item)

Represents an item within a PivotTable field that uses a formula . The formula is specified in the <formula> attribute.

Calculations and options available for a PivotTable depend on whether the source data came from an OLAP database or another type of database. This complex type applies to non-OLAP external data or on worksheet data. See <calculatedMember >for information on calculations on OLAP data sources.

Parent Elements

<calculatedItems> (§3.10.1.9)

Child Elements

Subclause

<extLst> (Future Feature Data Storage Area)

§3.2.10

<pivotArea> (Pivot Area)

§3.3.1.66

Attributes

Description

<field> (Field Index)

Specifies the index of the <pivotField> with which this calculated item is associated.

The possible values for this attribute are defined by the XML Schema unsignedInt datatype.

<formula> (Calculated Item Formula)

Specifies the formula of the calculated item. In formulas you create for calculated items, you can use operators and expressions as you do in other worksheet formulas. You can use constants and refer to data from the PivotTable, but you cannot use cell references or defined names. You cannot use worksheet functions that require cell references or defined names as arguments, and you cannot use array functions.

Further behaviors and restrictions apply to formulas for calculatedItems:

  • Formulas for calculated items operate on the individual records; the calculated item formula =Dairy *115% multiplies each individual sale of Dairy times 115%, after which the multiplied amounts are summarized together in the data area.

  • Formulas cannot refer to totals.

  • You can include the field name in a reference to an item. The item name must be in square brackets. Use this format to avoid #NAME? errors when two items in two different fields in a report have the same name.

  • You can refer to an item by its position in the PivotTable as currently sorted and displayed. The item referred to in this way can change whenever the positions of items change or different items are displayed or hidden. Hidden items are not counted in this index.

  • You can use relative positions to refer to items. The positions are determined relative to the calculated item that contains the formula. If the position you give is before the first item or after the last item in the field, the formula results in a #REF! error.

For more information about formulas see §3.17 in Formulas. For more information about defined names see §3.2.6 in Workbook.

The possible values for this attribute are defined by the ST_Xstring simple type (§3.18.96).

The following XML Schema fragment defines the contents of this element:

<complexType name="CT_CalculatedItem">
	<sequence>
	<element name="pivotArea" type="CT_PivotArea"/>
	<element name="extLst" minOccurs="0" type="CT_ExtensionList"/>
	</sequence>
	<attribute name="field" type="xsd:unsignedInt" use="optional"/>
	<attribute name="formula" type="ST_Xstring"/>
</complexType>