## Pivot Data Calculation

EPPlus from version 7.2 can calculate pivot tables to extract data from it and also to use the `GetPivotData` function. The calculation can use the stored pivot cache records or create the cache from source data within the workbook where the pivot table is created. To calculate and fetch data from a pivot table, EPPlus provides the following methods and properties:

### Functions & properties

| Method/Property | Description |
| --- | --- |
| ExcelPivotTable.Calculate(refresh) | Calculates the pivot table.<br>Parameter 0 - if true the cache will be refreshed before calculating.<br>If false the existing cache will be used. If no cache exists the cache will be created from the source range. |
| ExcelPivotTable.CalculatedData | Gets data from the pivot table for a data field.<br>Use the SelectField and GetValue methods to get the desired calculated values in a fluent way. |
| ExcelPivotTable.GetPivotData(criteriaList, dataField) | Gets data from the pivot table for a data field.<br>Parameter 0 is the name of the data field.<br>Parameter 1 is a list if criterias for the row/column fields. |
| ExcelPivotTable.IsCalculated | Boolean flag indicating if the pivot table is calculated. |

### Using the `ExcelPivotTable.CalculatedData` property

The `CalculatedData` property can be used to get the calculated data from a pivot table in a fluent way. The calculated data will call the ExcelPivotTable.Calculate method if the pivot table has not been calculated before. If you have updated the source data of the pivot table always refresh the pivot table cache and call the Calculate method before fetching any calculated data.

```csharp
var grandTotal = pivotTable.CalculatedData.GetValue(); //Gets the grant total from the pivot table for the first data field.
var capVerde = pt.CalculatedData.SelectField("Country", "Cape verde").GetValue(); //Gets the value for  row/column field "Country" for the first data field.
var hellenKuhlman2017Q3Tax = pt.CalculatedData.
        SelectField("Name", "Hellen Kuhlman").
        SelectField("Years", "2017").
        SelectField("Quarters", "Q3").
        GetValue("Tax");                        //Get the value for data field "Tax", for several row/column fields.
```

```vbnet
Dim grandTotal = pivotTable.CalculatedData.GetValue() 'Gets the grant total from the pivot table for the first data field.
Dim capVerde = pt.CalculatedData.SelectField("Country", "Cape verde").GetValue() 'Gets the value for  row/column field "Country" for the first data field.
Dim hellenKuhlman2017Q3Tax = pt.CalculatedData _
        .SelectField("Name", "Hellen Kuhlman") _
        .SelectField("Years", "2017") _
        .SelectField("Quarters", "Q3") _
        .GetValue("Tax") 'Get the value for data field "Tax", for several row/column fields.
```

### Using the `ExcelPivotTable.GetPivotData` method

The `GetPivotData` is another option to get data from the pivot table.  
It works very similar to the GETPIVOTDATA worksheet function. The `GetPivotData` method will call the ExcelPivotTable.Calculate method if the pivot table has not been calculated before. If you have updated the source data of the pivot table always refresh the pivot table cache and call the Calculate method before fetching any calculated data.

```csharp
     var dataWorksheet = package.Workbook.Worksheets["Data"];
     // Add a pivot table starting from cell A1. Data is located in the existing Data worksheet
     var pt = ws.PivotTables.Add(ws.Cells["A1"], dataWorksheet.Cells["A1:D17"], "PivotTable1");
     // Add one column field and one row field.
     var columnField = pt.ColumnFields.Add(pt.Fields["Continent"]);
     var rowField = pt.RowFields.Add(pt.Fields["Country"]);
     // Add a data field on "Sales"
     pt.DataFields.Add(pt.Fields["Sales"]);

     // Calculate the pivot table. 
     // The pivot table will be calculated when calculating the worksheet, if no previous calculation has been made (If the IsCalculated flag is false). 
     // The 'true' in the first parameter causes the pivot cache to be refreshed before calculating.
     // If the source data used by the pivot table contains formulas, those formulas must be calculated before calculating the pivot table. 
     pt.Calculate(true); //Only neccesary if you have changed the source data.

     //Now get the Sales for the cell North America/USA
      var usaSales = pt.GetPivotData(
         "Sales", //The data field we want to fetch
         new List<PivotDataCriteria> 
         { 
            new PivotDataCriteria(columnField, "North America"), 
            new PivotDataCriteria(rowField, "USA") 
         });

     //Now get the subtotal for Sales for North America. 
      var northAmericaSales = pt.GetPivotData(
         "Sales", //The data field we want to fetch
         new List<PivotDataCriteria> 
         { 
            new PivotDataCriteria(columnField, "North America")) 
         });

     //Now get the grand total for Sales for North America. 
      var northAmericaSales = pt.GetPivotData(
         "Sales", //The data field we want to fetch
         new List<PivotDataCriteria> 
         { 
         });

     ws.Calculate();
```

```vbnet
Dim dataWorksheet = package.Workbook.Worksheets("Data")
' Add a pivot table starting from cell A1. Data is located in the existing Data worksheet
Dim pt = ws.PivotTables.Add(ws.Cells("A1"), dataWorksheet.Cells("A1:D17"), "PivotTable1")
' Add one column field and one row field.
Dim columnField = pt.ColumnFields.Add(pt.Fields("Continent"))
Dim rowField = pt.RowFields.Add(pt.Fields("Country"))
' Add a data field on "Sales"
pt.DataFields.Add(pt.Fields("Sales"))

' Calculate the pivot table. 
' The pivot table will be calculated when calculating the worksheet, if no previous calculation has been made (If the IsCalculated flag is false). 
' The 'true' in the first parameter causes the pivot cache to be refreshed before calculating.
' If the source data used by the pivot table contains formulas, those formulas must be calculated before calculating the pivot table. 
pt.Calculate(True) 'Only neccesary if you have changed the source data.

'Now get the Sales for the cell North America/USA
Dim usaSales = pt.GetPivotData(
    "Sales", 'The data field we want to fetch
    New List(Of PivotDataCriteria) From {
        New PivotDataCriteria(columnField, "North America"),
        New PivotDataCriteria(rowField, "USA")
    })

'Now get the subtotal for Sales for North America. 
Dim northAmericaSales = pt.GetPivotData(
    "Sales", 'The data field we want to fetch
    New List(Of PivotDataCriteria) From {
        New PivotDataCriteria(columnField, "North America")
    })

'Now get the grand total for Sales.
Dim grandTotalSales = pt.GetPivotData(
    "Sales", 'The data field we want to fetch
    New List(Of PivotDataCriteria) From {
    })

ws.Calculate()
```

### Using the GETPIVOTDATA function

The GETPIVOTDATA worksheet function fetches data from a pivot table, by specifying the data field and the row and/or column values. If you modify the source data of the pivot table you must refresh and calculate the pivot table before calling calculate any cell referencing `GetPivotData` function.

```csharp
     var dataWorksheet = package.Workbook.Worksheets["Data"];
     // Add a pivot table starting from cell A1. Data is located in the existing Data worksheet
     var pt = ws.PivotTables.Add(ws.Cells["A1"], dataWorksheet.Cells["A1:D17"], "PivotTable1");
     // Add one column field and one row field.
     pt.ColumnFields.Add(pt.Fields["Continent"]);
     pt.RowFields.Add(pt.Fields["Country"]);
     // Add a data field on "Sales"
     pt.DataFields.Add(pt.Fields["Sales"]);

     // Calculate the pivot table. 
     // The pivot table will be calculated when calculating the worksheet, if no previous calculation has been made (If the IsCalculated flag is false). 
     // The 'true' in the first parameter causes the pivot cache to be refreshed before calculating.
     // If the source data used by the pivot table contains formulas, those formulas must be calculated before calculating the pivot table. 
     pt.Calculate(true); //Only necessary if you have changed the source data.

     //Add some formulas that fetch data from the pivot table.
     ws.Cells["G5"].Formula = "GETPIVOTDATA(\"Sales\",$A$1,\"Continent\",\"North America\",\"Country\",\"USA\")";    
     ws.Cells["G6"].Formula = "GETPIVOTDATA(\"Sales\",$A$1,\"Continent\",\"Europe\")";
     ws.Cells["G7"].Formula = "GETPIVOTDATA(\"Sales\",$A$1)";
     ws.Cells["G8"].Formula = "GETPIVOTDATA(\"Sales\",$A$1,\"Continent\",\"North America\",\"Country\",\"Sweden\")";

     //Now calculate the worksheet will get the values from the pivot table into cells G5:G8
     ws.Calculate();
```

```vbnet
Dim dataWorksheet = package.Workbook.Worksheets("Data")
' Add a pivot table starting from cell A1. Data is located in the existing Data worksheet
Dim pt = ws.PivotTables.Add(ws.Cells("A1"), dataWorksheet.Cells("A1:D17"), "PivotTable1")
' Add one column field and one row field.
pt.ColumnFields.Add(pt.Fields("Continent"))
pt.RowFields.Add(pt.Fields("Country"))
' Add a data field on "Sales"
pt.DataFields.Add(pt.Fields("Sales"))

' Calculate the pivot table. 
' The pivot table will be calculated when calculating the worksheet, if no previous calculation has been made (If the IsCalculated flag is false). 
' The 'true' in the first parameter causes the pivot cache to be refreshed before calculating.
' If the source data used by the pivot table contains formulas, those formulas must be calculated before calculating the pivot table. 
pt.Calculate(True) 'Only necessary if you have changed the source data.

'Add some formulas that fetch data from the pivot table.
ws.Cells("G5").Formula = "GETPIVOTDATA(""Sales"",$A$1,""Continent"",""North America"",""Country"",""USA"")"
ws.Cells("G6").Formula = "GETPIVOTDATA(""Sales"",$A$1,""Continent"",""Europe"")"
ws.Cells("G7").Formula = "GETPIVOTDATA(""Sales"",$A$1)"
ws.Cells("G8").Formula = "GETPIVOTDATA(""Sales"",$A$1,""Continent"",""North America"",""Country"",""Sweden"")"

'Now calculate the worksheet will get the values from the pivot table into cells G5:G8
ws.Calculate()
```

## Supported functionality

### Calculating Data Fields on rows and columns:

Functions:

- Sum
- Count
- Average
- Min
- Max
- Product
- Count Numbers
- StdDev
- StdDevP
- Var
- VarP

### Support for Show Value as

Show Value As:

- % of Grand Total
- % of Column Total
- % of Row Total
- % Of
- % of Parent Row Total
- % of Parent Column Total
- % of Parent Total
- Difference From
- % Difference From
- Running Total in
- % Running Total in
- Rank smallest to largest
- Rank largest to smallest
- Index

### Sorting

- Sorting Ascending or Descending per field.

### Page fields

- Filter on a single or multiple items.

### Filters

- Caption filters
- Numeric Filters
- Date &amp; Time Filters
- Value Filters
- Top/bottom filters
- Slicers

### Calculated Fields

- Calculated fields using formulas supported in EPPlus and valid in a pivot table formula field.

### Current limitations:

- EPPlus will not filter on Timeline filters.
- EPPlus will not handle external or OLAP data sources.
- EPPlus will not handle data model data sources.