Partager via

CalculatedItem Class

  • Article

Calculated Item.When the object is serialized out as xml, its qualified name is x:calculatedItem.

Inheritance Hierarchy

System.Object
  DocumentFormat.OpenXml.OpenXmlElement
    DocumentFormat.OpenXml.OpenXmlCompositeElement
      DocumentFormat.OpenXml.Spreadsheet.CalculatedItem

Namespace:  DocumentFormat.OpenXml.Spreadsheet
Assembly:  DocumentFormat.OpenXml (in DocumentFormat.OpenXml.dll)

Syntax

'Declaration
<ChildElementInfoAttribute(GetType(PivotArea))> _
<ChildElementInfoAttribute(GetType(ExtensionList))> _
Public Class CalculatedItem _
    Inherits OpenXmlCompositeElement
'Usage
Dim instance As CalculatedItem

[ChildElementInfoAttribute(typeof(PivotArea))]
[ChildElementInfoAttribute(typeof(ExtensionList))]
public class CalculatedItem : OpenXmlCompositeElement

Remarks

The following table lists the possible child types:

  • PivotArea <x:pivotArea>

  • ExtensionList <x:extLst>

[ISO/IEC 29500-1 1st Edition]

18.10.1.8 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 (§18.10.1.9)

Child Elements

Subclause

extLst (Future Feature Data Storage Area)

§18.2.10

pivotArea (Pivot Area)

§18.3.1.68

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 W3C 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 shall 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 §18.17 in Formulas. For more information about defined names see §18.2.6 in Workbook.

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

[Note: The W3C XML Schema definition of this element’s content model (CT_CalculatedItem) is located in §A.2. end note]

© ISO/IEC29500: 2008.

Thread Safety

Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe.

See Also

Reference

CalculatedItem Members

DocumentFormat.OpenXml.Spreadsheet Namespace