[Table of Contents] [docx version]

SpreadsheetML Reference Material - Table of Contents

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>

Illustrations

Layout

 

Row field

The blue field is a row field.

A PivotTable report that has more than one row field has one inner row field (Sport, in the example below), the one closest to the data area. Any other row fields are outer row fields (Region, in the example below). Items in the outermost row field are displayed only once, but items in the rest of the row fields are repeated as needed.

Region is an outer row field; Sport is an inner row field.

Column field

The blue field is a column field.

Page field

The blue field is a page field.

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

Data field

The blue field is a data field.

Data fields provide the data values to be summarized. Usually data fields contain numbers, which are combined with the Sum summary function, but data fields can also contain text, in which case the PivotTable report uses the Count summary function.

If a report has more than one data field, a single field button named Data appears in the report for access to all of the data fields.

[Example:

<?xml …?>
<sh:pivotTableDefinition xmlns:sh="…" name="PivotTable1" cacheId="3"
applyNumberFormats="0" applyBorderFormats="0" applyFontFormats="0"
applyPatternFormats="0" applyAlignmentFormats="0" applyWidthHeightFormats="1"
dataCaption="Values" updatedVersion="3" minRefreshableVersion="3"
showCalcMbrs="0" useAutoFormatting="1" colGrandTotals="0" itemPrintTitles="1"
createdVersion="3" indent="0" outline="1" outlineData="1"
multipleFieldFilters="0" fieldListSortAscending="1">

<sh:location ref="B5:H49" firstHeaderRow="1" firstDataRow="4"
 firstDataCol="1" rowPageCount="2" colPageCount="1"/>

<sh:pivotFields count="28">
 <sh:pivotField axis="axisPage" showAll="0" includeNewItemsInFilter="1">
 <sh:items count="5">
 <sh:item x="1"/>
 <sh:item x="2"/>
 <sh:item x="3"/>
 <sh:item x="4"/>
 <sh:item x="5"/>
 </sh:items>
 </sh:pivotField>

<sh:pivotField compact="0" showAll="0" includeNewItemsInFilter="1"/>

<sh:pivotField showAll="0" includeNewItemsInFilter="1"/>

<sh:pivotField axis="axisCol" showAll="0" includeNewItemsInFilter="1">

<sh:items count="5">
 <sh:item x="0"/>
 <sh:item h="1" x="1"/>
 <sh:item h="1" x="2"/>
 <sh:item h="1" x="3"/>
 <sh:item t="default"/>
 </sh:items>
 </sh:pivotField>

<sh:pivotField axis="axisCol" showAll="0" includeNewItemsInFilter="1">
 <sh:items count="5">
 <sh:item x="2"/>
 <sh:item x="3"/>
 <sh:item x="0"/>
 <sh:item x="1"/>
 <sh:item t="default"/>
 </sh:items>
 </sh:pivotField>

<sh:pivotField showAll="0" includeNewItemsInFilter="1"/>
 <sh:pivotField dataField="1" showAll="0" includeNewItemsInFilter="1"/>
 <sh:pivotField showAll="0" includeNewItemsInFilter="1"/>
 </sh:pivotFields>

<sh:rowFields count="2">
 <sh:field x="2"/>
 <sh:field x="5"/>
 </sh:rowFields>

<sh:rowItems count="3">
 <sh:i r="1">
 <sh:x v="236"/>
 </sh:i>

<sh:i r="1">
 <sh:x v="232"/>
 </sh:i>

<sh:i t="grand">
 <sh:x/>
 </sh:i>
 </sh:rowItems>

<sh:colFields count="3">
 <sh:field x="-2"/>
 <sh:field x="14"/>
 <sh:field x="15"/>
 </sh:colFields>

<sh:colItems count="3">
 <sh:i>
 <sh:x/>
 <sh:x/>
 <sh:x v="2"/>
 </sh:i>

<sh:i r="2">
 <sh:x v="3"/>
 </sh:i>

<sh:i t="default" r="1">
 <sh:x/>
 </sh:i>
 </sh:colItems>

<sh:pageFields count="2">
 <sh:pageField fld="0" hier="0"/>
 <sh:pageField fld="7" hier="0"/>
 </sh:pageFields>

<sh:dataFields count="2">
 <sh:dataField name="Sum of Sales Amount" fld="25" baseField="0"
baseItem="0"/>
 <sh:dataField name="Sum of Tax Amount" fld="26" baseField="0" baseItem="0"/>
 </sh:dataFields>

<sh:conditionalFormats count="1">
 <sh:conditionalFormat priority="1">
 <sh:pivotAreas count="1">
 <sh:pivotArea type="data" collapsedLevelsAreSubtotals="1">
 <sh:references count="2">
 <sh:reference field="14" count="1" selected="0">
 <sh:x v="0"/>
 </sh:reference>

<sh:reference field="15" count="2" selected="0">
 <sh:x v="2"/>
 <sh:x v="3"/>
 </sh:reference>

</sh:references>
 </sh:pivotArea>
 </sh:pivotAreas>
 </sh:conditionalFormat>
 </sh:conditionalFormats>

<sh:pivotTableStyleInfo name="PivotStyleDark8" showRowHeaders="1"
 showColHeaders="1" showRowStripes="0" showColStripes="0"
showLastColumn="1"/>
</sh:pivotTableDefinition>

end example]

Parent Elements

Root element of SpreadsheetML Pivot Table part

 

Child Elements

Subclause

chartFormats (PivotChart Formats)

§3.10.1.13

colFields (Column Fields)

§3.10.1.14

colHierarchiesUsage (Column OLAP Hierarchy References)

§3.10.1.15

colItems (Column Items)

§3.10.1.17

conditionalFormats (Conditional Formats)

§3.10.1.19

dataFields (Data Fields)

§3.10.1.23

extLst (Future Feature Data Storage Area)

§3.2.10

filters (Filters)

§3.10.1.34

formats (PivotTable Formats)

§3.10.1.36

location (PivotTable Location)

§3.10.1.49

pageFields (Page Field Items)

§3.10.1.63

pivotFields (PivotTable Fields)

§3.10.1.70

pivotHierarchies (PivotTable OLAP Hierarchies)

§3.10.1.71

pivotTableStyleInfo (PivotTable Style)

§3.10.1.74

rowFields (Row Fields)

§3.10.1.81

rowHierarchiesUsage (Row OLAP Hierarchy References)

§3.10.1.82

rowItems (Row Items)

§3.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 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 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 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 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 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 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 on, 1, or true indicates an asterisks will be displayed in subtotals and totals for OLAP PivotTables when visual tools are not available.

 

A value of off, 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 XML Schema boolean datatype.

autoFormatId (Auto Format Id)

Identifies which legacy table autoformat to apply.

 

Here are representations of the supported table autoformats:

 

autoFormatId

Description

0

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

 

 

The possible values for this attribute are defined by the 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 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 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 on, 1, or true indicates grand totals should be displayed.

 

A value of off, 0, or false indicates grand totals should not be displayed for PivotTable columns.

 

The possible values for this attribute are defined by the 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 (§3.18.96).

compact (Compact New Fields)

Specifies a boolean value that indicates whether new fields should have their compact flag set to true.

 

A value of on, 1, or true indicates new fields should default to compact mode equal to true.

 

A value of off, 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 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 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 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 on, 1, or true indicates custom lists are offered when sorting this PivotTable.

 

A value of off, 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 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 (§3.18.96).

dataOnRows (Data On Rows)

Specifies a boolean value that indicates the default orientation for fields in the data region.

 

A value of on, 1, or true indicates vertical orientation.

 

A value of off, 0, or false indicates horizontal orientation.

 

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

dataPosition (Default Data Field Position)

Specifies the default position for the data field in the PivotTable.

 

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

disableFieldList (Disable Field List)

Specifies a boolean value that indicates whether to disable the PivotTable field list.

 

A value of on, 1, or true indicates the field list, or similar mechanism for selecting fields in the user interface, is disabled.

 

A value of off, 0, or false indicates the field list is enabled.

 

The possible values for this attribute are defined by the 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 on, 1, or true indicates the user can edit values in the data area.

 

A value of off, 0, or false indicates the cells in the data area are not editable.

 

The possible values for this attribute are defined by the 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 on, 1, or true indicates the user can drill down on a pivot item or aggregate value.

 

A value of off, 0, or false indicates the user is prevented from drilling down pivot item.

 

The possible values for this attribute are defined by the 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 on, 1, or true indicates the user can display pivot field properties.

 

A value of off, 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 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 on, 1, or true indicates the user may display the PivotTable wizard.

 

A value of off, 0, or false indicates the user may 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 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 (§3.18.96).

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 on, 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 off, 0, or false indicates fields in the field list are not sorted.

 

The possible values for this attribute are defined by the 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 on, 1, or true indicates row and column titles should be printed.

 

A value of off, 0, or false indicates row and column titles should not be printed.

 

The possible values for this attribute are defined by the 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 (§3.18.96).

gridDropZones (Enable Drop Zones)

Specifies a boolean value that indicates whether the in-grid drop zones should be enabled.

 

A value of on, 1, or true indicates in-grid drop zones should be enabled.

 

A value of off, 0, or false indicates in-grid drop zones should be disabled. This attribute depends on how the application implements drop zones in the user interface.

 

The possible values for this attribute are defined by the 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 on, 1, or true indicates the PivotTable immersive experience should be turned off for this PivotTable.

 

A value of off, 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 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 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 on, 1, or true indicates pivot items names should be repeated at the top of each page.

 

A value of off, 0, or false indicates should not be repeated.

 

The possible values for this attribute are defined by the 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 on, 1, or true indicates MDX sub-queries are supported by the OLAP data provider.

 

A value of off, 0, or false indicates MDX sub-queries are not supported.

 

The possible values for this attribute are defined by the 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 on, 1, or true indicates that titles that span multiple cells will be merged into a single cell.

 

A value of off, 0, or false indicates titles are not merged.

 

The possible values for this attribute are defined by the 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 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 (§3.18.96).

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 on, 1, or true indicates the fields of a PivotTable can have multiple filters.

 

A value of off, 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 XML Schema boolean datatype.

name (Name)

Specifies the PivotTable name.

 

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

outline (Outline New Fields)

Specifies a boolean value that indicates whether new fields should have their outline flag set to true.

 

A value of on, 1, or true indicates new fields will be created with outline equal to true.

 

A value of off, 0, or false indicates new fields will be created with outline equal to false.

 

The possible values for this attribute are defined by the 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 on, 1, or true indicates data fields will display in outline form.

 

A value of off, 0, or false indicates data fields will not display in outline form.

 

The possible values for this attribute are defined by the 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 on, 1, or true indicates the fields will display "Over, then down"

 

A value of off, 0, or false indicates the fields will display "down, then Over"

 

The possible values for this attribute are defined by the 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 (§3.18.96).

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 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 (§3.18.96).

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 on, 1, or true indicates the formatting applied by the end user is discarded on refresh.

 

A value of off, 0, or false indicates the end-user formatting is retained on refresh.

 

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

printDrill (Print Drill Indicators)

Specifies a boolean value that indicates whether drill indicators should be printed.

 

A value of on, 1, or true indicates

 

A value of off, 0, or false indicates

 

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

published (Data Fields Published)

Specifies a boolean value that indicates whether data fields in the PivotTable are published.

 

A value of on, 1, or true indicates

 

A value of off, 0, or false indicates

 

The possible values for this attribute are defined by the 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 on, 1, or true indicates grand totals will be displayed for the PivotTable rows.

 

A value of off, 0, or false indicates grand totals will not be displayed.

 

The possible values for this attribute are defined by the 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 (§3.18.96).

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 on, 1, or true indicates that calculated members should be shown.

 

A value of off, 0, or false indicates calculated members should not be shown.

 

The possible values for this attribute are defined by the 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 on, 1, or true indicates drop down lists will be displayed for fields.

 

A value of off, 0, or false indicates drop down lists will not be displayed.

 

The possible values for this attribute are defined by the 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 on, 1, or true indicates tooltips will be displayed.

 

A value of off, 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 XML Schema boolean datatype.

showDrill (Show Expand Collapse)

Specifies a boolean value that indicates whether drill indicators should be hidden.

 

A value of on, 1, or true indicates drill indicators will be displayed.

 

A value of off, 0, or false indicates drill indicators will not be displayed.

 

The possible values for this attribute are defined by the 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 on, 1, or true indicates a large drop zone will be displayed.

 

A value of off, 0, or false indicates a large drop zone will not be displayed.

 

The possible values for this attribute are defined by the 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 on, 1, or true indicates empty columns will be included in the PivotTable.

 

A value of off, 0, or false indicates empty columns will be excluded.

 

The possible values for this attribute are defined by the 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 on, 1, or true indicates empty rows will be included in the PivotTable.

 

A value of off, 0, or false indicates empty rows will be excluded.

 

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

showError (Show Error)

Specifies a boolean value that indicates whether to show error messages in cells.

 

A value of on, 1, or true indicates error messages will be shown in cells.

 

A value of off, 0, or false indicates error messages will be shown through another mechanism the application provides in the user interface.

 

The possible values for this attribute are defined by the 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 on, 1, or true indicates field headers will be shown in the PivotTable.

 

A value of off, 0, or false indicates field headers will be excluded.

 

The possible values for this attribute are defined by the 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 on, 1, or true indicates item names will be displayed.

 

A value of off, 0, or false indicates item names will not be displayed.

 

The possible values for this attribute are defined by the 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 on, 1, or true indicates member property information will be included.

 

A value of off, 0, or false indicates member property information will be 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 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 on, 1, or true indicates to show a message string in cells without values.

 

A value of off, 0, or false indicates no message string will shown in cells without values.

 

The possible values for this attribute are defined by the 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 on, 1, or true indicates a different message string will be displayed for a page field with multiple items.

 

A value of off, 0, or false indicates the same message string will be displayed for all page fields.

 

The possible values for this attribute are defined by the 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 on, 1, or true indicates that data for hidden pivot items in the data area will be included in subtotals.

 

A value of off, 0, or false indicates hidden pivot items will not be included in subtotals.

 

The possible values for this attribute are defined by the 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 (§3.18.96).

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 XML Schema unsignedByte datatype.

useAutoFormatting (Auto Formatting)

Specifies a boolean value that indicates whether auto formatting has been applied to the PivotTable view.

 

A value of on, 1, or true indicates

 

A value of off, 0, or false indicates

 

The possible values for this attribute are defined by the 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 (§3.18.96).

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 on, 1, or true indicates subtotals will be computed on visible data only.

 

A value of off, 0, or false indicates subtotals will be computed on all data.

 

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

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

<complexType name="CT_pivotTableDefinition">

   <sequence>

   <element name="location" type="CT_Location"/>

   <element name="pivotFields" type="CT_PivotFields" minOccurs="0"/>

   <element name="rowFields" type="CT_RowFields" minOccurs="0"/>

   <element name="rowItems" type="CT_rowItems" minOccurs="0"/>

   <element name="colFields" type="CT_ColFields" minOccurs="0"/>

   <element name="colItems" type="CT_colItems" minOccurs="0"/>

   <element name="pageFields" type="CT_PageFields" minOccurs="0"/>

   <element name="dataFields" type="CT_DataFields" minOccurs="0"/>

   <element name="formats" type="CT_Formats" minOccurs="0"/>

   <element name="conditionalFormats" type="CT_ConditionalFormats" minOccurs="0"/>

   <element name="chartFormats" type="CT_ChartFormats" minOccurs="0"/>

   <element name="pivotHierarchies" type="CT_PivotHierarchies" minOccurs="0"/>

   <element name="pivotTableStyleInfo" minOccurs="0" maxOccurs="1" type="CT_PivotTableStyle"/>

   <element name="filters" minOccurs="0" maxOccurs="1" type="CT_PivotFilters"/>

   <element name="rowHierarchiesUsage" type="CT_RowHierarchiesUsage" minOccurs="0" maxOccurs="1"/>

   <element name="colHierarchiesUsage" type="CT_ColHierarchiesUsage" minOccurs="0" maxOccurs="1"/>

   <element name="extLst" minOccurs="0" type="CT_ExtensionList"/>

   </sequence>

   <attribute name="name" use="required" type="ST_Xstring"/>

   <attribute name="cacheId" use="required" type="xsd:unsignedInt"/>

   <attribute name="dataOnRows" type="xsd:boolean" default="false"/>

   <attribute name="dataPosition" type="xsd:unsignedInt" use="optional"/>

   <attributeGroup ref="AG_AutoFormat"/>

   <attribute name="dataCaption" use="required" type="ST_Xstring"/>

   <attribute name="grandTotalCaption" type="ST_Xstring"/>

   <attribute name="errorCaption" type="ST_Xstring"/>

   <attribute name="showError" type="xsd:boolean" default="false"/>

   <attribute name="missingCaption" type="ST_Xstring"/>

   <attribute name="showMissing" type="xsd:boolean" default="true"/>

   <attribute name="pageStyle" type="ST_Xstring"/>

   <attribute name="pivotTableStyle" type="ST_Xstring"/>

   <attribute name="vacatedStyle" type="ST_Xstring"/>

   <attribute name="tag" type="ST_Xstring"/>

   <attribute name="updatedVersion" type="xsd:unsignedByte" default="0"/>

   <attribute name="minRefreshableVersion" type="xsd:unsignedByte" default="0"/>

   <attribute name="asteriskTotals" type="xsd:boolean" default="false"/>

   <attribute name="showItems" type="xsd:boolean" default="true"/>

   <attribute name="editData" type="xsd:boolean" default="false"/>

   <attribute name="disableFieldList" type="xsd:boolean" default="false"/>

   <attribute name="showCalcMbrs" type="xsd:boolean" default="true"/>

   <attribute name="visualTotals" type="xsd:boolean" default="true"/>

   <attribute name="showMultipleLabel" type="xsd:boolean" default="true"/>

   <attribute name="showDataDropDown" type="xsd:boolean" default="true"/>

   <attribute name="showDrill" type="xsd:boolean" default="true"/>

   <attribute name="printDrill" type="xsd:boolean" default="false"/>

   <attribute name="showMemberPropertyTips" type="xsd:boolean" default="true"/>

   <attribute name="showDataTips" type="xsd:boolean" default="true"/>

   <attribute name="enableWizard" type="xsd:boolean" default="true"/>

   <attribute name="enableDrill" type="xsd:boolean" default="true"/>

   <attribute name="enableFieldProperties" type="xsd:boolean" default="true"/>

   <attribute name="preserveFormatting" type="xsd:boolean" default="true"/>

   <attribute name="useAutoFormatting" type="xsd:boolean" default="false"/>

   <attribute name="pageWrap" type="xsd:unsignedInt" default="0"/>

   <attribute name="pageOverThenDown" type="xsd:boolean" default="false"/>

   <attribute name="subtotalHiddenItems" type="xsd:boolean" default="false"/>

   <attribute name="rowGrandTotals" type="xsd:boolean" default="true"/>

   <attribute name="colGrandTotals" type="xsd:boolean" default="true"/>

   <attribute name="fieldPrintTitles" type="xsd:boolean" default="false"/>

   <attribute name="itemPrintTitles" type="xsd:boolean" default="false"/>

   <attribute name="mergeItem" type="xsd:boolean" default="false"/>

   <attribute name="showDropZones" type="xsd:boolean" default="true"/>

   <attribute name="createdVersion" type="xsd:unsignedByte" default="0"/>

   <attribute name="indent" type="xsd:unsignedInt" default="1"/>

   <attribute name="showEmptyRow" type="xsd:boolean" default="false"/>

   <attribute name="showEmptyCol" type="xsd:boolean" default="false"/>

   <attribute name="showHeaders" type="xsd:boolean" default="true"/>

   <attribute name="compact" type="xsd:boolean" default="true"/>

   <attribute name="outline" type="xsd:boolean" default="false"/>

   <attribute name="outlineData" type="xsd:boolean" default="false"/>

   <attribute name="compactData" type="xsd:boolean" default="true"/>

   <attribute name="published" type="xsd:boolean" default="false"/>

   <attribute name="gridDropZones" type="xsd:boolean" default="false"/>

   <attribute name="immersive" type="xsd:boolean" default="true"/>

   <attribute name="multipleFieldFilters" type="xsd:boolean" default="true"/>

   <attribute name="chartFormat" type="xsd:unsignedInt" default="0"/>

   <attribute name="rowHeaderCaption" type="ST_Xstring"/>

   <attribute name="colHeaderCaption" type="ST_Xstring"/>

   <attribute name="fieldListSortAscending" type="xsd:boolean" default="false"/>

   <attribute name="mdxSubqueries" type="xsd:boolean" default="false"/>

   <attribute name="customListSort" type="xsd:boolean" use="optional" default="true"/>

</complexType>