JavaScript/jQuery PivotGrid - Grouping

The PivotGrid UI component supports data grouping for header items and field grouping. These types of grouping are essentially different. The detailed description of each grouping type is presented below.

Pivot grid's way of displaying information requires data to be split into independent chunks. For dimensions, these chunks are called header items and the process of their generation is called data grouping. Each header item is a condition for calculating summary values. There are many different ways to generate header items from data. The function responsible for splitting data is called selector. The default selector returns the unique values of the dataType among all the dataField values. This article describes how to configure the selector function and implement your own.

The data selector for fields whose dataType is number or date is configured using the groupInterval property.

To configure a data selector for numeric data, assign a number to the groupInterval property. The number assigned is the maximum length of each group. The bigger the number, the less the quantity of the generated groups.

For instance, consider a field whose values belong to an interval from 0 to 35. If groupInterval is set to 10, the following groups will be generated: 0-9, 10-19, 20-29 and 30-35. If groupInterval is set to 20, groups will be the following: 0-19, 20-29 and 30-35.

To configure the data selector for dates, assign one of the following strings to the groupInterval property.

• "year" — groups by years
• "quarter" — groups by quarters
• "month" — groups by months
• "day" — groups by days
• "dayOfWeek" — groups by days of the week

A sample column area configuration is presented below.

var pivotGridOptions = {
    dataSource: {
        // ...
        fields: [
            // ...
            { area: "column", dataField: "OrderDate", dataType: "date", groupInterval: "year" },
            { area: "column", dataField: "OrderDate", dataType: "date", groupInterval: "quarter" },
            { area: "column", dataField: "OrderDate", dataType: "date", groupInterval: "month" },
            { area: "column", dataField: "OrderDate", dataType: "date", groupInterval: "day" }
        ]
    }
}

If you need to split data into groups in a custom way, implement a selector function. In this function, check the current field's value and return the group to which this value belongs. The default selector function acts in the following way.

1. The dataField value is acquired from the dataSource.
2. The acquired value is casted to the type specified in the dataType property.
3. The header item value is calculated according to existing items and the groupInterval value.

When implementing your own selector function, follow a procedure similar to the one described above. Keep in mind that the dataType and groupInterval properties are ignored when the selector function is defined. Also, the field's current value is not passed to the selector function explicitly, you should manually take it from the data source. This will allow you to build a selector for grouping data based on the values of several fields.

For instance, the alphabetic selector for strings can be implemented as follows.

function(data){
    return data.name.charAt(0).toUpperCase()
}

By default, values returned by the selector function are displayed in the UI as group names. If you need to change the text to be displayed in header groups, implement the customizeText function.

If your field set contains typical dimensions, such as Address, you can organize fields related to them into groups. The pivot grid's group of fields concept is similar to the hierarchy OLAP concept. A group will be displayed in the Field Chooser as a single field, but when you put a group into an area, then that action is similar to putting a set of fields in a corresponding order in that area.

To create a group, follow the steps below.

1. Create an empty field in the fields array. Declare a new group by assigning its name to the groupName property of this field. Specify the area where this group must be located using the area property.
2. Assign the group's name to the groupName property of each field you need to group.
3. Order the fields within the group by setting the groupIndex property of each field within a group. The order of fields is important because it affects the structure of the resulting hierarchy.

A sample implementation of the Address dimension is presented below.

var pivotGridOptions = {
    dataSource: {
        // ...
        fields: [{ 
            groupName: 'Address',
            area: 'column'
        }, {
            dataField: 'Region',
            groupName: 'Address',
            groupIndex: 0
        }, {
            dataField: 'Country',
            groupName: 'Address',
            groupIndex: 1
        }, { 
            dataField: 'City',
            groupName: 'Address',
            groupIndex: 2
        },
        // ...
        ]
    }
}

You can specify a number of secondary properties for a group. Define whether the group contains dimensional fields or measures using the isMeasure property. If the groupName value should not be displayed in the Field Chooser, configure the group's caption. If you need to filter data by group values, specify the filterValues property. Each item of the filterValues array should contain a filtering value for each field in the group.

You do not need to implement groups for date values as they are implemented by default. However, you may need to customize the groups. For example, consider the following code. Here, the fields of the "month" group interval are sorted in descending order using the sortOrder property. In addition, the fields of the "quarter" group interval are hidden using the visible property.

var pivotGridOptions = {
    dataSource: {
        // ...
        fields: [{
            dataField: 'OrderDate',
            dataType: 'date',
            area: 'column'
        }, {
            groupName: 'OrderDate', // "groupName" equals "dataField" of the group 
            groupInterval: 'month',
            sortOrder: 'desc'
        }, {
            groupName: 'OrderDate',
            groupInterval: 'quarter',
            visible: false
        },
        // ...
        ]
    }
}