PivotTableDefinition Class

PivotTable Definition. It is the root element of PivotTablePart.

Inheritance Hierarchy

System.Object
  DocumentFormat.OpenXml.OpenXmlElement
    DocumentFormat.OpenXml.OpenXmlCompositeElement
      DocumentFormat.OpenXml.OpenXmlPartRootElement
        DocumentFormat.OpenXml.Spreadsheet.PivotTableDefinition

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

Syntax

'Declaration
<ChildElementInfoAttribute(GetType(Location))> _
<ChildElementInfoAttribute(GetType(PivotTableStyle))> _
<ChildElementInfoAttribute(GetType(ConditionalFormats))> _
<ChildElementInfoAttribute(GetType(PivotFields))> _
<ChildElementInfoAttribute(GetType(RowFields))> _
<ChildElementInfoAttribute(GetType(RowItems))> _
<ChildElementInfoAttribute(GetType(ColumnFields))> _
<ChildElementInfoAttribute(GetType(ColumnItems))> _
<ChildElementInfoAttribute(GetType(PageFields))> _
<ChildElementInfoAttribute(GetType(DataFields))> _
<ChildElementInfoAttribute(GetType(Formats))> _
<ChildElementInfoAttribute(GetType(ColumnHierarchiesUsage))> _
<ChildElementInfoAttribute(GetType(PivotHierarchies))> _
<ChildElementInfoAttribute(GetType(PivotFilters))> _
<ChildElementInfoAttribute(GetType(RowHierarchiesUsage))> _
<ChildElementInfoAttribute(GetType(ChartFormats))> _
<ChildElementInfoAttribute(GetType(PivotTableDefinitionExtensionList))> _
Public Class PivotTableDefinition _
    Inherits OpenXmlPartRootElement
'Usage
Dim instance As PivotTableDefinition

[ChildElementInfoAttribute(typeof(Location))]
[ChildElementInfoAttribute(typeof(PivotTableStyle))]
[ChildElementInfoAttribute(typeof(ConditionalFormats))]
[ChildElementInfoAttribute(typeof(PivotFields))]
[ChildElementInfoAttribute(typeof(RowFields))]
[ChildElementInfoAttribute(typeof(RowItems))]
[ChildElementInfoAttribute(typeof(ColumnFields))]
[ChildElementInfoAttribute(typeof(ColumnItems))]
[ChildElementInfoAttribute(typeof(PageFields))]
[ChildElementInfoAttribute(typeof(DataFields))]
[ChildElementInfoAttribute(typeof(Formats))]
[ChildElementInfoAttribute(typeof(ColumnHierarchiesUsage))]
[ChildElementInfoAttribute(typeof(PivotHierarchies))]
[ChildElementInfoAttribute(typeof(PivotFilters))]
[ChildElementInfoAttribute(typeof(RowHierarchiesUsage))]
[ChildElementInfoAttribute(typeof(ChartFormats))]
[ChildElementInfoAttribute(typeof(PivotTableDefinitionExtensionList))]
public class PivotTableDefinition : OpenXmlPartRootElement

Remarks

The following table lists the possible child types:

• Location <x:location>

• PivotFields <x:pivotFields>

• RowFields <x:rowFields>

• RowItems <x:rowItems>

• ColumnFields <x:colFields>

• ColumnItems <x:colItems>

• PageFields <x:pageFields>

• DataFields <x:dataFields>

• Formats <x:formats>

• ConditionalFormats <x:conditionalFormats>

• ChartFormats <x:chartFormats>

• PivotHierarchies <x:pivotHierarchies>

• PivotTableStyle <x:pivotTableStyleInfo>

• PivotFilters <x:filters>

• RowHierarchiesUsage <x:rowHierarchiesUsage>

• ColumnHierarchiesUsage <x:colHierarchiesUsage>

• PivotTableDefinitionExtensionList <x:extLst>

[ISO/IEC 29500-1 1st Edition]

18.10.1.73 pivotTableDefinition (PivotTable Definition)

Represents the PivotTable root element for non-null PivotTables. There exists one pivotTableDefinition for each PivotTableDefinition part. The PivotTable definition encompasses the following information:

Structure

• Top-level attributes

• Location information

• Collection of fields

• Fields on the row axis

• Items on the row axis (specific values)

• Fields on the column axis

• Items on the column axis (specific values)

• Fields on the report filter region

• Fields in the values region

• Style information

Outline of the XML for a pivotTableDefinition

<pivotTableDefinition>
<location/>
<pivotFields/>
<rowFields/>
<rowItems/>
  <colFields/>
<colItems/>
<pageFields/>
<dataFields/>
  <conditionalFormats/>
<pivotTableStyleInfo/>
</pivotTableDefinition>

Layout

The reference specified in the ref attribute on the location element specifies the location of the PivotTable body. The data area, row, column, and data fields and value items are located in this area. More specifically, the row fields begin below the A1-most cell in the reference, and the column fields begin adjacent to that cell, in the same row, extending out into the PivotTable body away from column A. [Note: How far below or across the field labels begin are dependent upon how many row, column, and data fields are shown in the PivotTable. More detail is provided below. end note]

[Note: All layout discussion and examples are given for outline mode layout. There two additional layout modes: compact and tabular. See Other layout modes below for a discussion of how those differ from outline mode. end note]

When encountering sheet boundaries, the PivotTable is truncated rather than wrapped, and as much as possible shall be shown.

The graphics given in this section are meant to illustrate layout only, and do not require implementation of any implied controls, like dropdowns or expand/collapse functionality.

Page Field Layout

In the above picture, SSN is a page field, State and City are row fields, and Amount is a data field. There are no column fields.

Page fields allow you to filter the entire PivotTable report to display data for a single item or all items.

The page field area always ends (vertically) so that there is always 1 row of space between the page field area and the top row of the PivotTable body, and always begins (horizontally) in the same column as the A1-most column of the PivotTable body. Each page field occupies two cells: the A1-most for displaying the field name, and the next cell over for displaying the selected item values. [Example: (see above picture) If the top row in the PivotTable body reference is row 4, then page field layout ends (vertically) in row 2, and if the A1-most column of the PivotTable body is column B, then page field layout begins (horizontally) in column B. end example]

Aside from the number of fields in the page field area, there are two attributes of pivotTableDefinition that affect page field layout: pageOverThenDown and pageWrap. pageOverThenDown = 1 specifies that when there is more than 1 page field, lay them out horizontally across the sheet (extending in the direction of the PivotTable body area, away from column A) until the maximum specified in pageWrap is reached, and then begin a new row. If the pageWrap value is high and there are many page fields, then it is possible (and allowed) for page fields to extend beyond the edge of the PivotTable body. When laying out page fields in the same row (side by side), each shall be separated by a single column. However, multiple rows of page fields are not separated by single rows between them. pageOverThenDown = 0 specifies that when there is more than 1 page field, lay them out vertically down the sheet (always keeping 1 row of space between the PivotTable body and page field area) until the maximum specified in pageWrap is reached, and then begin a new column. Again, for multiple page fields, if they shall occupy more than 1 column, then each column of page fields is separated by a single column, and multiple rows of page fields are not separated by single rows between them.

[Example: This example shows a PivotTable body occupying B5:B6 and 6 page fields in the page field area, where pageOverThenDown = 0 and pageWrap = 2. This means that the first column of the page field area contains 2 page fields, and then, because the pageWrap value only allows 2 page fields per column, a new column of page fields is started, and so on until all 6 page fields are shown.

The order of assignment of position within page field layout for this example is:

• Postal Code

• State

• City

• SSN

• Last Name

• Home Phone

Aside from the 6 page fields, the only other field in this PivotTable example is a data field called Amount.

end example]

[Note: When the user gestures to add a page field and there are not enough free cells above the PivotTable body area to allow for page fields to be added, the application must determine the best response. The application may decide to shift the PivotTable down some number of rows to make room, or overwrite existing data or features that might be above the PivotTable, or simply block the user gesture completely. In any result, however, the application should adhere to the layout principles given above. end note]

Row Field Layout

The State and City fields are row fields, SSN is a page field, there are no column fields, and Amount is a data field.

Row fields provide for and specify how the data is summarized, grouped, and viewed as rows in the PivotTable.

The row field area always begins in the A1-most column of the PivotTable body area. The layout of page fields does not affect the layout of row fields.

Row Field Layout - 1 Row Field and 0 Column Fields

When there is only 1 row field and 0 column fields,

the first row field is located in the A1-most cell of the PivotTable body, and

the values for that field are expressed in the cells directly under that row field, in the same column.

[Example:

In this example, there are no page fields, no column fields, State is a row field, and Amount is a data field. end example]

Row Field Layout - 2 or More Row Fields and 0 Column Fields

When there are 2 or more row fields and 0 column fields to be displayed,

• the row field labels are located adjacent to each other and in the same row as the first row field label

• Each corresponding set of values for the row field in question are located in the cells under that row field (same column)

• Innermost row field values (the ones closest to the data summary area) are grouped and organized by values in the next outer row field, in the following fashion: starting with the outermost row field, the first value is listed. For the next innermost row field, starting on the next row and over one column (toward the data summary area), the value list for that field begins. If that is the innermost row field, all values are listed for that row field, and then moving down a row and back to the outer column, the next value for the outermost row field is listed. If there are more inner row fields, the same layout rules apply until the innermost row field is reached.

• In this case of 0 column fields, only the top row of the PivotTable body is used for row field labels.

[Example:

In this example Postal Code, State, and City are row fields and Amount is a data field. There are no page fields and no column fields. end example]

Row Field Layout - 1 or More Row Fields and 1 or More Column Fields

When there are row fields and 1 or more column fields, the row fields are not located in the topmost row of the PivotTable body. Instead the row fields are located in the n+1st topmost row of the PivotTable body, where n is the number of column fields in the PivotTable.

[Example:

This example shows 3 column fields in the PivotTable (Postal Code, State, and City), a single row field Last Name, and a single data field Amount. The PivotTable body area begins at B5 and the row field label Last Name is located in the 4th row of the PivotTable body area, in row 8 of the spreadsheet, cell B8. Since Last Name is the only row field in this example, its row field values begin and are listed directly under the label. end example]

Column Field Layout

State and City are column fields, Last Nameis a row field, and Amount is a data field.

Column fields provide for and specify how the data is summarized, grouped, and viewed horizontally in the PivotTable.

The layout of page fields does not affect the layout of column fields.

The column field label area is always located in the top row of the PivotTable body.

Column Field Layout - 0 Row Fields and 0 Data Fields

• When there are no row fields and no data fields, then the first column field is located in the A1-most column of the PivotTable body.

• When there are multiple column fields

• the labels are located adjacent to each other in the same row as the first column field label.

• Each corresponding set of values for each of the column fields are located in the rows directly below the column field label row and above the data area, one row of values for each column field.

• The first column field’s values are located in the row directly under the column field row.

• Column field values are displayed starting directly underneath the first column field label’s cell and filling adjacent cells in the same row. The second column field’s values are located two rows under the column field label row, and values are again displayed starting directly underneath the first column field label’s cell, filling adjacent cells in the same manner as the first set of values. The layout of column field values continues in this way until all column field values are displayed.

Inner column field values (the ones closer to the data summary area) are grouped and organized by values in the next outer column field, similarly to how row field values are grouped. [Example:

In this example, State and City are column fields, and there are no row fields, no page fields, and no data fields. end example]

Column Field Layout – 1 or More Column Fields and 1 or More Row Fields

When there are 1 or more column fields and 1 or more row fields in the PivotTable, then:

• First, row fields are displayed according to the row field layout described earlier

• The first column field label is located in the top row of the PivotTable body area, and adjacent to any row field labels that are displayed.

• Multiple column fields shall be displayed as described earlier

[Example: In this example, State and City are column fields, Amount is a data field, and Last Name is a row field.

end example]

Data Field Layout

Last Name is a row field, Sum of Amount is a data field label, and the data underneath Sum of Amount are the summarized data values.

Data fields specify which fields are summarized in the PivotTable report.

The summarized data always appears below the column field and value area, and any row field values are closer to column A than any of the summarized data. When there are no row fields and no column fields, the summarized data is located directly under the A1-most cell of the PivotTable body. Each cell in the summarized data area represents an aggregation of a set of records. The set of records that a particular cell is summarizing is determined by looking at the row field value(s) and column field value(s) that intersect on that particular cell, and then determining which records in the source data contain all of those row and column field values.

Data Field Layout - 0 Row Fields and 0 Column Fields and 1 Data Field

When there are no row fields and no column fields and only 1 data field being summarized, the data field label is located in the A1-most cell of the PivotTable body.

[Example:

In this example there is only 1 field in the PivotTable, a data field Amount. end example]

Data Field Layout - More Than 1 Data Field

When there is more than 1 data field being summarized,

• An additional field (in these examples labeled “Values”, but the label can be specified by the user) is added to the field list, located as either a row field label or a column field label (depending on user choice and behaviour as specified by the dataOnRows and dataPosition attributes), and

• each data field being summarized is displayed either in the row area (when the additional field is a row field) as if it were an item value of that row field (see row field layout description above), or in the column area (when the additional field is a column field) as if it were an item value of that column field (see column field layout description above).

[Example:

In this example there are 2 data fields Amount and Tax. There are no page fields, no column fields, no row fields, and the additional field labeled Values is placed on the column area.

Above is the same PivotTable, with the Values field placed on the row area.

end example]

Data Field Layout - 0 Row Fields, 1 or More Column Fields, and 1 Data Field

When there are no row fields, 1 or more column fields, and only 1 data field being summarized, the data field label is located in the A1-most column of the PivotTable body, directly under the column field area.

[Example:

In this example there is 1 column field State and 1 data field Amount. There are no row fields or page fields. end example]

Data Field Layout - 0 Column Fields, 1 or More Row Fields, and 1 Data Field

When there are no column fields, 1 or more row fields, and only 1 data field being summarized, the data field label is located in the same row as the row field labels, above the data summary area.

[Example:

In this example there is 1 data field Amount and 1 row field State. There are no column fields or page fields. end example]

Subtotal and grand total layout

If subtotals are on, the values for row subtotals are placed at either the top of each group of data being summarized or at the bottom of each group, as indicated by the subtotalTop attribute value on the pivotField element. Row subtotal values appear in the same column as the data being subtotalled. If placed at the top of the group, then the subtotal value for the group appears in the row above the group of values, in the same row as the group’s parent row field value. When there is only a single row field, no subtotal is shown.

[Example:

In this example, there are 3 row fields (Postal Code, State, and City) and 1 data field Amount.

end example]

If row subtotals are placed at the bottom of each data group, then a new row is inserted directly below the data group in question, and a new row field value is inserted, in the same column as the row field in question, whose caption indicates that this row represents a subtotal value.

[Example:

In this example, there are 3 row fields (Postal Code, State, and City) and 1 data field Amount.

end example]

If subtotals are on, for column subtotals a new column is inserted directly after the data group being subtotalled. A new column field value is inserted, in the same row as the column field in question, whose caption indicates that this column represents a subtotal value. When there is only a single column field, no subtotal is shown.

[Example:

In this example, there are 3 column fields (Postal Code, State, and City) and a data field Amount. end example]

If row grand totals are on and there are column fields, a new column item is inserted at the very edge of the PivotTable body furthest away from column A, in the same row as the outermost column field values. The caption indicates that this is a grand total, and the values total all values across the row. When row grand totals are on but there are no column fields, no row grand total is shown.

[Example:

In this example there is 1 column field State and 1 data field Amount, and row grand totals are on.

end example]

When column grand totals are on and there are row fields, a new row item is inserted at the very bottom of the PivotTable body, in the same column as the outermost row field values. The caption indicates that this is a grand total, and the values total all values in the column. When column grand totals are on but there are no row fields, no column grand total is shown.

[Example:

In this example there is 1 row field State and 1 data field Amount, and column grand totals are on.

end example]

Other Layout Modes

A PivotTable can be displayed in Compact, Outline, or Tabular form. In addition, Classic layout can be applied to any of the 3 layout forms.

Outline mode has been discussed in the above sections, and all examples are shown using outline mode with classic layout off (gridDropZones = 0).

For Compact mode, the layout differs from outline mode by:

• Instead of multiple row fields occupying multiple columns, the A1-most column of the PivotTable body contains all row field labels and values. A single label, “Row Labels”, is located where the first (outermost) row label is placed. When there are multiple row fields, the outermost list of values is not indented, then next inner row field values are indented (as specified in the indent attribute), and so on until each set of values for inner row fields are shown.

• Instead of multiple column fields being listed and located across a row, the first column field position is labeled “Column Labels”, and there is only this label, located in the first column field position.

[Example:

Outline mode:

The above picture shows 2 column fields (Postal Code and Last Name), 1 data field (Amount), and 2 row fields (State and City). There are no page fields shown.

Same PivotTable in compact mode:

The above picture shows all column field labels collapsed into a single label Column Labels and all row field labels collapsed into a single label Row Labels. There is 1 data field Amount and no page fields. end example]

For Tabular mode, the layout differs from outline mode by:

Instead of beginning new inner row field values on the next row down from the outer row field value parent, the first next-inner row field value is located on the same row as the parent value.

Row subtotals can only appear at the bottom of a group, not at the top

[Example:

Outline mode:

The above picture shows 3 row fields (Postal Code, State, and City) and 1 data field, Amount.

Same PivotTable in tabular mode:

The above picture shows 3 row fields (Postal Code, State, and City) and 1 data field, Amount.

end example]

For Classic layout, the layout differs by:

• When there are row fields, no column fields, and 1 data field, instead of displaying the data field label adjacent to and in the same row as the row field labels, the data field label is located in the A1-most cell of the PivotTable body, and the row directly under this cell contains the row field labels.

• In the exact location where the data field label is located when classic layout is off, a label titled “Total” is displayed when classic layout is on.

[Example:

Outline mode, classic layout off:

The above picture shows 3 row fields (Postal Code, State, and City) and 1 data field, Amount.

Same PivotTable in Outline mode, classic layout applied:

The above picture shows 3 row fields (Postal Code, State, and City) and 1 data field, Amount.

end example]

Parent Elements
Root element of SpreadsheetML Pivot Table part

Child Elements	Subclause
chartFormats (PivotChart Formats)	§18.10.1.13
colFields (Column Fields)	§18.10.1.14
colHierarchiesUsage (Column OLAP Hierarchy References)	§18.10.1.15
colItems (Column Items)	§18.10.1.17
conditionalFormats (Conditional Formats)	§18.10.1.19
dataFields (Data Fields)	§18.10.1.23
extLst (Future Feature Data Storage Area)	§18.2.10
filters (Filters)	§18.10.1.34
formats (PivotTable Formats)	§18.10.1.36
location (PivotTable Location)	§18.10.1.49
pageFields (Page Field Items)	§18.10.1.63
pivotFields (PivotTable Fields)	§18.10.1.70
pivotHierarchies (PivotTable OLAP Hierarchies)	§18.10.1.71
pivotTableStyleInfo (PivotTable Style)	§18.10.1.74
rowFields (Row Fields)	§18.10.1.81
rowHierarchiesUsage (Row OLAP Hierarchy References)	§18.10.1.82
rowItems (Row Items)	§18.10.1.84

Attributes	Description
applyAlignmentFormats (Apply Alignment Formats)	If true apply legacy table autoformat alignment properties. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
applyBorderFormats (Apply Border Formats)	If true apply legacy table autoformat border properties. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
applyFontFormats (Apply Font Formats)	If true apply legacy table autoformat font properties. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
applyNumberFormats (Apply Number Formats)	If true apply legacy table autoformat number format properties. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
applyPatternFormats (Apply Pattern Formats)	If true apply legacy table autoformat pattern properties. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
applyWidthHeightFormats (Apply Width / Height Formats)	If true apply legacy table autoformat width/height properties. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
asteriskTotals (Asterisk Totals)	Specifies a boolean value that indicates whether an asterisks should be displayed in subtotals and totals when visual totals are not used in OLAP -based PivotTables. A value of 1 or true indicates an asterisks are displayed in subtotals and totals for OLAP PivotTables when visual tools are not available. A value of 0 or false indicates an asterisk will not be displayed. This attribute depends on the implementation and availability of visual tools in the application user interface. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
autoFormatId (Auto Format Id)	Identifies which legacy table autoformat to apply. Annex D contains a listing of the supported PivotTable AutoFormats, example formatting, and a sample workbook with each of those AutoFormats applied. The possible values for this attribute are defined by the W3C XML Schema unsignedInt datatype.
cacheId (PivotCache Definition Id)	Specifies the identifier of the related PivotCache definition. This Id is listed in the pivotCaches collection in the workbook part. The possible values for this attribute are defined by the W3C XML Schema unsignedInt datatype.
chartFormat (Chart Format Id)	Specifies the next chart formatting identifier to use on the PivotTable. The possible values for this attribute are defined by the W3C XML Schema unsignedInt datatype.
colGrandTotals (Grand Totals On Columns)	Specifies a boolean value that indicates whether grand totals should be displayed for the PivotTable columns. A value of 1 or true indicates grand totals should be displayed. A value of 0 or false indicates grand totals should not be displayed for PivotTable columns. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
colHeaderCaption (Column Header Caption)	Specifies the string to be displayed in column header in compact mode. This attribute depends on whether the application implements a compact mode for displaying PivotTables in the user interface. The possible values for this attribute are defined by the ST_Xstring simple type (§22.9.2.19).
compact (Compact New Fields)	Specifies a boolean value that indicates whether new fields should have their compact flag set to true. A value of 1 or true indicates new fields should default to compact mode equal to true. A value of 0 or false indicates new fields should default to compact mode equal to false. This attribute depends on whether the application implements a compact mode in the user interface. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
compactData (Compact Data)	Specifies a boolean value that indicates whether the field next to the data field in the PivotTable should be displayed in the same column of the spreadsheet The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
createdVersion (PivotCache Created Version)	Specifies the version of the application that created the cache. This attribute is application-dependent. The possible values for this attribute are defined by the W3C XML Schema unsignedByte datatype.
customListSort (Custom List AutoSort)	Specifies a boolean value that indicates whether the "custom lists" option is offered when sorting this PivotTable. A value of 1 or true indicates custom lists are offered when sorting this PivotTable. A value of 0 or false indicates custom lists are not offered. This attribute depends on the implementation of sorting features in the application. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
dataCaption (Data Field Header Name)	Specifies the name of the value area field header in the PivotTable. This caption is shown when the PivotTable when two or more fields are in the values area. The possible values for this attribute are defined by the ST_Xstring simple type (§22.9.2.19).
dataOnRows (Data On Rows)	Specifies a boolean value that indicates whether the field representing multiple fields in the data region is located in the row area or the column area. A value of 1 or true indicates that this field is located in the row area. A value of 0 or false indicates that this field is located in the column area. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
dataPosition (Default Data Field Position)	Specifies the position for the field representing multiple data field in the PivotTable, whether that field is located in the row area or column area. Missing attribute indicates this field is last, or innermost in the field list. 0 indicates this field is first, or outermost in the field list. 1 indicates this field is second in the field list. 2 indicates this field is third in the field list, and increasing values follow this pattern. If this value is higher than the number of fields in the field list, then this field is last, or innermost in the field list. The possible values for this attribute are defined by the W3C XML Schema unsignedInt datatype.
disableFieldList (Disable Field List)	Specifies a boolean value that indicates whether to disable the PivotTable field list. A value of 1 or true indicates the field list, or similar mechanism for selecting fields in the user interface, is disabled. A value of 0 or false indicates the field list is enabled. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
editData (Allow Edit Data)	Specifies a boolean value that indicates whether the user is allowed to edit the cells in the data area of the PivotTable. A value of 1 or true indicates the user can edit values in the data area. A value of 0 or false indicates the cells in the data area are not editable. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
enableDrill (Enable Drill Down)	Specifies a boolean value that indicates whether the user is prevented from drilling down on a PivotItem or aggregate value. A value of 1 or true indicates the user can drill down on a pivot item or aggregate value. A value of 0 or false indicates the user is prevented from drilling down pivot item. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
enableFieldProperties (Enable Field Properties)	Specifies a boolean value that indicates whether the user is prevented from displaying PivotField properties. A value of 1 or true indicates the user can display pivot field properties. A value of 0 or false indicates the user cannot display pivot field properties. This attribute depends on how pivot field properties are exposed in the application user interface. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
enableWizard (Enable PivotTable Wizard)	Specifies a boolean value that indicates whether the user is prevented from displaying the PivotTable wizard. A value of 1 or true indicates the user can display the PivotTable wizard. A value of 0 or false indicates the user can not display the PivotTable wizard. This attribute depends on whether the application exposes a wizard or similar mechanism for creating and working with PivotTables in the user interface. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
errorCaption (Error Caption)	Specifies the string to be displayed in cells that contain errors. The possible values for this attribute are defined by the ST_Xstring simple type (§22.9.2.19).
fieldListSortAscending (Default Sort Order)	Specifies a boolean value that indicates whether fields in the PivotTable are sorted in non-default order in the field list. A value of 1 or true indicates fields for the PivotTable are sorted in the field list. The sort order from the data source is applied for range-based PivotTables. Alphabetical sorting is applied for external data PivotTables. A value of 0 or false indicates fields in the field list are not sorted. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
fieldPrintTitles (Field Print Titles)	Specifies a boolean value that indicates whether the row and column titles from the PivotTable should be printed. A value of 1 or true indicates row and column titles should be printed. A value of 0 or false indicates row and column titles should not be printed. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
grandTotalCaption (Grand Totals Caption)	Specifies the string to be displayed for grand totals. The possible values for this attribute are defined by the ST_Xstring simple type (§22.9.2.19).
gridDropZones (Enable Drop Zones)	Specifies a boolean value that indicates whether the in-grid drop zones should be displayed at runtime, and whether classic layout is applied. A value of 1 or true indicates in-grid drop zones should be displayed and classic layout should be applied to the PivotTable. A value of 0 or false indicates in-grid drop zones should be disabled and classic layout should not be applied. [Note: Grid drop zones are optional runtime UI, determined by the application, that indicate to the user the locations of the page, row, column, and data fields in the PivotTable report. See layout discussion under pivotTableDefinition for the precise locations of these areas. end note] The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
immersive (Stop Immersive UI)	Specifies a boolean value that indicates whether PivotTable immersive experience user interface should be turned off. A value of 1 or true indicates the PivotTable immersive experience should be turned off for this PivotTable. A value of 0 or false indicates the immersive experience should be left on. This attribute depends on whether the application implements an immersive experience in the user interface. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
indent (Indentation for Compact Axis)	Specifies the indentation increment for compact axis and can be used to set the Report Layout to Compact Form. The possible values for this attribute are defined by the W3C XML Schema unsignedInt datatype.
itemPrintTitles (Item Print Titles)	Specifies a boolean value that indicates whether PivotItem names should be repeated at the top of each printed page. A value of 1 or true indicates pivot items names should be repeated at the top of each page. A value of 0 or false indicates should not be repeated. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
mdxSubqueries (MDX Subqueries Supported)	Specifies a boolean value that indicates whether MDX sub-queries are supported by OLAP data provider for this PivotTable. A value of 1 or true indicates MDX sub-queries are supported by the OLAP data provider. A value of 0 or false indicates MDX sub-queries are not supported. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
mergeItem (Merge Titles)	Specifies a boolean value that indicates whether row or column titles that span multiple cells should be merged into a single cell. A value of 1 or true indicates that titles that span multiple cells are merged into a single cell. A value of 0 or false indicates titles are not merged. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
minRefreshableVersion (Minimum Refreshable Version)	Specifies the minimum version of the application required to update this PivotTable view. This attribute is application-dependent. The possible values for this attribute are defined by the W3C XML Schema unsignedByte datatype.
missingCaption (Caption for Missing Values)	Specifies the string to be displayed in cells with no value The possible values for this attribute are defined by the ST_Xstring simple type (§22.9.2.19).
multipleFieldFilters (Multiple Field Filters)	Specifies a boolean value that indicates whether the fields of a PivotTable can have multiple filters set on them. A value of 1 or true indicates the fields of a PivotTable can have multiple filters. A value of 0 or false indicates the fields of a PivotTable can only have a simple filter. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
name (Name)	Specifies the PivotTable name. The possible values for this attribute are defined by the ST_Xstring simple type (§22.9.2.19).
outline (Outline New Fields)	Specifies a boolean value that indicates whether new fields should have their outline flag set to true. A value of 1 or true indicates new fields are created with outline equal to true. A value of 0 or false indicates new fields are created with outline equal to false. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
outlineData (Outline Data Fields)	Specifies a boolean value that indicates whether data fields in the PivotTable should be displayed in outline form. A value of 1 or true indicates data fields will display in outline form. A value of 0 or false indicates data fields will not display in outline form. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
pageOverThenDown (Page Over Then Down)	Specifies a boolean value that indicates how the page fields are laid out when there are multiple PivotFields in the page area. A value of 1 or true indicates the fields will display "Over, then down" A value of 0 or false indicates the fields will display "down, then Over" The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
pageStyle (Page Header Style Name)	Specifies the name of the style to apply to each of the field item headers in the page area of the PivotTable. The possible values for this attribute are defined by the ST_Xstring simple type (§22.9.2.19).
pageWrap (Page Wrap)	Specifies the number of page fields to display before starting another row or column. The possible values for this attribute are defined by the W3C XML Schema unsignedInt datatype.
pivotTableStyle (Table Style Name)	Specifies the name of the style to apply to the main table area of the PivotTable. The possible values for this attribute are defined by the ST_Xstring simple type (§22.9.2.19).
preserveFormatting (Preserve Formatting)	Specifies a boolean value that indicates whether the formatting applied by the user to the PivotTable cells is discarded on refresh. A value of 1 or true indicates the formatting applied by the end user is discarded on refresh. A value of 0 or false indicates the end-user formatting is retained on refresh. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
printDrill (Print Drill Indicators)	Specifies a boolean value that indicates whether drill indicators expand collapse buttons should be printed. A value of 1 or true indicates that these buttons should be printed. A value of 0 or false indicates that these buttons should not be printed. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
published (Data Fields Published)	Specifies a boolean value that indicates whether data fields in the PivotTable are published and available for viewing in a server rendering environment. A value of 1 or true indicates that the data fields in the PivotTable are published and shall be available for viewing in a server rendering environment. A value of 0 or false indicates that the data fields in the PivotTable are not published and shall not be available for viewing in a server rendering environment. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
rowGrandTotals (Row Grand Totals)	Specifies a boolean value that indicates whether grand totals should be displayed for the PivotTable rows. The default value for this attribute is true. A value of 1 or true indicates grand totals are displayed for the PivotTable rows. A value of 0 or false indicates grand totals will not be displayed. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
rowHeaderCaption (Row Header Caption)	Specifies the string to be displayed in row header in compact mode. The possible values for this attribute are defined by the ST_Xstring simple type (§22.9.2.19).
showCalcMbrs (Show Calculated Members)	Specifies a boolean value that indicates whether calculated members should be shown in the PivotTable view. This attribute applies to PivotTables from OLAP-sources only. A value of 1 or true indicates that calculated members should be shown. A value of 0 or false indicates calculated members should not be shown. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
showDataDropDown (Show Drop Down)	Specifies a boolean value that indicates whether the drop-down lists for the fields in the PivotTable should be hidden. This attribute depends on whether the application implements drop down lists or similar mechanism in the user interface. A value of 1 or true indicates drop down lists are displayed for fields. A value of 0 or false indicates drop down lists will not be displayed. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
showDataTips (Show ToolTips on Data)	Specifies a boolean value that indicates whether tooltips should be displayed for PivotTable data cells. A value of 1 or true indicates tooltips are displayed. A value of 0 or false indicates tooltips will not be displayed. This attribute depends on whether the application employs tooltips or similar mechanism in the user interface. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
showDrill (Show Expand Collapse)	Specifies a boolean value that indicates whether drill indicators should be hidden. A value of 1 or true indicates drill indicators are displayed. A value of 0 or false indicates drill indicators will not be displayed. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
showDropZones (Show Drop Zones)	Specifies a boolean value that indicates whether the PivotTable should display large drop zones when there are no fields in the data region. A value of 1 or true indicates a large drop zone is displayed. A value of 0 or false indicates a large drop zone will not be displayed. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
showEmptyCol (Show Empty Column)	Specifies a boolean value that indicates whether to include empty columns in the table. A value of 1 or true indicates empty columns are included in the PivotTable. A value of 0 or false indicates empty columns are excluded. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
showEmptyRow (Show Empty Row)	Specifies a boolean value that indicates whether to include empty rows in the table. A value of 1 or true indicates empty rows are included in the PivotTable. A value of 0 or false indicates empty rows are excluded. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
showError (Show Error)	Specifies a boolean value that indicates whether to show error messages in cells. A value of 1 or true indicates error messages are shown in cells. A value of 0 or false indicates error messages are shown through another mechanism the application provides in the user interface. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
showHeaders (Show Field Headers)	Specifies a boolean value that indicates whether to suppress display of pivot field headers. A value of 1 or true indicates field headers are shown in the PivotTable. A value of 0 or false indicates field headers are excluded. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
showItems (Show Item Names)	Specifies a boolean value that indicates whether to display item names when adding a field onto a PivotTable that has no data fields. A value of 1 or true indicates item names are displayed. A value of 0 or false indicates item names will not be displayed. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
showMemberPropertyTips (Show Member Property ToolTips)	Specifies a boolean value that indicates whether member property information should be omitted from PivotTable tooltips. A value of 1 or true indicates member property information is included. A value of 0 or false indicates member property information is excluded. This attribute depends on whether the application employs tooltips or similar mechanism in the user interface. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
showMissing (Show Missing)	Specifies a boolean value that indicates whether to show a message in cells with no value. A value of 1 or true indicates to show a message string in cells without values. A value of 0 or false indicates no message string will shown in cells without values. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
showMultipleLabel (Show Multiple Labels)	Specifies a boolean value that indicates whether a page field with multiple selected items should display "(multiple items)" instead of "All". This attribute applies only to non-OLAP PivotTables. The messages displayed depend on the application implementation. A value of 1 or true indicates a different message string is displayed for a page field with multiple items. A value of 0 or false indicates the same message string is displayed for all page fields. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
subtotalHiddenItems (Subtotal Hidden Items)	Specifies a boolean value that indicates whether data for hidden pivotItems for PivotFields in the data area should be included in subtotals. A value of 1 or true indicates that data for hidden pivot items in the data area is included in subtotals. A value of 0 or false indicates hidden pivot items will not be included in subtotals. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
tag (PivotTable Custom String)	Specifies a user-defined string that is associated with this PivotTable. The possible values for this attribute are defined by the ST_Xstring simple type (§22.9.2.19).
updatedVersion (PivotTable Last Updated Version)	Specifies the version of the application that last updated the PivotTable view. This attribute is application-dependent. The possible values for this attribute are defined by the W3C XML Schema unsignedByte datatype.
useAutoFormatting (Auto Formatting)	Specifies a boolean value that indicates whether legacy auto formatting has been applied to the PivotTable view. A value of 1 or true indicates that legacy auto formatting has been applied to the PivotTable. A value of 0 or false indicates that legacy auto formatting has not been applied to the PivotTable. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.
vacatedStyle (Vacated Style)	Specifies the name of the style to apply to the cells left blank when a PivotTable shrinks during a refresh operation The possible values for this attribute are defined by the ST_Xstring simple type (§22.9.2.19).
visualTotals (Total Visual Data)	Specifies a boolean value that indicates whether totals should be based on visible data only. This attribute applies to OLAP PivotTables only. A value of 1 or true indicates subtotals are computed on visible data only. A value of 0 or false indicates subtotals are computed on all data. The possible values for this attribute are defined by the W3C XML Schema boolean datatype.

[Note: The W3C XML Schema definition of this element’s content model (CT_pivotTableDefinition) 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

PivotTableDefinition Members

DocumentFormat.OpenXml.Spreadsheet Namespace