How to: Group Items in a Pivot Table
- 6 minutes to read
Grouping can help you to get a clearer view of data and show only data you want to analyze. Use the PivotField.GroupItems method overloads to group data in a PivotTable report.
Select the task you wish to perform.
Group a Pivot Table by Date
The following example demonstrates how to group items in the “Date” field by quarters and months. To do this, use the PivotField.GroupItems method with the combination of PivotFieldGroupByType.Quarters and PivotFieldGroupByType.Months values passed as a parameter.
Worksheet worksheet = workbook.Worksheets["Report8"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Access the "DATE" field by its name in the collection.
PivotField field = pivotTable.Fields["DATE"];
// Group field items by quarters and months.
field.GroupItems(PivotFieldGroupByType.Quarters | PivotFieldGroupByType.Months);
The image below shows the resulting report (the workbook is opened in Microsoft® Excel®).
Group a Pivot Table by Numbers
To group a numeric field in code, use the PivotField.GroupItems method with the PivotFieldGroupByType.NumericRanges parameter, specify the smallest and largest number to group the field and an interval for each group.
The following example groups the sales amounts by thousands.
Worksheet worksheet = workbook.Worksheets["Report12"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Access the "Usual Hours Worked" field by its name in the collection.
PivotField field = pivotTable.Fields["Sales"];
// Group field items from 1000 to 4000 by 1000.
field.GroupItems(1000, 4000, 1000, PivotFieldGroupByType.NumericRanges);
The image below shows the resulting report (the workbook is opened in Microsoft® Excel®).
Group Selected Items
The following example demonstrates how to create a group of the first three items in the “State” field (Arizona, California and Colorado). To do this, use the PivotField.GroupItems method with a list of item indices (0, 1, 2) passed as a parameter.
Worksheet worksheet = workbook.Worksheets["Report11"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Access the "State" field by its name in the collection.
PivotField field = pivotTable.Fields["State"];
// Add the "State" field to the column axis area.
pivotTable.ColumnFields.Add(field);
// Group the first three items in the field.
IEnumerable<int> items = new List<int>() { 0, 1, 2 };
field.GroupItems(items);
// Access the created grouped field by its index in the field collection.
int groupedFieldIndex = pivotTable.Fields.Count - 1;
PivotField groupedField = pivotTable.Fields[groupedFieldIndex];
// Set the grouped item caption to "West".
groupedField.Items[0].Caption = "West";
The image below shows the resulting report (the workbook is opened in Microsoft® Excel®).
Ungroup Data
To ungroup data in a pivot table, use the PivotField.UngroupItems method overloads.
The following example creates two groups of “State” items and subsequently ungroups the first group.
Worksheet worksheet = workbook.Worksheets["Report11"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Access the "State" field by its name in the collection.
PivotField field = pivotTable.Fields["State"];
// Add the "State" field to the column axis area.
pivotTable.ColumnFields.Add(field);
// Group the first three items in the field.
IEnumerable<int> items = new List<int>() { 0, 1, 2 };
field.GroupItems(items);
// Access the created grouped field by its index in the field collection.
int groupedFieldIndex = pivotTable.Fields.Count - 1;
PivotField groupedField = pivotTable.Fields[groupedFieldIndex];
// Set the grouped item caption to "West".
groupedField.Items[0].Caption = "West";
// Group the remaining field items.
items = new List<int>() { 3, 4, 5 };
field.GroupItems(items);
// Set the grouped item caption to "Midwest"
groupedField.Items[1].Caption = "Midwest";
// Ungroup the "West" item.
items = new List<int> { 0 };
groupedField.UngroupItems(items);
The image below shows the resulting report (the workbook is opened in Microsoft® Excel®).
