JavaScript/jQuery PivotGrid - Sorting

In PivotGrid, sorting means ordering of column and row fields. By default, they are ordered from A to Z (strings), from smallest to largest (numbers) or from oldest to newest (dates). You can reverse this order if you need to.

Alternatively, you can sort fields by summary values. In the grid below, regions are sorted by the summary values of the "2015" column.

From further topics, you will learn to configure both these approaches, apply sorting in code and specify a custom sorting algorithm.

Topics from this section describe the properties that configure sorting for the end-user.

To sort fields by their own values, the user invokes the Field Chooser. There, each item of the "Row Fields" and "Column Fields" areas has an arrow icon on its right side. The user clicks (or taps) this icon to sort the values of the corresponding field.

Alternatively, this type of sorting can be performed using the Field Panel. Each item on it has the same clickable arrow icon that sorts the values of the corresponding field.

To allow the user to sort by field values, set the allowSorting property to true.

var pivotGridOptions = {
    // ...
    allowSorting: true // allows the end-user to sort all column and row fields
};

If you need to allow sort operations for an individual field only, set the same property inside the configuration object of this field.

var pivotGridOptions = {
    // ...
    dataSource: {
        // ...
        fields: [{
            dataField: "FirstName",
            allowSorting: true // allows sort operations by "FirstName" only
            // ...
        }, {
            // ...
        }]
    },
    allowSorting: false
};

To understand how fields are sorted by summary values, consider two dimensions: column header and row header. To sort fields of one dimension, the user right-clicks (or performs a long tap) on a field of another dimension. In the appeared context menu, he or she chooses the required item.

To allow the user to perform this type of sorting, set the allowSortingBySummary property to true.

var pivotGridOptions = {
    // ...
    allowSortingBySummary: true // allows the end-user to sort by summary for all column and row fields
};

If you need to allow sort-by-summary operations for an individual field only, set the same property inside the configuration object of this field.

var pivotGridOptions = {
    // ...
    dataSource: {
        // ...
        fields: [{
            dataField: "FirstName",
            allowSortingBySummary: true // allows the end-user to sort by summary for the "FirstName" field only
            // ...
        }, {
            // ...
        }]
    },
    allowSortingBySummary: false
};

In addition to sorting with the PivotGrid interface, you can apply sorting in code.

To sort row or column fields by their own values, change the sortOrder property of the required field. For this purpose, call the field(id, options) method. For example, the following code sorts the "Region" field in descending order.

var pivotGridDataSource = pivotGridInstance.getDataSource();
pivotGridDataSource.field("Region", {
    sortOrder: 'desc'
});
pivotGridDataSource.load();

If you need to sort fields by summary values, change the field's sortBySummaryField property. Set this property to the data field that provides summary values. For example, consider that a grid contains a "Region" field located in the row header, a "Date" field located in the column header, and an "Amount" field that provides summary values. The configuration may be as follows.

var pivotGridOptions = {
    // ...
    dataSource: {
        // ...
        fields: [{
            dataField: 'Region',
            area: 'row'
        }, {
            dataField: 'Date',
            area: 'column'
        }, {
            dataField: 'Amount',
            area: 'data',
            summaryType: 'count'
        }]
    }
};

In this case, to sort the "Region" field by summary values, call the field(id, options) as shown in the code below.

var pivotGridDataSource = pivotGridInstance.getDataSource();
pivotGridDataSource.field("Region", {
    sortBySummaryField: 'Amount'
});
pivotGridDataSource.load();

The code snippet above will sort the "Region" field by Grand Totals. To sort the same field by Totals of a specific column, specify the path to this column using the sortBySummaryPath property. For example, the following code will sort the "Region" field by Totals of the "2015" column.

var pivotGridDataSource = pivotGridInstance.getDataSource();
pivotGridDataSource.field("Region", {
    sortBySummaryField: 'Amount',
    sortBySummaryPath: [2015]
});
pivotGridDataSource.load();

To sort row or column fields by their own values, set the sortOrder property of the required field. For example, the following code sorts the "Region" field in descending order.

var pivotGridOptions = {
    // ...
    dataSource: {
        // ...
        fields: [{
            // ...
            dataField: 'Region',
            sortOrder: 'desc' // sorts "Region" in descending order
        }, {
            // ...
        }]
    }
};

If you need to sort fields by summary values, set the field's sortBySummaryField property to the data field that provides summary values. For example, consider that a grid contains a "Region" field located in the row header, a "Date" field located in the column header, and an "Amount" field that provides summary values. Then, to sort the "Region" field by summary values of the "Amount" field, use the following code.

var pivotGridOptions = {
    // ...
    dataSource: {
        // ...
        fields: [{
            dataField: 'Region',
            area: 'row',
            sortBySummaryField: 'Amount' // sorts "Region" by summary values of "Amount"
        }, {
            dataField: 'Date',
            area: 'column'
        }, {
            dataField: 'Amount',
            area: 'data',
            summaryType: 'count'
        }]
    }
};

The code snippet above will sort the "Region" field by Grand Totals. To sort the same field by Totals of a specific column, specify the path to this column using the sortBySummaryPath property. For example, the following code will sort the "Region" field by Totals of the "2015" column.

var pivotGridOptions = {
    // ...
    dataSource: {
        // ...
        fields: [{
            dataField: 'Region',
            area: 'row',
            sortBySummaryField: 'Amount' 
            sortBySummaryPath: [2015] // sorts "Region" by Totals of the "2015" column
        }, {
            dataField: 'Date',
            area: 'column'
        }, {
            dataField: 'Amount',
            area: 'data',
            summaryType: 'count'
        }]
    }
};

In case you want to make initially set sorting impossible for the end-user to change, assign false to the allowSorting and/or allowSortingBySummary properties.

var pivotGridOptions = {
    // ...
    allowSorting: false, // disables UI sorting by field values
    allowSortingBySummary: false // disables UI sorting by summary values
}