/
excelscript.pivotvaluefilter.yml
146 lines (139 loc) · 4.76 KB
/
excelscript.pivotvaluefilter.yml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
### YamlMime:TSType
name: ExcelScript.PivotValueFilter
uid: 'ExcelScript!ExcelScript.PivotValueFilter:interface'
package: ExcelScript!
fullName: ExcelScript.PivotValueFilter
summary: >-
Configurable template for a value filter to apply to a PivotField. The `condition` defines what criteria need to be
set in order for the filter to operate.
remarks: |-
#### Examples
```TypeScript
/**
* This script applies a PivotValueFilter to the first row hierarchy in the PivotTable.
*/
function main(workbook: ExcelScript.Workbook) {
// Get the PivotTable on the current worksheet.
let sheet = workbook.getActiveWorksheet();
let pivotTable = sheet.getPivotTables()[0];
// Get the first row hierarchy to use as the field which gets filtered.
let rowHierarchy = pivotTable.getRowHierarchies()[0];
// Get the first data hierarchy to use as the values for filtering the rows.
let dataHierarchy = pivotTable.getDataHierarchies()[0];
// Create a filter that excludes values greater than 500.
let filter: ExcelScript.PivotValueFilter = {
condition: ExcelScript.ValueFilterCondition.greaterThan,
comparator: 500,
value: dataHierarchy.getName()
};
// Apply the filter.
rowHierarchy.getPivotField(rowHierarchy.getName()).applyFilter({
valueFilter: filter
});
}
```
isPreview: false
isDeprecated: false
type: interface
properties:
- name: comparator
uid: 'ExcelScript!ExcelScript.PivotValueFilter#comparator:member'
package: ExcelScript!
fullName: comparator
summary: >-
The comparator is the static value to which other values are compared. The type of comparison is defined by the
condition. For example, if comparator is "50" and condition is "greaterThan", all item values that are not greater
than 50 will be removed by the filter.
remarks: ''
isPreview: false
isDeprecated: false
syntax:
content: 'comparator?: number;'
return:
type: number
- name: condition
uid: 'ExcelScript!ExcelScript.PivotValueFilter#condition:member'
package: ExcelScript!
fullName: condition
summary: 'Specifies the condition for the filter, which defines the necessary filtering criteria.'
remarks: ''
isPreview: false
isDeprecated: false
syntax:
content: 'condition: ValueFilterCondition;'
return:
type: '<xref uid="ExcelScript!ExcelScript.ValueFilterCondition:enum" />'
- name: exclusive
uid: 'ExcelScript!ExcelScript.PivotValueFilter#exclusive:member'
package: ExcelScript!
fullName: exclusive
summary: >-
If `true`<!-- -->, filter *excludes* items that meet criteria. The default is `false` (filter to include items
that meet criteria).
remarks: ''
isPreview: false
isDeprecated: false
syntax:
content: 'exclusive?: boolean;'
return:
type: boolean
- name: lowerBound
uid: 'ExcelScript!ExcelScript.PivotValueFilter#lowerBound:member'
package: ExcelScript!
fullName: lowerBound
summary: The lower-bound of the range for the `between` filter condition.
remarks: ''
isPreview: false
isDeprecated: false
syntax:
content: 'lowerBound?: number;'
return:
type: number
- name: selectionType
uid: 'ExcelScript!ExcelScript.PivotValueFilter#selectionType:member'
package: ExcelScript!
fullName: selectionType
summary: 'Specifies if the filter is for the top/bottom N items, top/bottom N percent, or top/bottom N sum.'
remarks: ''
isPreview: false
isDeprecated: false
syntax:
content: 'selectionType?: TopBottomSelectionType;'
return:
type: '<xref uid="ExcelScript!ExcelScript.TopBottomSelectionType:enum" />'
- name: threshold
uid: 'ExcelScript!ExcelScript.PivotValueFilter#threshold:member'
package: ExcelScript!
fullName: threshold
summary: 'The "N" threshold number of items, percent, or sum to be filtered for a top/bottom filter condition.'
remarks: ''
isPreview: false
isDeprecated: false
syntax:
content: 'threshold?: number;'
return:
type: number
- name: upperBound
uid: 'ExcelScript!ExcelScript.PivotValueFilter#upperBound:member'
package: ExcelScript!
fullName: upperBound
summary: The upper-bound of the range for the `between` filter condition.
remarks: ''
isPreview: false
isDeprecated: false
syntax:
content: 'upperBound?: number;'
return:
type: number
- name: value
uid: 'ExcelScript!ExcelScript.PivotValueFilter#value:member'
package: ExcelScript!
fullName: value
summary: Name of the chosen "value" in the field by which to filter.
remarks: ''
isPreview: false
isDeprecated: false
syntax:
content: 'value: string;'
return:
type: string