JavaScript/jQuery PivotGrid - Sorting

NOTE
This article operates with concepts and terms that were introduced earlier. Before you start acquainting yourself with this topic, make sure you are familiar with PivotGrid basics described in the Visual Elements and Fields and Areas articles.

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.

DevExtreme PivotGrid Sorting

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

DevExtreme PivotGrid SortingSummary

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

Sorting in the UI

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

Sorting by Field Values

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.

DevExtreme PivotGrid FieldChooser SortingIcons

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.

DevExtreme PivotGrid FieldPanel

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

JavaScript
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.

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

Sorting by Summary Values

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.

DevExtreme PivotGrid FieldChooser SortingIcons

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

JavaScript
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.

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

Sorting in Code

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

Sorting by Field Values

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.

JavaScript
var pivotGridDataSource = pivotGridInstance.getDataSource();
pivotGridDataSource.field("Region", {
    sortOrder: 'desc'
});
pivotGridDataSource.load();
NOTE
After you have made changes in a PivotGridDataSource, call its load() method to refresh data in the bound PivotGrid.

Sorting by Summary Values

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.

JavaScript
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.

JavaScript
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.

JavaScript
var pivotGridDataSource = pivotGridInstance.getDataSource();
pivotGridDataSource.field("Region", {
    sortBySummaryField: 'Amount',
    sortBySummaryPath: [2015]
});
pivotGridDataSource.load();
NOTE
After you have made changes in a PivotGridDataSource, call its load() method to refresh data in the bound PivotGrid.

Setting Initial Sorting

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.

JavaScript
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.

JavaScript
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.

JavaScript
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.

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