SoftArtisans ExcelWriter Guide

Show code in...

The PivotField Object (IPivotField)

New

* The PivotField object is not available in ExcelWriterSE, ExcelWriterLE, or ExcelWriterFree.

The IPivotField object represents a single pivot field in a PivotTable. A pivot field is a category of data that is derived from a field in the PivotTable's source data. To create an IPivotField, use the property IPivotTable.Fields, for example:

[VBScript]
'--- The Fields collection is populated automatically
'--- based on the range of data passed to the CreatePivotTable 
'--- method.
Set fldProduct = pvtTable.Fields("Product")

IPivotField Properties	IPivotField Methods
AutoShowCount AutoShowField AutoShowRange AutoShowType AutoSortField AutoSortType DataDisplayFormat DisplayName InOutlineForm IsCalculatedDataField Name NumberFormat SummarizeBy Type	AddSubtotal ClearSubtotals RemoveSubtotal

IPivotField Properties

AutoShowCount

Signature

[VBScript]
Property AutoShowCount As Long (read/write)

Description

AutoShowCount sets the number of field items to display in the PivotTable by default. The property can be set to any value in the range 1-255. AutoShowCount is set to 10 by default.

Either the top or bottom field items will be displayed, according to the value of AutoShowRange. AutoShowField specifies the data field by which top or bottom values are determined.

Example

[VBScript]
pvtTable.Fields("Month").AutoShowCount = 2

AutoShowField

Signature

[VBScript]
Property AutoShowField As IPivotField (read/write)

Description

If AutoShow is enabled (see AutoShowType), only the top or bottom items will be displayed for the specified field in the generated PivotTable. The number of items displayed is determined by the value of AutoShowCount.

AutoShowField specifies the data field by which "top" and "bottom" values are determined. For example, if a PivotTable contains the two data fields "Count of OrderId" (fldOrderId) and "Sum of OrderTotal" (fldOrderTotal), and fldCustomer.AutoShowField is set to fldOrderTotal, the top/bottom fldCustomer.AutoShowCount number of customers will be displayed, by sum of OrderTotal.

AutoShowField can be set to a data field created using either saxlPivotFieldData or AddDataField.

Example

[VBScript]
fldTotal.Type = saxlPivotFieldData
fldCategory.AutoShowCount = 2
pvtTable.Fields("Country").AutoShowType = saxlAutoShowAutomatic
pvtTable.Fields("Country").AutoShowField = fldTotal

AutoShowRange

Signature

[VBScript]
Property AutoShowRange As saxlAutoShowRangeType (read/write)

Description

If AutoShow is enabled (see AutoShowType), only the top or bottom items will be displayed for the specified field in the generated PivotTable. The number of items displayed is determined by the value of AutoShowCount. AutoShowRange specifies whether the top or bottom values will be shown. Set AutoShowRange to an SAXlAutoShowRangeType value by name or number:

SAXlAutoShowRangeType Values
`saxlAutoShowRangeTop`	`0`
`saxlAutoShowRangeBottom`	`1`

Example

[VBScript]
fldCategory.AutoShowRange = saxlAutoShowRangeBottom

AutoShowType

Signature

[VBScript]
Property AutoShowType As SAXlAutoShowType (read/write)

Description

AutoShowType enables or disables AutoShow. If AutoShow is enabled, only the top or bottom items will be displayed for the specified field in the generated PivotTable. Set AutoShowType to an SAXlAutoShowType value by name or number:

SAXlAutoShowType Values
`saxlAutoShowManual`	`0`	Turns AutoShow off.
`saxlAutoShowAutomatic`	`1`	Turns AutoShow on.

Example

[VBScript]
fldCategory.AutoShowType = saxlAutoShowAutomatic

AutoSortField

Signature

[VBScript]
Property AutoSortField As IPivotField (read/write)

Description

If AutoSorting is enabled, AutoSortField specifies the data field by which to sort the field items.

For example, if:

A PivotTable contains the two data fields "Count of OrderId" (fldOrderId) and "Sum of OrderTotal" (fldOrderTotal) (in addition to fields that are not data fields),
fldCustomer.AutoSortType is set to saxlAutoSortDescending, and
fldCustomer.AutoSortField is set to fldOrderTotal

Then, the items in the Customer field will be sorted by by sum of OrderTotal, in descending order.

AutoSortField can be set to a data field created using either saxlPivotFieldData or AddDataField.

Example

[VBScript]
fldCategory.AutoSortType = saxlAutoSortDescending
fldCategory.AutoSortField = fldTotal

AutoSortType

Signature

[VBScript]
Property AutoSortType As SAXlAutoSortType (read/write)

Description

AutoSortType enables or disables AutoSorting, and sets the AutoSort order. If AutoSortType is on, the items in the pivot field will be sorted by a specified data field (see AutoSortField) in ascending or descending order.

For example, if:

A PivotTable contains the two data fields "Count of OrderId" (fldOrderId) and "Sum of OrderTotal" (fldOrderTotal) (in addition to fields that are not data fields),
fldCustomer.AutoSortType is set to saxlAutoSortDescending, and
fldCustomer.AutoSortField is set to fldOrderTotal

Then, the items in the Customer field will be sorted by by sum of OrderTotal, in descending order.

SAXlAutoSortType Values
`saxlAutoSortManual`	`0`
`saxlAutoSortAscending`	`1`
`saxlAutoSortDescending`	`2`

Example

[VBScript]
fldCategory.AutoSortType = saxlAutoSortDescending
fldCategory.AutoSortField = fldTotal

DataDisplayFormat

Signature

[VBScript]
Public Property DataDisplayFormat([BaseField As IPivotField], 
		[FieldItem As Long]) As SAXlDataDisplayFormat (write-only)

Description

DataDisplayFormat specifies how to display the values contained in a data field. This property does not affect pivot fields that are not data fields.

A data field contains values to be summarized. By default, number values are summarized by the Sum function and text values are summarized by the Count function. (To change the summarization type set SummarizeBy.) If a PivotTable contains more than one data field, a single field drop-down named Data appears in the report for access to all of the data fields.

Pass DataDisplayFormat an IPivotField and a field item number (both parameters are optional in VBScript). Set DataDisplayFormat to an SAXlDataDisplayFormat value by name or number:

SAXlDataDisplayFormat Values
`saxlDataDisplayNormal`	`0`
`saxlDataDisplayDifferenceFrom`	`1`
`saxlDataDisplayPercentageOf`	`2`
`saxlDataDisplayPercentageDifferenceOf`	`3`
`saxlDataDisplayRunningTotalIn`	`4`
`saxlDataDisplayPercentageOfRow`	`5`
`saxlDataDisplayPercentageOfColumn`	`6`
`saxlDataDisplayPercentageOfTotal`	`7`
`saxlDataDisplayIndex`	`8`

Example

[VBScript]
fldSales.DataDisplayFormat(fldSales,6) = saxlDataDisplayPercentageOfTotal

DisplayName

Signature

[VBScript]
Property DisplayName As String (read/write)

Description

DisplayName sets a name to display on the specified pivot field's label. The default value of this property is is the value of PivotField.Name, unless the field is a data field. The default display name of a data field is "[summarization type] PivotField.Name." For example, if the data in the "Sales" field is summarized by Sum, the display name will be "Sum of Sales."

Example

[VBScript]
fldProduct.DisplayName = "Products"

InOutlineForm

Signature

[VBScript]
Property InOutlineForm As Boolean (read/write)

Description

InOutlineForm specifies whether to display a field in outline form or in tabular form.

Example

[VBScript]
fldProduct.InOutlineForm = true

IsCalculatedDataField

Signature

[VBScript]
Property IsCalculatedDataField As Boolean (read-only)

Description

IsCalculatedDataField returns true if the specified pivot field is a data field that was added through the AddDataField method.

Example

[VBScript]
If fldSales.IsCalculatedDataField = true Then
    fldSales.DisplayName = "Sales (Data Field)"
End If

Name

Signature

[VBScript]
Property Name As String (read-only)

Description

Name returns the programmatic name of a field, the name assigned when the field was created. For example, if you created a field as follows:


	Set fldMonth = pvtTable.Fields("Month")

"Month" is the string that Name will return.

NumberFormat

Signature

[VBScript]
Property NumberFormat As Variant (read/write)

Description

NumberFormat sets the display format of number and date fields. For a complete list of format codes, see Formatting Codes.

Example

[VBScript]
fldTotal.NumberFormat = 8

SummarizeBy

Signature

[VBScript]
Property SummarizeBy As SAXlSummarizationType (read/write)

Description

A data field contains values to be summarized. By default, number values are summarized by the Sum function and text values are summarized by the Count function. To change the summarization type set SummarizeBy to an SAXlSummarizationType by name or number:

SAXlSummarizationType Values
`saxlSummarizationSum`	`0`
`saxlSummarizationCount`	`1`
`saxlSummarizationAverage`	`2`
`saxlSummarizationMax`	`3`
`saxlSummarizationMin`	`4`
`saxlSummarizationProduct`	`5`
`saxlSummarizationCountNums`	`6`
`saxlSummarizationStdDev`	`7`
`saxlSummarizationStdDevp`	`8`
`saxlSummarizationVar`	`9`
`saxlSummarizationVarp`	`10`

Example

[VBScript]
'--- The Total field will be summarized by sum to show 
'--- the total dollar value. The OrderID field will be 
'--- counted, to show the number of orders.
fldTotal.SummarizeBy = saxlSummarizationSum
fldOrderID.SummarizeBy = saxlSummarizationCount

Type

Signature

[VBScript]
Property Type As SAXlPivotFieldType (read/write)

Description

SAXlPivotFieldType Values
`saxlPivotFieldUndefined`	`0`
`saxlPivotFieldRow`	`1`
`saxlPivotFieldColumn`	`2`
`saxlPivotFieldPage`	`4`
`saxlPivotFieldData`	`8`

IPivotField Methods

AddSubtotal

Signature

[VBScript]
Function AddSubtotal(newVal As SAXlSubtotalType)

Description

AddSubtotal inserts a subtotal of the specified field. You can add several subtotal types. Set the subtotal type to an SAXlSubtotalType value by name or number:

SAXlSubtotalType Values
`saxlSubtotalNone`	`0`
`saxlSubtotalDefault`	`1`
`saxlSubtotalSum`	`2`
`saxlSubtotalCountA`	`3`
`saxlSubtotalAverage`	`4`
`saxlSubtotalMax`	`5`
`saxlSubtotalMin`	`6`
`saxlSubtotalProduct`	`7`
`saxlSubtotalCount`	`8`
`saxlSubtotalStdDev`	`9`
`saxlSubtotalStdDevp`	`10`
`saxlSubtotalVar`	`11`
`saxlSubtotalVarp`	`12`

Example

[VBScript]
fldSales.AddSubtotal saxlSubtotalSum

ClearSubtotals

Signature

[VBScript]
Function ClearSubtotals()

Description

ClearSubtotals clears all subtotal rows/columns inserted through the AddSubtotal method.

Example

[VBScript]
fldSales.ClearSubtotals()

RemoveSubtotal

Signature

[VBScript]
Function RemoveSubtotal(newVal As SAXlSubtotalType)

Description

RemoveSubtotal removes a subtotal of the specified field. Specify which subtotal to remove by its SAXlSubtotalType:

SAXlSubtotalType
`saxlSubtotalNone`	`0`
`saxlSubtotalDefault`	`1`
`saxlSubtotalSum`	`2`
`saxlSubtotalCountA`	`3`
`saxlSubtotalAverage`	`4`
`saxlSubtotalMax`	`5`
`saxlSubtotalMin`	`6`
`saxlSubtotalProduct`	`7`
`saxlSubtotalCount`	`8`
`saxlSubtotalStdDev`	`9`
`saxlSubtotalStdDevp`	`10`
`saxlSubtotalVar`	`11`
`saxlSubtotalVarp`	`12`

Example

[VBScript]
fldSales.RemoveSubtotal saxlSubtotalSum

Copyright © 2005, SoftArtisans, Inc.