| 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
Top |
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
Top |
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
Top |
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
Top |
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
Top |
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
Top |
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
Top |
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"
Top |
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
Top |
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
Top |
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.
Top |
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
Top |
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
Top |
Type
Signature
[VBScript]
Property Type As SAXlPivotFieldType (read/write)
Description
| SAXlPivotFieldType Values |
|---|
saxlPivotFieldUndefined | 0 |
saxlPivotFieldRow | 1 |
saxlPivotFieldColumn | 2 |
saxlPivotFieldPage | 4 |
saxlPivotFieldData | 8 |
Top |
