



# Pivot tables
  
Module to work and interact with Pivot Tables from Microsoft Excel.  

*Read this in other languages: [English](Manual_PivotTableExcel.md), [Português](Manual_PivotTableExcel.pr.md), [Español](Manual_PivotTableExcel.es.md)*
  
![banner](imgs/Banner_PivotTableExcel.png)
## How to install this module
  
To install the module in Rocketbot Studio, it can be done in two ways:
1. Manual: __Download__ the .zip file and unzip it in the modules folder. The folder name must be the same as the module and inside it must have the following files and folders: \__init__.py, package.json, docs, example and libs. If you have the application open, refresh your browser to be able to use the new module.
2. Automatic: When entering Rocketbot Studio on the right margin you will find the **Addons** section, select **Install Mods**, search for the desired module and press install.  


## Description of the commands

### Create
  
Create a new pivot table
|Parameters|Description|example|
| --- | --- | --- |
|Data range |Enter the data range you want to use to create the pivot table|Sheet1!B2:C4|
|Destination Cell |Enter the cell where you want the pivot table to be created|Sheet2!C4|
|Pivot table name |Enter the pivot table name|Name: |

### Refresh
  
Refresh a pivot table
|Parameters|Description|example|
| --- | --- | --- |
|Sheet |Name of the sheet where the pivot table is located|Sheet1|
|Refresh all ||False|
|Pivote table name |Name of the pivot table to update|Name: |
|Skip external data update ||False|

### Add field
  
Add field to a pivot table
|Parameters|Description|example|
| --- | --- | --- |
|Sheet |Name of the sheet where the pivot table is located|Sheet1|
|Pivot table name |Pivot table name|Name: |
|Field to add ||Field: |
|Select option|Select option to add a field to the pivot table|Add Data|
|Select a function|||
|Field name ||Sales Sum|

### Add calculated field
  
Create a calculated field in a pivot table
|Parameters|Description|example|
| --- | --- | --- |
|Sheet |Name of the sheet where the pivot table is located|Sheet1|
|Pivot table name |Pivot table name|Name: |
|Calculated field name|Name of the new calculated field|Total sold|
|Formula||=Liters*Price|
|Add to values|||
|Aggregation function|||
|Values display name||Total sold|

### Remove field
  
Remove a field from a pivot table
|Parameters|Description|example|
| --- | --- | --- |
|Sheet ||Sheet1|
|Pivot table name ||Name: |
|Field to remove |Name of the field to add|Field: |

### Filter
  
Filter a pivot table
|Parameters|Description|example|
| --- | --- | --- |
|Sheet |Name of the sheet where the pivot table is located|Sheet1|
|Pivot table name |Pivot table name|Name: |
|Filter name |Name of the field to be filtered|Field |
|Clear All Filters|If selected, all filters of the pivot table will be cleared|True|
|Filter(s) name to check |Name of the filter value to be checked|Name: |
|Filter(s) name to uncheck |Name of the filter value to be unchecked|Name: |

### Filter Values
  
Filter a pivot table values
|Parameters|Description|example|
| --- | --- | --- |
|Sheet ||Sheet1|
|Pivot table name ||Name: |
|Base field name ||Base field: |
|Field to apply the filter to.||Field: |
|Clear All Filters|||
|Select filter |Type of filter to apply.|xlValueEquals|
|Filter(s) name to check ||Name: |

### List Fields
  
List all available table fields
|Parameters|Description|example|
| --- | --- | --- |
|Sheet |Sheet name where the pivot table is located|Sheet1|
|pivot table name |Pivot table name|Name: |
|Assign result to variable |Variable name where the result will be stored|Variable|

### Change pivot table data
  
Change the source data range of a pivot table
|Parameters|Description|example|
| --- | --- | --- |
|Sheet |Name of the sheet where the pivot table is located|Sheet1|
|Pivot table name |Pivot table name|Name: |
|New Range |Pivot table data range|Sheet1!A1:R200|

### List Filter Items 
  
Return all items from filter
|Parameters|Description|example|
| --- | --- | --- |
|Sheet |Sheet name|Sheet1|
|Pivot table name |Pivot table name|Name: |
|Filter name |Filter name|Field |
|Assign result to variable |Variable name to store the result|Variable|

### Insert timeline
  
Create a new timeline.
|Parameters|Description|example|
| --- | --- | --- |
|Sheet |Name of the sheet where the timeline will be inserted|Plan1|
|Pivote table name |Name of the pivot table that will be used to create the timeline|PivotTable1: |
|Pivot table field |Name of the pivot table field that will be used to create the timeline|Field |
|Position range |Range where the timeline will be inserted|A1:D20|

### Filter slider
  
Sets the timeline's filter.
|Parameters|Description|example|
| --- | --- | --- |
|Sheet |Name of the sheet where the slider is located|Sheet1|
|Slider name |Slider name|Name: |
|Start date|Start date of the filter|13/12/1999: |
|End date|End date of the filter|13/12/2000: |

### Filter status
  
Checks whether the element is marked in the field filter as visible or not visible. Return True or False respectively.
|Parameters|Description|example|
| --- | --- | --- |
|Sheet |Sheet name where the filter is located|Sheet1|
|Pivot table name |Pivot table name where the filter is located|Name: |
|Field name |Filter name to be consulted|Field |
|Filter element to check|Field filter value to be checked|Value: |
|Assign result to variable ||Variable|

### Change table format to tabular
  
Changes the field of the pivot table to tabular format.
|Parameters|Description|example|
| --- | --- | --- |
|Sheet |Name of the sheet where the pivot table is located.|Sheet1|
|Table name |Name of the pivot table.|Name: |
|Pivot fields|Pivot table fields.|['Number', 'Date', 'Hours']: |

### Delete subtotals
  
Erase the subtotals from the pivot table.
|Parameters|Description|example|
| --- | --- | --- |
|Sheet |Name of the sheet where the pivot table is located.|Sheet1|
|Table name |Name of the pivot table.|Name: |
|Pivot fields|Pivot table fields.|['Number', 'Date', 'Hours']: |

### Repeat label
  
Allows to the pivot table to repeat labels.
|Parameters|Description|example|
| --- | --- | --- |
|Sheet |Name of the sheet where the pivot table is located.|Sheet1|
|Table name |Name of the pivot table.|Name: |
|Pivot fields|Pivot table fields that will be repeated.|['Number', 'Date', 'Hours']: |

### Expand items
  
Expand all items in the field that is collapsed
|Parameters|Description|example|
| --- | --- | --- |
|Sheet |Excel Sheet name where the pivot table is located|Sheet1|
|Pivot table name |Pivot table name|MyTable|
|Field |Name of the field where the record to expand is located (Active Field)|Month|
|Item to expand|Name of the item to expand as it appears in the List items command|January|

### List pivot tables in sheet
  
Returns a list with all pivot tables in the sheet
|Parameters|Description|example|
| --- | --- | --- |
|Sheet |Sheet name where the pivot tables are located|Sheet1|
|Assign result to variable |Variable name where the result will be stored|Variable|
