SpreadJS 14
Features / Pivot Table / Group and Collapse
In This Topic
    Group and Collapse
    In This Topic

    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 which can be summarized further. You can group the data by date, text and numbers as explained in the below section.

    Group Date

    You can group data by date based on seconds, minutes, hours, days, months, quarters or years and even set the starting and ending timelines. The dateGroups method can be used to achieve the same.

    The following image shows the data grouped quarterly for 2013 and 2014 years.

    Grouping dates in the pivot table

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

    Copy Code
    // group by dates 
    let groupInfo = { originFieldName: "OrderDate", dateGroups: [{ by: GC.Pivot.DateGroupType.quarters }, { by: GC.Pivot.DateGroupType.years }] };
    myPivotTable.add("OrderDate", "Qtr", GC.Spread.Pivot.PivotTableFieldType.columnField);
    myPivotTable.add("Years", "Years", 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.

    Grouping text in the pivot table

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

    Copy Code
    // group by items
    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.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.

    Grouping numbers in the pivot table

    The following example code shows how to group numbers.

    Copy Code
    // group by numbers
    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 } };

    Collapse Groups

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

    Collapse groups in the pivot table

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

    Copy Code
    // 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 pivot item heading) in a pivot table using the showDrill option.Hide collapse button

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

    Copy Code
    myPivotTable.options.showDrill = false;