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 options 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 option to true.

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

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

JavaScript
var pivotGridOptions = {
    // ...
    dataSource: {
        // ...
        fields: [{
            dataField: "FirstName",
            allowSorting: true // allows sorting by the "FirstName" only
            // ...
        }, {
            // ...
        }]
    },
    allowSorting: false
};
Show Example:
AngularJS
Knockout
jQuery

In this example, you can sort row and column fields using the Field Chooser. Note that the configuration object of the "City" field has the allowSorting option set to false. Therefore, this particular field cannot be used for sorting.


                                    

                                    

In this example, you can sort row and column fields using the Field Chooser. Note that the configuration object of the "City" field has the allowSorting option set to false. Therefore, this particular field cannot be used for sorting.


                                    

                                    

In this example, you can sort row and column fields using the Field Chooser. Note that the configuration object of the "City" field has the allowSorting option set to false. Therefore, this particular field cannot be used for sorting.


                                    

                                    

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 option 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 sorting by summary for an individual field only, set the same option 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
};
Show Example:
AngularJS
Knockout
jQuery

In this example, you can sort row and column fields by summary values. Right-click any row or column field to invoke the context menu. Then, choose a required item from it.


                                    

                                    

In this example, you can sort row and column fields by summary values. Right-click any row or column field to invoke the context menu. Then, choose a required item from it.


                                    

                                    

In this example, you can sort row and column fields by summary values. Right-click any row or column field to invoke the context menu. Then, choose a required item from it.


                                    

                                    

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 option 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 option. Set this option 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 option. 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 option 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 option 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 option. 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 options.

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

In this example, items of the "Region" field, which is located in the row area, are sorted by the summary values of the "2015" column. This sorting setting cannot be changed by the end-user, because the allowSorting and allowSortingBySummary options are set to false.


                                    

                                    

In this example, items of the "Region" field, which is located in the row area, are sorted by the summary values of the "2015" column. This sorting setting cannot be changed by the end-user, because the allowSorting and allowSortingBySummary options are set to false.


                                    

                                    

In this example, items of the "Region" field, which is located in the row area, are sorted by the summary values of the "2015" column. This sorting setting cannot be changed by the end-user, because the allowSorting and allowSortingBySummary options are set to false.