Data field ShowDataAs
EPPlus from version 5.7 supports setting the ShowDataAs (Show Value As in Excel) property on pivot table data fields. ShowDataAs sets different calculation options on the data field.
EPPlus supports the follwing options:
- Difference from
- Index
- Percent of value
- Percent difference from a value
- Percent of column total,
- Percent of row total
- Percent of total
- Running total
- Percent of parent row
- Percent of parent column
- Percent of parent
- Rank ascending
- Rank descending
- Percent of running total
To set the ShowDataAs.Value property, use the different methods prefixed Set available on 'ShowDataAs' property:
//This sets 'Show Value As' for the data field to Percent of column total
var df = pivotTable5.DataFields.Add(pivotTable5.Fields["OrderValue"]);
df.Name = "Order value % of total";
df.ShowDataAs.SetPercentOfColumn();
df.Format = "0.0%;";Some ShowValueAs types requires you to relate to an individual item or the previous or the next item:
var rowField1 = pivotTable5.RowFields.Add(pivotTable5.Fields["CompanyName"]);
var df3 = pivotTable5.DataFields.Add(pivotTable5.Fields["OrderValue"]);
df3.Name = "Count Difference From Previous";
df3.ShowDataAs.SetDifference(rowField1, ePrevNextPivotItem.Previous);
df3.Function = DataFieldFunctions.Count;
df3.Format = "#,##0";