Group and Collapse

SpreadJS allows you to use group and collapse operations in a pivot table which makes it easier to get a clear view and display only the required data. This is particularly helpful when the pivot table contains lots of data that can be summarized further. You can group the data by date, text, and numbers as explained in the below section.

Group Date

SpreadJS allows you to group and ungroup datetime type fields in pivot tables. You can group data by date based on different time intervals, such as seconds, minutes, hours, days, months, quarters, or years, and even set the starting and ending timelines. The dateGroups property can be used to achieve the same.

The following image shows the data grouped quarterly for 2020.

The following code sample shows how to group date data by quarters.

// Group by dates
 let groupInfo = {
     originFieldName: "date", dateGroups: [
         { by: GC.Pivot.DateGroupType.quarters },
         { by: GC.Pivot.DateGroupType.years },
         { by: GC.Pivot.DateGroupType.months }
     ]
 };
 pivotTable.group(groupInfo);
 pivotTable.add("Years (date)", "Years (date)", GC.Spread.Pivot.PivotTableFieldType.columnField);
 pivotTable.add("Quarters (date)", "Quarters (date)", GC.Spread.Pivot.PivotTableFieldType.columnField);
 pivotTable.add("Months (date)", "Months (date)", GC.Spread.Pivot.PivotTableFieldType.columnField);

Group Text

In a pivot table, you can also group the Row label or Column Label items by using the textGroup method. It uses the add method to add a new group to the pivot table. When a text group is added, a new field is also created.

The following image shows the new group created by row label items.

The following code sample shows how to group the text of row label items.

// Group by items
myPivotTable.views.apply("empty_pt");
myPivotTable.add("Region", "Region", GC.Spread.Pivot.PivotTableFieldType.rowField);
myPivotTable.add("Quantity", "Sum of quantity", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
let groupInfo = {
    originFieldName: "Product", textGroup: {
        fieldName: "NewProduct",
        groupItems: {
            Drinks: ['Coffee', 'Juice'],
            Chocolate: ['Dark', 'White', 'Milk'],
            Others: ['Breads', 'Cheese puffs', 'Cookie']
        }
    }
};
myPivotTable.group(groupInfo);
myPivotTable.add("NewProduct", "TestProduct", GC.Spread.Pivot.PivotTableFieldType.columnField);

Group Numbers

You can group the numerical values instead of listing them individually in a Row field while creating frequency distribution tables. This helps in analyzing numerical values by grouping them into ranges. You can use the numberGroup method to group numerical values. The following image shows the frequency distribution of quantity within the group.

The following code sample shows how to group numbers.

// group by numbers
myPivotTable.views.apply("empty_pt");
myPivotTable.add("Quantity", "Quantity", GC.Spread.Pivot.PivotTableFieldType.rowField);
myPivotTable.add("Region", "Region", GC.Spread.Pivot.PivotTableFieldType.colField);
myPivotTable.add("Product", "Count of quantity", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.count);
let groupInfo = { originFieldName: "Quantity", numberGroup: { by: 30 } };
myPivotTable.group(groupInfo);

Collapse Groups

You can expand or collapse specific groups or entire pivot fields in a pivot table by using the collapse option. The following image shows the East region, Bakery, and Chocolates category fields as collapsed.

The following code sample shows how to collapse and expand groups.

// collapse some groups
myPivotTable.collapse("Region", "East", true);
myPivotTable.collapse("Category", "Bakery", true);
myPivotTable.collapse("Category", "Chocolates", true);
// expand region group
myPivotTable.collapse("Region", "East", false);

Hide Collapse button

You can show or hide the expand and collapse buttons (+ or - sign buttons on the left of the pivot item heading) in a pivot table using the showDrill option.

The following code sample shows how to hide expand and collapse buttons in the pivot table.

myPivotTable.options.showDrill = false;