Configure Pivot Table Fields

This topic explains how to configure the fields in your pivot table to suit your data analysis needs. Learn how to manage field levels, rename fields, expand or collapse field groups, sort fields, and control the visibility of the field list and headers.

Configure Pivot Table Fields

You can configure the fields of your pivot table using the properties and methods of the IPivotCaches interface and IPivotTables interface.

Refer to the following example code to configure the pivot table fields in a worksheet.

//Source data for PivotCache
object[,] sourceData = new object[,] {
{ "Order ID", "Product",  "Category",   "Amount", "Date",                    "Country" },
{ 1,          "Carrots",  "Vegetables",  4270,    new DateTime(2012, 1, 6),  "United States" },
{ 2,          "Broccoli", "Vegetables",  8239,    new DateTime(2012, 1, 7),  "United Kingdom" },
{ 3,          "Banana",   "Fruit",       617,     new DateTime(2012, 1, 8),  "United States" },
{ 4,          "Banana",   "Fruit",       8384,    new DateTime(2012, 1, 10), "Canada" },
{ 5,          "Beans",    "Vegetables",  2626,    new DateTime(2012, 1, 10), "Germany" },
{ 6,          "Orange",   "Fruit",       3610,    new DateTime(2012, 1, 11), "United States" },
{ 7,          "Broccoli", "Vegetables",  9062,    new DateTime(2012, 1, 11), "Australia" },
{ 8,          "Banana",   "Fruit",       6906,    new DateTime(2012, 1, 16), "New Zealand" },
{ 9,          "Apple",    "Fruit",       2417,    new DateTime(2012, 1, 16), "France" },
{ 10,         "Apple",    "Fruit",       7431,    new DateTime(2012, 1, 16), "Canada" },
{ 11,         "Banana",   "Fruit",       8250,    new DateTime(2012, 1, 16), "Germany" },
{ 12,         "Broccoli", "Vegetables",  7012,    new DateTime(2012, 1, 18), "United States" },
{ 13,         "Carrots",  "Vegetables",  1903,    new DateTime(2012, 1, 20), "Germany" },
{ 14,         "Broccoli", "Vegetables",  2824,    new DateTime(2012, 1, 22), "Canada" },
{ 15,         "Apple",    "Fruit",       6946,    new DateTime(2012, 1, 24), "France" },
};

//Initialize the WorkBook and fetch the default WorkSheet
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.Worksheets[0];
// Assigning data to the range
worksheet.Range["A1:F16"].Value = sourceData;
// Creating pivot
var pivotcache = workbook.PivotCaches.Create(worksheet.Range["A1:F16"]);
var pivottable = worksheet.PivotTables.Add(pivotcache, worksheet.Range["L7"], "pivottable1");

// Configuring pivot table fields
var field_Category = pivottable.PivotFields["Category"];
field_Category.Orientation = PivotFieldOrientation.RowField;

var field_Product = pivottable.PivotFields["Product"];
field_Product.Orientation = PivotFieldOrientation.ColumnField;

var field_Amount = pivottable.PivotFields["Amount"];
field_Amount.Orientation = PivotFieldOrientation.DataField;

var field_Country = pivottable.PivotFields["Country"];
field_Country.Orientation = PivotFieldOrientation.PageField;

Manage Pivot Field Level

Refer to the following example code to manage the field level of a pivot table.

//product in level 1.
var field_product = pivottable.PivotFields["Product"];
field_product.Orientation = PivotFieldOrientation.RowField;

//category in level 2.
var field_category = pivottable.PivotFields["Category"];
field_category.Orientation = PivotFieldOrientation.RowField;

var field_Amount = pivottable.PivotFields[3];
field_Amount.Orientation = PivotFieldOrientation.DataField;

//category will in level 1 and product in level 2.
field_product.Position = 1;
field_category.Position = 0;

Rename Pivot Table Fields

Sometimes, the pivot table fields are not easily comprehendible and hence can be renamed to meaningful and easily understandable names.

Refer to the following example code to rename the pivot table fields.

//config pivot table's fields
var field_Date = pivottable.PivotFields["Date"];
field_Date.Orientation = PivotFieldOrientation.PageField;

// Renaming PivotField "Category" to "Type of Category"
var field_Category = pivottable.PivotFields["Category"];
field_Category.Name = "Type of Category";
field_Category.Orientation = PivotFieldOrientation.RowField;

var field_Product = pivottable.PivotFields["Product"];
field_Product.Orientation = PivotFieldOrientation.ColumnField;

var field_Amount = pivottable.PivotFields["Amount"];
field_Amount.Orientation = PivotFieldOrientation.DataField;
     

var field_Country = pivottable.PivotFields["Country"];
field_Country.Orientation = PivotFieldOrientation.RowField;

// Renaming DataField "Sum of Amount" to "Amount Total"
pivottable.DataFields[0].Name = "Amount Total";

Expand or Collapse Pivot Table Fields

DsExcel provides ShowDetail property in IPivotItem interface which allows you to expand or collapse the outline of pivot table fields. The default value of the property is True which shows the expanded state of pivot table fields. However, it can be set to False to display the collapsed state.

Refer to the following example code to set collapsed state of two pivot table fields.

worksheet.Range["A1:F16"].Value = sourceData;
IPivotCache pivotCache = workbook.PivotCaches.Create(worksheet.Range["A1:F16"]);
IPivotTable pivotTable = worksheet.PivotTables.Add(pivotCache, worksheet.Range["H7"], "pivottable1");
        
pivotTable.PivotFields["Product"].Orientation = PivotFieldOrientation.RowField;
pivotTable.PivotFields["Country"].Orientation = PivotFieldOrientation.RowField;
pivotTable.PivotFields["Category"].Orientation = PivotFieldOrientation.ColumnField;
pivotTable.PivotFields["Amount"].Orientation = PivotFieldOrientation.DataField;
        
//Set collapsed state
pivotTable.PivotFields["Product"].PivotItems["Banana"].ShowDetail = false;
pivotTable.PivotFields["Product"].PivotItems["Carrots"].ShowDetail = false;

workbook.Save("CollapsePivotFields.xlsx");

Sort Pivot Table Fields

DsExcel supports sorting data fields in a pivot table by using AutoSort method and defining ascending or descending as its sort order.

You can also retrieve the name of data field used to sort the specified PivotTable field by using AutoSortField property and its sorting order by using AutoSortOrder property. The position of an item in its field can also be set or retrieved by using the Position property of IPivotItem interface.

Refer to the following example code to sort 'Product' field in a pivot table.

//sort the product items 
field_Product.AutoSort(SortOrder.Descending);