Contains the properties and description of a PivotTable report.
Contained In
Subelements
For a PivotList component: CacheDetails, CommandText, ConnectionString, CubeField, DataAxisEmpty, DataMember, DisplayFieldList, FieldListBottom, FieldListLeft, FieldListRight, FieldListTop, Height, Location, MaxHeight, MaxWidth, MemberExpand, Name, NoAllowDetails, NoAllowFiltering, NoAllowGrouping, NoAllowPropertyToolbox, NoAutoFit, NoColumnGrand, NoDisplayAlerts, NoDisplayExpandIndicator, NoDisplayToolbar, NoRowGrand, OWCVersion, PivotAxis, PivotData, PivotField, PivotView, PLExport, PLPivotField, PLTotal, PTFormat, PTSource, Width
For a workbook: AutoFormatAlignment, AutoFormatBorder, AutoFormatFont, AutoFormatName, AutoFormatNumber, AutoFormatPattern, CubeField, DataMember, DisableDrillDown, DisableFieldDialog, DisableWizard, DisplayErrorString, ErrorString, GrandTotalString, HasNoAutoFormat, HideTotalsAnnotation, Location, MergeLabels, Name, NoAutoFormatWidth, NoColumnGrand, NoDisplayNullString, NoPreserveFormatting, NoPrintRepeatItems, NoRowGrand, NullString, PageFieldOrder, PageFieldStyle, PageFieldWrapCount, PivotField, PLExport, PrintSetTitles, PTFormat, PTFormula, PTLineItems, PTSource, Selection, SmallGrid, SubtotalHiddenPageItems, TableStyle, Tag, VacatedStyle, VersionLastUpdate, VersionUpdateableMin
Remarks
For a PivotList component, the Name subelement is required. Zero or more CubeField, PivotAxis, PivotField, PLPivotField, PLTotal, PTFormat subelements can be specified. Either the DataMember or CommandText subelement can be specified, but not both. If the NoAutoFit subelement is specified, the Height and Width subelements are required, but if the NoAutoFit subelement is not specified, the MaxHeight and MaxWidth subelements are required. All other subelements are optional.
For a workbook, the Location, Name, PTSource, and one or more PivotField subelements are required. Up to two PTLineItems subelements (one for row-oriented items in a PivotTable report and the other for column-oriented items) can be specified. The CubeField subelement is required if the PivotTable report is based on an OLAP data source. The order in which the PivotField subelements for base fields are specified must match the order in which its data appears in the PivotTable cache. All other subelements are optional. There can be one or more CubeField, PTFormat, and PTFormula subelements.
This example defines a PivotTable report in range A1:E297 based on data queried from the authors table in a Microsoft SQL Server database. A parameter query is used to obtain records of contact information from the pubs2.dbo.authors table for the state of California.
The first part of the example specifies that cells in the PivotTable report containing an error display the string "incorrect", and cells containing null values display the string "N/A". The page fields are displayed over and down, one field per column. The report is formatted using the built-in Report4 style, and the number, border, font, pattern, and alignment are changed. The formatting is not preserved when the report is refreshed; however, refreshes are disabled. No column or row grand totals are displayed. Row print titles are set to the rows that contain the report’s column field items, and column print titles are set to the columns that contain its row items.
<x:PivotTable>
<x:Name>PivotTable4</x:Name>
<x:ErrorString>incorrect</x:ErrorString>
<x:NullString>N/A</x:NullString>
<x:PageFieldOrder>OverThenDown</x:PageFieldOrder>
<x:PageFieldWrapCount>1</x:PageFieldWrapCount>
<x:AutoFormatName>Report4</x:AutoFormatName>
<x:AutoFormatNumber/>
<x:AutoFormatBorder/>
<x:AutoFormatFont/>
<x:AutoFormatPattern/>
<x:AutoFormatAlignment/>
<x:NoPreserveFormatting/>
<x:DisplayErrorString/>
<x:SubtotalHiddenPageItems/>
<x:NoRowGrand/>
<x:NoColumnGrand/>
<x:PrintSetTitles/>
<x:MergeLabels/>
<x:HideTotalsAnnotation/>
<x:Location>$A$1:$E$297</x:Location>
<x:PivotField>
<x:Name>au_id</x:Name>
<x:SQLType>VariableCharacter</x:SQLType>
<x:LayoutForm>Outline</x:LayoutForm>
</x:PivotField>
<x:PivotField>
<x:Name>au_lname</x:Name>
<x:Orientation>Row</x:Orientation>
<x:SQLType>VariableCharacter</x:SQLType>
<x:AutoShowType>Auto</x:AutoShowType>
<x:AutoShowField>Count of au_id</x:AutoShowField>
<x:AutoSortOrder>Descending</x:AutoSortOrder>
<x:LayoutForm>Outline</x:LayoutForm>
<x:ShowAllItems/>
<x:BlankLineAfterItems/>
<x:Position>2</x:Position>
<x:ParentField>au_lname2</x:ParentField>
<x:PivotItem>
<x:Name>Yokomoto</x:Name>
</x:PivotItem>
This section of the example defines rules for specific items. In the author last name field, data for authors Steinbeck and Smith are not in the PivotTable cache. One of the items contains a formula that returns the string "del Castillo". In the author first name field, data for the authors with the first name Abraham, Akiko, and Albert is hidden. Data for Albert is also missing from the PivotTable cache.
<x:PivotItem>
<x:Name>Steinbeck</x:Name>
<x:Missing/>
</x:PivotItem>
<x:PivotItem>
<x:Name>Smith</x:Name>
<x:Missing/>
</x:PivotItem>
.
.
.
<x:PivotItem>
<x:Name>Formula2</x:Name>
<x:Formula>='del Castillo'</x:Formula>
<x:FormulaIndex>1</x:FormulaIndex>
</x:PivotItem>
.
.
.
</x:PivotField>
<x:PivotField>
<x:Name>au_fname</x:Name>
<x:Orientation>Row</x:Orientation>
<x:SQLType>VariableCharacter</x:SQLType>
<x:AutoShowField>Count of au_id</x:AutoShowField>
<x:AutoSortOrder>Ascending</x:AutoSortOrder>
<x:LayoutForm>Outline</x:LayoutForm>
<x:Position>3</x:Position>
<x:PivotItem>
<x:Name>Abraham</x:Name>
<x:Hidden/>
</x:PivotItem>
<x:PivotItem>
<x:Name>Akiko</x:Name>
<x:Hidden/>
</x:PivotItem>
<x:PivotItem>
<x:Name>Albert</x:Name>
<x:Hidden/>
<x:Missing/>
</x:PivotItem>
The following section of the example specifies that the address, city, contract, phone number, state, and zip code fields are in outline form. The state field uses server-based filtering and the current page is California. The field named Field2 contains a formula referencing the zip code field. Field2 cannot be dragged to the row, column, and page positions. The field named au_lname2 is grouped according to the data in the au_lname field. All items in the field are visible, and a blank line is displayed after the items. The child field items are defined.
</x:PivotField>
<x:PivotField>
<x:Name>phone</x:Name>
<x:SQLType>Character</x:SQLType>
<x:LayoutForm>Outline</x:LayoutForm>
</x:PivotField>
<x:PivotField>
<x:Name>address</x:Name>
<x:SQLType>VariableCharacter</x:SQLType>
<x:LayoutForm>Outline</x:LayoutForm>
</x:PivotField>
<x:PivotField>
<x:Name>city</x:Name>
<x:SQLType>VariableCharacter</x:SQLType>
<x:LayoutForm>Outline</x:LayoutForm>
</x:PivotField>
<x:PivotField>
<x:Name>state</x:Name>
<x:Orientation>Page</x:Orientation>
<x:SQLType>Character</x:SQLType>
<x:AutoShowType>Auto</x:AutoShowType>
<x:AutoShowRange>Bottom</x:AutoShowRange>
<x:AutoShowCount>44</x:AutoShowCount>
<x:AutoShowField>Count of au_id</x:AutoShowField>
<x:AutoSortOrder>Ascending</x:AutoSortOrder>
<x:LayoutForm>Outline</x:LayoutForm>
<x:ServerBased/>
<x:CurrentPage>CA</x:CurrentPage>
<x:Position>1</x:Position>
<x:PivotItem>
<x:Name>CA</x:Name>
</x:PivotItem>
<x:PivotItem>
<x:Name>IN</x:Name>
</x:PivotItem>
.
.
.
</x:PivotField>
<x:PivotField>
<x:Name>zip</x:Name>
<x:SQLType>Character</x:SQLType>
<x:LayoutForm>Outline</x:LayoutForm>
</x:PivotField>
<x:PivotField>
<x:Name>contract</x:Name>
<x:SQLType>Bit</x:SQLType>
<x:LayoutForm>Outline</x:LayoutForm>
<x:DataType>Integer</x:DataType>
</x:PivotField>
<x:PivotField>
<x:Name>Field2</x:Name>
<x:Subtotal>None</x:Subtotal>
<x:LayoutForm>Outline</x:LayoutForm>
<x:NoDragToRow/>
<x:NoDragToColumn/>
<x:NoDragToPage/>
<x:Formula>=zip</x:Formula>
</x:PivotField>
<x:PivotField>
<x:Name>au_lname2</x:Name>
<x:Orientation>Row</x:Orientation>
<x:Subtotal>None</x:Subtotal>
<x:LayoutForm>Outline</x:LayoutForm>
<x:ShowAllItems/>
<x:BlankLineAfterItems/>
<x:Position>1</x:Position>
<x:BaseField>au_lname</x:BaseField>
<x:MapChildItems>
<x:Item>Yokomoto</x:Item>
<x:Item>White</x:Item>
.
.
.
</x:MapChildItems>
<x:PivotItem>
<x:Name>Yokomoto</x:Name>
</x:PivotItem>
<x:PivotItem>
<x:Name>White</x:Name>
<x:Hidden/>
</x:PivotItem>
In the last section of the example, the column field named Data is a data field. In all, there are 2 instances of line item 0 and 3 instances of line item 1. Both items are in the row position. In the column position, line item 1 contains an index to aggregate field 2. The PivotTable cache memory usage is optimized. In the data area of the report, the minimum of au_id and the count of Field2 are displayed, and only data can be selected.
</x:PivotField>
<x:PivotField>
<x:DataField/>
<x:Name>Data</x:Name>
<x:Orientation>Column</x:Orientation>
</x:PivotField>
<x:PivotField>
<x:Name>Min of au_id</x:Name>
<x:ParentField>au_id</x:ParentField>
<x:Orientation>Data</x:Orientation>
<x:Function>Min</x:Function>
<x:Position>1</x:Position>
</x:PivotField>
<x:PivotField>
<x:Name>Count of Field2</x:Name>
<x:ParentField>Field2</x:ParentField>
<x:Orientation>Data</x:Orientation>
<x:Function>Count</x:Function>
<x:Position>2</x:Position>
</x:PivotField>
<x:PTLineItems>
<x:PTLineItem>
<x:ItemType>Default</x:ItemType>
<x:CountOfSameItems>1</x:CountOfSameItems>
<x:Item>0</x:Item>
</x:PTLineItem>
<x:PTLineItem>
<x:ItemType>Blank</x:ItemType>
<x:CountOfSameItems>2</x:CountOfSameItems>
<x:Item>1</x:Item>
</x:PTLineItem>
.
.
.
</x:PTLineItems>
<x:PTLineItems>
<x:Orientation>Column</x:Orientation>
<x:PTLineItem>
<x:Item>0</x:Item>
</x:PTLineItem>
<x:PTLineItem>
<x:DataField>2</x:DataField>
<x:Item>1</x:Item>
</x:PTLineItem>
</x:PTLineItems>
<x:PTSource>
<x:CacheIndex>1</x:CacheIndex>
<x:RefreshName>T456TST</x:RefreshName>
<x:CacheFile HRef="./ptauth.files/cachedata.xml"/>
<x:RefreshDate>1999-04-15T15:14:36</x:RefreshDate>
<x:OptimizeCache/>
<x:DisableRefresh/>
<x:QuerySource>
<x:Connection>DRIVER=SQL Server;SERVER=xsrvone;UID=xtstacc;;APP=MicrosoftR Query;WSID=T456TST</x:Connection>
<x:CommandText>SELECT authors.au_id, authors.au_lname, authors.au_fname, </x:CommandText>
<x:CommandText>authors.phone, authors.address, authors.city, authors.state, </x:CommandText>
<x:CommandText>authors.zip, authors.contract </x:CommandText>
<x:CommandText>FROM pubs2.dbo.authors authors </x:CommandText>
<x:CommandText>WHERE (authors.state=?)</x:CommandText>
<x:CommandTextOriginal>SELECT authors.au_id, authors.au_lname, authors.au_fname, </x:CommandTextOriginal>
<x:CommandTextOriginal>authors.phone, authors.address, authors.city, authors.state, </x:CommandTextOriginal>
<x:CommandTextOriginal>authors.zip, authors.contract </x:CommandTextOriginal>
<x:CommandTextOriginal>FROM pubs2.dbo.authors authors</x:CommandTextOriginal>
<x:Parameter>
<x:Name>Parameter1</x:Name>
<x:SQLType>Character</x:SQLType>
<x:ParameterType>Value</x:ParameterType>
<x:ParameterValue>CA</x:ParameterValue>
</x:Parameter>
</x:QuerySource>
</x:PTSource>
<x:Selection>
<x:PTRule>
<x:RuleType>DataOnly</x:RuleType>
</x:PTRule>
</x:Selection>
</x:PivotTable>