Pivot Table Activities

Creates a pivot table with specific pivot fields or updates an existing pivot table in Excel.

This package is useful for:

• creating a pivot table with specific pivot fields and filters from the data source.
• updating data source range and refresh existing pivot tables.

Create Pivot Table

This package helps to create pivot table with specific pivot fields from the data source and you can also perform actions like 'add filters'.

Mandatory input fields:

• Workbook File Path - This activity works on a single file and it must contain your data source for the pivot in the same file.
• Input Sheet Name - The name of the sheet where source data is present.
• Input Data Range - Range of Input data.
• Destination Data Range - Start range in destination sheet where the pivot table needs to be created, e.g. 'A1'.
• Destination Sheet Name - Sheet name where the pivot table needs to be created. If the sheet is not available it will create a new sheet.

Optional Fields

• Row Pivot Fields - Pivot fields in Row section. For multiple fields use "," as a separator.
• Column Pivot Fields - Pivot fields in Column section. For multiple fields use "," as a separator.
• Data Pivot Fields - Pivot fields in Value section. For multiple fields use "," as a separator.
• Data Pivot Filter - Choose the type of calculation that you want to use to summarize data from the selected field under Value section. The options available are Sum, Count, Avg, Min, Product, CountNum, stDev, stDevP, Var, VarP.
• Filter Pivot Field - Pivot field in Filter section.

Notes
- At least one of the pivot field arguments must be provided.
- If the destination sheet name does not exists in Excel, it will be created.

- If destination data source range is not provided, it will consider the range as "A1".
- Before using the activity, the Excel file should be closed.
- The activity will load the file and process the activity in the background, it uses Excel Interops.
- Excel file name must be mentioned in absolute path.

Update Pivot Table

This activity will update the data source range if needed and refresh the existing pivot table in Excel.

Mandatory input fields:

• Workbook File Path - This activity works on a single file and it must contain your data source for the pivot in the same file.
• Pivot Sheet Name - The name of the sheet where the pivot is available.

Optional Fields:

• Pivot Table Source Range - Source range of the pivot table e.g. "A1:C4".
• Pivot Table Name - Pivot table name to be updated/refreshed.
• Pivot Data Source Range - Data source range. Format : R + Rowstartingnumber + C + Column StartingNumber : R + Rowendnumber + C + Column Endnumber. E.g. A1:C4 will be R1C1:R4C3.
• Pivot Data Source Sheet Name - Data source sheet name.

Notes
- Either Pivot Table Name or Pivot Table Source Range should be provided.
- If any change takes place in the data source range, then Pivot Data Source Range and Pivot Data Source Sheet Name should be provided.
- Before using the activity, the Excel file should be closed.
- The activity will load the file and process the activity in the background, it uses Excel Interops.
- Excel file name must be mentioned in absolute path.

For Excel users, this activity helps in creating a pivot table with specific pivot fields from the data source and you can also perform action like 'add filters'. 

It also helps in updating an existing pivot by updating the data source range if needed and refresh existing pivot table in Excel.

Since this activity runs in the background with a runtime of less than a minute, it eliminates the need for engaging in UI interaction or macro to create/update a pivot table with specific number and type of pivot fields.