PivotTableDefinition class
Defines the PivotTableDefinition Class.This class is available in Office2010 or above. When the object is serialized out as xml, its qualified name is x14:pivotTableDefinition.
DocumentFormat.OpenXml.OpenXmlElement
DocumentFormat.OpenXml.OpenXmlCompositeElement
DocumentFormat.OpenXml.Office2010.Excel.PivotTableDefinition
Namespace: DocumentFormat.OpenXml.Office2010.Excel
Assembly: DocumentFormat.OpenXml (in DocumentFormat.OpenXml.dll)
2.4.19 pivotTableDefinition
Target namespace: http://schemas.microsoft.com/office/spreadsheetml/2009/9/main
A pivotTableDefinition element is a CT_PivotTableDefinition type element, as specified in section 2.6.32, that specifies additional properties of the PivotTable, as specified in [ISO/IEC-29500-1] section 18.10, view. See section 2.2.4.4 for how this element integrates with the Office Open XML file formats specified in [ISO/IEC-29500-1].
The following W3C XML Schema ([XMLSCHEMA1] section 2.1) fragment specifies the contents of this element.
<xsd:element name="pivotTableDefinition" type="CT_PivotTableDefinition"/>
See section 5.4 for the full W3C XML Schema ([XMLSCHEMA1] section 2.1).
2.6.32 CT_PivotTableDefinition
Target namespace: http://schemas.microsoft.com/office/spreadsheetml/2009/9/main
Referenced by: pivotTableDefinition
This element specifies additional properties of a PivotTable ([ISO/IEC-29500-1] section 18.10) view.
Child Elements:
pivotEdits: A CT_PivotEdits element that specifies a collection of PivotTable What-if Analysis edits.
pivotChanges: A CT_PivotChanges element that specifies a collection of PivotTable What-if Analysis changes.
conditionalFormats: A CT_ConditionalFormats element that specifies a collection of conditional formats applied to the PivotTable ([ISO/IEC-29500-1] section 18.10) view.
Attributes:
fillDownLabelsDefault: A Boolean ([XMLSCHEMA2] section 3.2.2) attribute that specifies the behavior of PivotTable ([ISO/IEC-29500-1] section 18.10) fields (1) that are not a part of the PivotTable ([ISO/IEC-29500-1] section 18.10) view if they are later added to a PivotTable ([ISO/IEC-29500-1] section 18.10) view. MUST be a value from the following table.
Value | Meaning |
"false" | Specifies that PivotTable ([ISO/IEC-29500-1] section 18.10) fields (1) added to the PivotTable ([ISO/IEC-29500-1] section 18.10) view behave as if the fillDownLabels attribute of the CT_PivotField elements associated with those PivotTable ([ISO/IEC-29500-1] section 18.10) fields (1) are set to "false". |
"true" | Specifies that PivotTable ([ISO/IEC-29500-1] section 18.10) fields (1) added to the PivotTable ([ISO/IEC-29500-1] section 18.10) view behave as if the fillDownLabels attribute of the CT_PivotField elements associated with those PivotTable ([ISO/IEC-29500-1] section 18.10) fields (1) are set to "true". |
visualTotalsForSets: A Boolean ([XMLSCHEMA2] section 3.2.2) attribute that specifies whether hidden PivotTable ([ISO/IEC-29500-1] section 18.10) items will be included when calculating totals for PivotTable ([ISO/IEC-29500-1] section 18.10) named sets. MUST be a value from the following table.
Value | Meaning |
"false" | Include hidden PivotTable ([ISO/IEC-29500-1] section 18.10) items in the set totals. |
"true" | Do not include hidden PivotTable ([ISO/IEC-29500-1] section 18.10) items in the set totals. |
calculatedMembersInFilters: A Boolean ([XMLSCHEMA2] section 3.2.2) attribute that specifies whether PivotTable ([ISO/IEC-29500-1] section 18.10) OLAP-calculated members participate in PivotTable ([ISO/IEC-29500-1] section 18.10) manual filters and PivotTable ([ISO/IEC-29500-1] section 18.10) advanced filters.
Value | Meaning |
"false" | PivotTable ([ISO/IEC-29500-1] section 18.10) OLAP-calculated members do not participate in PivotTable ([ISO/IEC-29500-1] section 18.10) manual filters and PivotTable ([ISO/IEC-29500-1] section 18.10) advanced filters. |
"true" | PivotTable ([ISO/IEC-29500-1] section 18.10) OLAP-calculated members participate in PivotTable ([ISO/IEC-29500-1] section 18.10) manual filters and PivotTable ([ISO/IEC-29500-1] section 18.10) advanced filters as do other PivotTable ([ISO/IEC-29500-1] section 18.10) items. |
altText: An ST_Xstring ([ISO/IEC-29500-1] section 22.9.2.19) attribute that specifies the alternate text for the PivotTable ([ISO/IEC-29500-1] section 18.10) view. The string MUST be less than or equal to 2,000 characters in length.
altTextSummary: An ST_Xstring ([ISO/IEC-29500-1] section 22.9.2.19) attribute that specifies the alternate text summary for the PivotTable ([ISO/IEC-29500-1] section 18.10) view. This string MUST be less than or equal to 2,000 characters in length.
enableEdit: A Boolean ([XMLSCHEMA2] section 3.2.2) attribute that specifies whether PivotTable what-if analysis is enabled for the current PivotTable ([ISO/IEC-29500-1] section 18.10) view.
autoApply: A Boolean ([XMLSCHEMA2] section 3.2.2) attribute that specifies whether PivotTable what-if analysis values are automatically allocated.
allocationMethod: An ST_AllocationMethod attribute that specifies the allocation method to use for PivotTable what-if analysis.
weightExpression: An ST_Xstring ([ISO/IEC-29500-1] section 22.9.2.19) attribute that specifies the multidimensional expression (MDX) of the weight expression for weighted allocations of PivotTable what-if analysis values. This string MUST be less than or equal to 65,535 characters in length.
hideValuesRow: A Boolean ([XMLSCHEMA2] section 3.2.2) attribute that specifies whether the values row (2) in the PivotTable ([ISO/IEC-29500-1] section 18.10) report is visible.
Value | Meaning |
"false" | The values row (2) in the PivotTable ([ISO/IEC-29500-1] section 18.10) report is visible. |
"true" | The values row (2) in the PivotTable ([ISO/IEC-29500-1] section 18.10) report is not visible. |
The following W3C XML Schema ([XMLSCHEMA1] section 2.1) fragment specifies the contents of this complex type.
<xsd:complexType name="CT_PivotTableDefinition">
<xsd:sequence>
<xsd:element name="pivotEdits" type="CT_PivotEdits" minOccurs="0" maxOccurs="1"/>
<xsd:element name="pivotChanges" type="CT_PivotChanges" minOccurs="0" maxOccurs="1"/>
<xsd:element name="conditionalFormats" type="CT_ConditionalFormats" minOccurs="0"/>
</xsd:sequence>
<xsd:attribute name="fillDownLabelsDefault" type="xsd:boolean" use="optional" default="false"/>
<xsd:attribute name="visualTotalsForSets" type="xsd:boolean" use="optional" default="false"/>
<xsd:attribute name="calculatedMembersInFilters" type="xsd:boolean" use="optional" default="false"/>
<xsd:attribute name="altText" type="x:ST_Xstring" use="optional"/>
<xsd:attribute name="altTextSummary" type="x:ST_Xstring" use="optional"/>
<xsd:attribute name="enableEdit" type="xsd:boolean" use="optional" default="false"/>
<xsd:attribute name="autoApply" type="xsd:boolean" use="optional" default="false"/>
<xsd:attribute name="allocationMethod" type="ST_AllocationMethod" use="optional" default="equalAllocation"/>
<xsd:attribute name="weightExpression" type="x:ST_Xstring" use="optional"/>
<xsd:attribute name="hideValuesRow" type="xsd:boolean" use="optional" default="false"/>
</xsd:complexType>
See section 5.4 for the full W3C XML Schema ([XMLSCHEMA1] section 2.1).