PivotField Class

PivotTable Field.When the object is serialized out as xml, its qualified name is x:pivotField.

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

[ChildElementInfoAttribute(typeof(PivotFieldExtensionList))]
[ChildElementInfoAttribute(typeof(Items))]
[ChildElementInfoAttribute(typeof(AutoSortScope))]
public class PivotField : OpenXmlCompositeElement

The following table lists the possible child types:

  • Items <x:items>

  • AutoSortScope <x:autoSortScope>

  • PivotFieldExtensionList <x:extLst>


[ISO/IEC 29500-1 1st Edition]

18.10.1.69 pivotField (PivotTable Field)

Represents a single field in the PivotTable. This element contains information about the field, including the collection of items in the field.

[Example:


<pivotField axis="axisRow" allDrilled="1" showAll="0" measureFilter="1"
sortType="descending">
  <items count="8">
<item s="1" c="1" x="0"/>
<item s="1" c="1" x="1"/>
<item c="1" x="2"/>
<item c="1" x="3"/>
<item c="1" x="4"/>
<item c="1" x="5"/>
<item c="1" x="6"/>
<item t="default"/>
</items>
  <autoSortScope>
<pivotArea dataOnly="0" outline="0" fieldPosition="0">
<references count="2">
<reference field="4294967294" count="1" selected="0">
<x v="0"/>
</reference>
        <reference field="25" count="1" selected="0">
<x v="0"/>
</reference>
</references>
</pivotArea>
</autoSortScope>
</pivotField>

end example]

Parent Elements

pivotFields (§18.10.1.70)

Child Elements

Subclause

autoSortScope (AutoSort Scope)

§18.10.1.1

extLst (Future Feature Data Storage Area)

§18.2.10

items (Field Items)

§18.10.1.46

Attributes

Description

allDrilled (All Items Expanded)

Specifies a boolean value that indicates whether all items in the field are expanded. Applies only to OLAP PivotTables.

A value of 1 or true indicates all items in the field are expanded.

A value of 0 or false indicates all items are not expanded. However some items might be expanded.

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

autoShow (Auto Show)

Specifies a boolean value that indicates whether an "AutoShow" filter is applied to this field. This attribute depends on the implementation of filtering in the application.

A value of 1 or true indicates an "AutoShow" filter is applied to the field.

A value of 0 or false indicates an "AutoShow" filter is not applied.

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

avgSubtotal (Average)

Specifies a boolean value that indicates whether to apply the 'Average' aggregation function in the subtotal of this field.

A value of 1 or true indicates the subtotal for this field is 'Average.'

A value of 0 or false indicates a different aggregation function is applied to the subtotal for this field.

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

axis (Axis)

Specifies the region of the PivotTable that this field is displayed.

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

compact (Compact)

Specifies a boolean value that indicates whether the application will display fields compactly in the sheet on which this PivotTable resides.

A value of 1 or true indicates the next field should be displayed in the same column of the sheet.

A value of 0 or false indicates each pivot field will display in its own column in the sheet.

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

countASubtotal (CountA)

Specifies a boolean value that indicates whether to apply the 'countA' aggregation function in the subtotal of this field.

A value of 1 or true indicates the subtotal for this field is 'countA.'

A value of 0 or false indicates a different aggregation function is applied to the subtotal for this field.

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

countSubtotal (Count)

Specifies a boolean value that indicates whether to apply the 'count' aggregation function in the subtotal of this field.

A value of 1 or true indicates the subtotal for this field is 'count.'

A value of 0 or false indicates a different aggregation vfunction is applied to the subtotal for this field.

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

dataField (Data Field)

Specifies a boolean value that indicates whether this field appears in the data region of the PivotTable.

A value of 1 or true indicates this field appears in the data region of the PivotTable.

A value of 0 or false indicates this field appears in another region of the PivotTable.

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

dataSourceSort (Data Source Sort)

Specifies a boolean value that indicates whether sort is applied to this field in the data source.

A value of 1 or true indicates this field is sorted in the data source.

A value of 0 or false indicates this field is not sorted in the data source.

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

defaultAttributeDrillState (Drill State)

Specifies a boolean value that indicates the drill state of the attribute hierarchy in an OLAP-based PivotTable.

A value of 1 or true indicates the attribute hierarchy is expanded.

A value of 0 or false indicates the attribute hierarchy is collapsed.

This attribute is designed to allow the application to issue more optimized queries when all items of each field have the same drill state.

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

defaultSubtotal (Show Default Subtotal)

Specifies a boolean value that indicates whether the default subtotal aggregation function is displayed for this field.

A value of 1 or true indicates the default subtotal aggregation function is displayed for this field.

A value of 0 or false indicates the default aggregation function is not displayed.

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

dragOff (Drag Off)

Specifies a boolean value that indicates whether the field can be removed from the PivotTable.

A value of 1 or true indicates the field can be removed from the PivotTable.

A value of 0 or false indicates the field cannot be removed from the PivotTable.

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

dragToCol (Drag To Column)

Specifies a boolean value that indicates whether the field can be dragged to the column axis.

A value of 1 or true indicates the field can be dragged to the column axis.

A value of 0 or false indicates the field cannot be dragged to the column axis.

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

dragToData (Field Can Drag to Data)

Specifies a boolean value that indicates whether the field can be dragged to the data region.

A value of 1 or true indicates the field can be dragged to the data region.

A value of 0 or false indicates the field cannot be dragged to the data region.

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

dragToPage (Drag Field to Page)

Specifies a boolean value that indicates whether the field can be dragged to the page region.

A value of 1 or true indicates the field can be dragged to the page region.

A value of 0 or false indicates the field cannot be dragged to the page region.

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

dragToRow (Drag To Row)

Specifies a boolean value that indicates whether the field can be dragged to the row axis.

A value of 1 or true indicates the field can be dragged to the row axis.

A value of 0 or false indicates the field cannot be dragged to the row axis.

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

hiddenLevel (Hidden Level)

Specifies a boolean value that indicates whether there is a hidden level in the PivotTable. This attribute applies to OLAP-based PivotTables only.

A value of 1 or true indicates the OLAP PivotTable contains a hidden level.

A value of 0 or false indicates the OLAP PivotTable does not contain any hidden levels.

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

hideNewItems (Hide New Items)

Specifies a boolean value that indicates whether new items that appear after a refresh should be hidden by default.

A value of 1 or true indicates that items that appear after a refresh should be hidden by default.

A value of 0 or false indicates that items that appear after a refresh should be shown by default.

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

includeNewItemsInFilter (Inclusive Manual Filter)

Specifies a boolean value that indicates whether manual filter is in inclusive mode.

A value of 1 or true indicates the manual filter is inclusive.

A value of 0 or false indicates the manual filter is not inclusive.

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

insertBlankRow (Insert Blank Row)

Specifies a boolean value that indicates whether to insert a blank row after each item.

A value of 1 or true indicates that a blank row is inserted after each item.

A value of 0 or false indicates no additional rows are inserted after each item.

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

insertPageBreak (Insert Item Page Break)

Specifies a boolean value that indicates whether to insert a page break after each item.

A value of 1 or true indicates that a page break is inserted after each item.

A value of 0 or false indicates no page breaks are inserted after items.

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

itemPageCount (Items Per Page Count)

Specifies the number of items showed per page in the PivotTable.

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

maxSubtotal (Max Subtotal)

Specifies a boolean value that indicates whether to apply the 'max' aggregation function in the subtotal of this field.

A value of 1 or true indicates that the 'max' aggregation function is applied in the subtotal for this field.

A value of 0 or false indicates another aggregation function is applied in the subtotal for this field.

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

measureFilter (Measure Filter)

Specifies a boolean value that indicates whether field has a measure based filter.

A value of 1 or true indicates the field has a measure-based filter.

A value of 0 or false indicates does not have a measure-based filter.

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

minSubtotal (Min Subtotal)

Specifies a boolean value that indicates whether to apply the 'min' aggregation function in the subtotal of this field.

A value of 1 or true indicates that the 'min' aggregation function is applied in the subtotal for this field.

A value of 0 or false indicates another aggregation function is applied in the subtotal for this field.

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

multipleItemSelectionAllowed (Multiple Field Filters)

Specifies a boolean value that indicates whether the field can have multiple items selected in the page field.

A value of 1 or true indicates the PivotTable can have multiple items selected in the page field.

A value of 0 or false indicates the PivotTable cannot have multiple items selected in the page field. This attribute depends on the application support for selecting multiple items in page fields.

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

name (Field Name)

Specifies the name of the field.

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

nonAutoSortDefault (Auto Sort)

Specifies a boolean value that indicates whether sort operation that is applied to field should be AutoSort operation or simple data sort operation.

A value of 1 or true indicates that an AutoSort operation is applied to the field.

A value of 0 or false indicates a simple data sort operation is applied to the field.

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

numFmtId (Number Format Id)

Specifies the identifier of the number format to apply to this field. Number formats are written to the styles part. See the Styles section (§18.8) for more information on number formats.

Formatting information provided by cell table and by PivotTable need not agree. If the two formats differ, the cell-level formatting takes precedence. If you change the layout the PivotTable, the PivotTable formatting will then take precedence.

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

outline (Outline Items)

Specifies a boolean value that indicates whether the items in this field should be shown in Outline form.

A value of 1 or true indicates the items in this field is shown in Outline form.

A value of 0 or false indicates the items in this field will not be shown in Outline form. This attribute depends on the application support for displaying items in Outline form.

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

productSubtotal (Product Subtotal)

Specifies a boolean value that indicates whether to apply 'product' aggregation function in the subtotal of this field.

A value of 1 or true indicates that the 'product' aggregation function is applied in the subtotal for this field.

A value of 0 or false indicates another aggregation function is applied in the subtotal for this field.

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

rankBy (Auto Show Rank By)

Specifies the index of the data field by which AutoShow will rank.

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

serverField (Server-based Page Field)

Specifies a boolean value that indicates whether this is a server-based page field.

A value of 1 or true indicates this is a server-based page field.

A value of 0 or false indicates this is a local page field.

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

showAll (Show All Items)

Specifies a boolean value that indicates whether to show all items for this field.

A value of 1 or true indicates that all items be shown.

A value of 0 or false indicates items be shown according to user specified criteria.

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

showDropDowns (Show PivotField Header Drop Downs)

Specifies a boolean value that indicates whether to hide drop down buttons on PivotField headers. This attribute depends on the application implementation for filtering in the user interface.

A value of 1 or true indicates the application will display some mechanism for selecting and applying filters – [Example: A dropdown menu end example] – in the user interface.

A value of 0 or false indicates for mechanism for applying a filter is displayed in the user interface.

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

showPropAsCaption (Show As Caption)

Specifies a boolean value that indicates whether to show the property as a member caption.

A value of 1 or true indicates the property is shown as a member caption.

A value of 0 or false indicates the property will not be shown as a member caption.

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

showPropCell (Show Member Property in Cell)

Specifies a boolean value that indicates whether to show the member property value in a PivotTable cell.

A value of 1 or true indicates the property value is shown in a PivotTable cell.

A value of 0 or false indicates the property value will not be shown in a PivotTable cell.

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

showPropTip (Show Member Property ToolTip)

Specifies a boolean value that indicates whether to show the member property value in a tooltip on the appropriate PivotTable cells.

A value of 1 or true indicates the property value is shown in a tooltip in the user interface.

A value of 0 or false indicates the property will not be shown in a tooltip. 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.

sortType (Auto Sort Type)

Specifies the type of sort that is applied to this field.

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

stdDevPSubtotal (StdDevP Subtotal)

Specifies a boolean value that indicates whether to apply the 'stdDevP' aggregation function in the subtotal of this field.

A value of 1 or true indicates that the 'stdDevP' aggregation function is applied in the subtotal for this field.

A value of 0 or false indicates another aggregation function is applied in the subtotal for this field.

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

stdDevSubtotal (StdDev Subtotal)

Specifies a boolean value that indicates whether to use 'stdDev' in the subtotal of this field.

A value of 1 or true indicates that the 'stdDev' aggregation function is applied in the subtotal for this field.

A value of 0 or false indicates another aggregation function is applied in the subtotal for this field.

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

subtotalCaption (Custom Subtotal Caption)

Specifies the custom text that is displayed for the subtotals label.

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

subtotalTop (Subtotals At Top)

Specifies a boolean value that indicates whether to display subtotals at the top of the group. Applies only when Outline its true.

A value of 1 or true indicates a subtotal is display at the top of the group.

A value of 0 or false indicates subtotal will not be displayed at the top of the group.

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

sumSubtotal (Sum Subtotal)

Specifies a boolean value that indicates whether apply the 'sum' aggregation function in the subtotal of this field.

A value of 1 or true indicates the 'sum' aggregation function is applied in the subtotal of this field.

A value of 0 or false indicates another aggregation function is applied in the subtotal of this field.

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

topAutoShow (Top Auto Show)

Specifies a boolean value that indicates whether an AutoShow filter applied to this field is set to show the top ranked values.

A value of 1 or true indicates whether an AutoShow filter will show top values for this field.

A value of 0 or false indicates bottom ranked values are shown.

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

uniqueMemberProperty (Unique Member Property)

Specifies the unique name of the member property to be used as a caption for the field and field items.

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

varPSubtotal (VarP Subtotal)

Specifies a boolean value that indicates whether to apply the 'varP' aggregation function in the subtotal of this field.

A value of 1 or true indicates the 'varP' aggregation function is applied in the subtotal of this field.

A value of 0 or false indicates another aggregation function is applied in the subtotal of this field.

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

varSubtotal (Variance Subtotal)

Specifies a boolean value that indicates whether to apply the 'variance' aggregation function in the subtotal of this field.

A value of 1 or true indicates the 'variance' aggregation function is applied in the subtotal of this field.

A value of 0 or false indicates another aggregation function is applied in the subtotal of this field.

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_PivotField) is located in §A.2. end note]

© ISO/IEC29500: 2008.

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

Community Additions

ADD
Show:
© 2014 Microsoft