Group Pivot Fields in the Pivot Table

Possible Usage Scenarios

  • Grouping pivot fields is useful when a row or column field of a pivot table contains many distinct items, such as a date field with hundreds of dates or a numeric field with many scattered values. Grouping collapses these items into meaningful sections: dates into months, quarters and years, and numbers into ranges such as 0-1, 2-3, 4-5. This makes a large pivot table compact and easier to analyze. Aspose.Cells for Node.js via Java provides this capability through the PivotField.group() method (automatic grouping by data type) and PivotField.group(PivotFieldGroupOptions) (custom grouping options).
  • Date grouping rolls the individual dates up into larger periods, such as days, months, quarters and years, and builds a time hierarchy (for example Years, Quarters, Months). Trends over time become visible at a glance, and each period level becomes its own pivot field in the row or column area of the pivot table.
  • Grouping is available only for fields placed in the ROW or COLUMN area of a pivot table. A data (value) field cannot be grouped directly. To group such a field you must first add it to the row or column area of the pivot table.

Group Pivot Fields Automatically

The parameterless PivotField.group() overload inspects the data type of the field and groups it automatically. A field that contains only dates (and blanks) is grouped by months. A field that contains only numbers (and blanks) is grouped into numeric ranges using an automatic interval.

const AsposeCells = require("aspose.cells");
let wb = new AsposeCells.Workbook("sampleGroupPivotFieldsInPivotTable.xlsx");
// Access the second worksheet and its pivot table
let pt = wb.getWorksheets().get(1).getPivotTables().get(0);
// Group the first row field automatically.
// The field contains dates, so it is grouped by months.
let field = pt.getRowFields().get(0);
field.group();
// Calculate the pivot table data and save the output file
pt.calculateData();
wb.save("outputAutoGroupPivotField.xlsx");

Group Pivot Fields with Custom Options

The PivotFieldGroupOptions class provides custom grouping configuration. For date fields, set StartDate, EndDate, and GroupTypes. For numeric fields, set StartNumber, EndNumber, and Interval. The NewPivotField property controls whether grouping creates new pivot fields alongside the original (see the Note section at the end of this article).

Group a Date Field by Months and Quarters

The following example groups the date row field of the sample pivot table by months and quarters between a start and end date. The code loads the sample Excel file, performs the grouping, calculates the pivot table data, and saves the grouped result. The screenshot shows the effect: the date row field is now grouped by quarters and months. Note that the order of the values in options.GroupTypes is ignored. No matter in which order the group types are listed, the grouped fields are always displayed from the largest period to the smallest: years, quarters, months, days, hours, minutes, seconds. In this example the months are listed before the quarters in options.GroupTypes, yet the quarters are displayed as the outer level and the months as the inner level.

const AsposeCells = require("aspose.cells");
// Load the sample workbook
const wb = new AsposeCells.Workbook("sampleGroupPivotFieldsInPivotTable.xlsx");
// Access the second worksheet and its pivot table
const pt = wb.getWorksheets().get(1).getPivotTables().get(0);
// Group the first row field (a date field) by months and quarters
const options = new AsposeCells.PivotFieldGroupOptions();
options.setStartDate(new Date(2008, 0, 1));
options.setEndDate(new Date(2008, 8, 5));
options.setGroupTypes([AsposeCells.PivotGroupByType.MONTHS, AsposeCells.PivotGroupByType.QUARTERS]);
pt.getRowFields().get(0).group(options);
// Calculate the pivot table data and save the output file
pt.calculateData();
wb.save("outputGroupPivotFieldsInPivotTable.xlsx");

Pivot table grouped by months and quarters

Group a Numeric Field into Ranges

FieldTwo of the sample pivot table is initially a data (value) field. Because only row or column fields can be grouped, the code first calls pivotTable.addFieldToArea(PivotFieldType.Row, "FieldTwo") to move FieldTwo to the row area, refreshes the pivot, then groups the numeric field into the ranges 0-1, 2-3 and 4-5 using StartNumber = 0, EndNumber = 5 and Interval = 2.

const AsposeCells = require("aspose.cells");
// Load the sample workbook
const wb = new AsposeCells.Workbook("sampleGroupPivotFieldsInPivotTable.xlsx");
// Access the second worksheet and its pivot table
const pt = wb.getWorksheets().get(1).getPivotTables().get(0);
// FieldTwo is a data field: move it to the row area first,
// because only row/column fields can be grouped
pt.addFieldToArea(AsposeCells.PivotFieldType.ROW, "FieldTwo");
pt.calculateData();
// Group the numeric field into the ranges 0-1, 2-3, 4-5
const options = new AsposeCells.PivotFieldGroupOptions();
options.setStartNumber(0);
options.setEndNumber(5);
options.setInterval(2);
pt.getRowFields().get(1).group(options);
// Calculate the pivot table data and save the output file
pt.calculateData();
wb.save("outputNumberGroupPivotField.xlsx");

Note