Pivot Activity
The Pivot activity performs pivot operations within an Excel file. It enables users to transform and summarize data from a source sheet into a new, structured pivot table.
| Field | Description | Requirement |
|---|---|---|
| Excel name | The reference name of the Excel file that has already been opened or created. | Required |
| Source sheet | The name of the Excel sheet containing the data for the pivot operation. | Optional |
| Range | Specifies the data range to be pivoted within the source sheet. | Optional |
| Sheet name | The name for the new Excel sheet where the pivot table results will be displayed. | Optional |
| Destination | Determines the starting cell in the destination sheet where the pivoted data will be written. | Optional |
| Columns | Defines the columns to be used in the pivot operation. | Optional |
| Rows | Defines the rows to be used in the pivot operation. | Optional |
| Values | Specifies the desired aggregation actions to be performed on the data. | Optional |
| Filters | Defines the columns to be used as report filters in the pivot table, restricting which data is included in the aggregation. | Optional |
Action Types & Examples
AVERAGE
- Format: Number
- Example Result:
15.75
COUNT
- Format: Integer
- Example Result:
100
COUNT_NUMS
- Format: Integer
- Example Result:
95
MAX
- Format: Number
- Example Result:
250.00
MIN
- Format: Number
- Example Result:
1.50
PRODUCT
- Format: Number
- Example Result:
12000
STD_DEV
- Format: Number
- Example Result:
5.2
STD_DEVP
- Format: Number
- Example Result:
4.8
SUM
- Format: Number
- Example Result:
1500
VAR
- Format: Number
- Example Result:
27.04
VARP
- Format: Number
- Example Result:
23.04
Implementation Examples
Field Setup - Excel name: ${pivotExcel} - Source sheet: Sheet1 - Range: A1:B3, A:A, A:H - Sheet name: Pivot - Destination: A1 - Columns: A, A,B - Rows: B, A,B - Values: SUM(C), COUNT(C),AVERAGE(C)
Execution Parameters - Filters: D, D,E
Technical Notes
If the first page of the Excel file is to be pivoted, the Source sheet field does not need to be filled. Otherwise, it must be provided. If no Destination is specified, the pivoted data is copied to cell
A1. If Range is not provided, the entire range in the Excel file is used for the pivot operation.Filters adds one or more columns as pivot table report filters, alongside Rows and Columns, letting the resulting pivot table be narrowed down by the selected filter values. Note that unlike the standard Excel component, MS Excel does not expose a Sheet index field — the destination sheet is identified only by name.