JavaScript (ES5)

Search results

Sorting in JavaScript (ES5) Pivot Table control

24 May 2022 / 9 minutes to read

Member Sorting

Allows to order field members in rows and columns either in ascending or descending order. By default, field members in rows and columns are in ascending order.

Member sorting can be enabled by setting the enableSorting property in dataSourceSettings to true. After enabling this API, click the sort icon besides each field in row or column axis, available in field list or grouping bar UI for re-arranging members either in ascending or descending order.

By default the enableSorting property in dataSourceSettings set as true. If we set it as false, then the field members arrange in pivot table as its data source order. And, the sort icons in grouping bar and field list buttons will be removed.

output
output
output

Member sorting can also be configured using the sortSettings through code behind, during initial rendering. The settings required to sort are:

name: It allows to set the field name.
order: It allows to set the sort direction either to ascending or descending of the respective field.

By default the order property in the sortSettings set as Ascending. Meanwhile, we can arrange the field members as its order in data source by setting it as None where the sort icons in grouping bar and field list buttons for the corresponding field will be removed.

Source

Preview

index.js

index.html

var pivotTableObj = new ej.pivotview.PivotView({
    dataSourceSettings: {
        dataSource: pivotData,
        expandAll: false,
        enableSorting: true,
        drilledMembers: [{ name: 'Country', items: ['France'] }],
        sortSettings: [{ name: 'Country', order: 'Descending' }],
        columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
        values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }],
        rows: [{ name: 'Country' }, { name: 'Products' }],
        filters: []
    },
    showGroupingBar: true,
    height: 350
});
pivotTableObj.appendTo('#PivotTable');

<!DOCTYPE html><html lang="en"><head>
    <title>EJ2 Pivot Grid</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta name="description" content="Typescript Pivot Grid Control">
    <meta name="author" content="Syncfusion">
    <link href="index.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-base/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-buttons/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-calendars/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-dropdowns/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-grids/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-charts/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-inputs/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-lists/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-navigations/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-popups/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-pivotview/styles/material.css" rel="stylesheet">
    
    <link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet">
    
<script src="https://cdn.syncfusion.com/ej2/20.1.57/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
</head>

<body>
    
    <div id="container">
        <div>
            <div id="PivotTable"></div>
        </div>
    </div>


<script>
var ele = document.getElementById('container');
if(ele) {
    ele.style.visibility = "visible";
 }   
        </script>
<script src="index.js" type="text/javascript"></script>
</body></html>

Alphanumeric Sorting

Usually string sorting is applied to field members even if it starts with numbers. But this kind of field members can also be sorted on the basis of numbers that are placed at the beginning of the member name. This can be achieved by setting the dataType property as number to the desired field.

Source

Preview

index.js

index.html

var pivotTableObj = new ej.pivotview.PivotView({
    dataSourceSettings: {
            dataSource: alphanumeric_data,
            rows: [{ name: 'ProductID', dataType: 'number' }],
            columns: [{ name: 'Country' }],
            values: [{ name: 'Sold', caption:'Units Sold' }, { name: 'Amount', caption:'Sold Amount' }],
            formatSettings: [{ name: 'Amount', format: 'C0' }]
    },
    showGroupingBar: true,
   height: 350
});
pivotTableObj.appendTo('#PivotTable');

<!DOCTYPE html><html lang="en"><head>
    <title>EJ2 Pivot Grid</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta name="description" content="Typescript Pivot Grid Control">
    <meta name="author" content="Syncfusion">
    <link href="index.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-base/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-buttons/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-calendars/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-dropdowns/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-grids/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-charts/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-inputs/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-lists/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-navigations/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-popups/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-pivotview/styles/material.css" rel="stylesheet">
    
    <link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet">
    
<script src="https://cdn.syncfusion.com/ej2/20.1.57/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
</head>

<body>
    
    <div id="container">
        <div>
            <div id="PivotTable"></div>
        </div>
    </div>


<script>
var ele = document.getElementById('container');
if(ele) {
    ele.style.visibility = "visible";
 }   
        </script>
<script src="index.js" type="text/javascript"></script>
</body></html>

Value Sorting

This property is applicable only for the relational data source.

Allows to sort individual value field and its aggregated values either in row or column axis in both ascending and descending order. It can been enabled by setting the enableValueSorting property in pivot table to true. On enabling, end user can sort the values by directly clicking the value field header positioned either in row or column axis of the pivot table component.

The value sorting can also be configured using the valueSortSettings option through code behind. The settings required to sort value fields are:

headerText: It allows to set the header names with delimiters, that is used for value sorting. The header names are arranged from Level 1 to Level N, down the hierarchy with a delimiter for better specification.
headerDelimiter: It allows to set the delimiters string to separate the header text between levels.
sortOrder: It allows to set the sort direction of the value field.

Value fields are set to the column axis by default. In such cases, the value sorting applied will have an effect on the column alone. You need to place the value fields in the row axis to do so in row wise. For more information, please refer here.

Source

Preview

index.js

index.html

var pivotTableObj = new ej.pivotview.PivotView({
    dataSourceSettings: {
        dataSource: pivotData,
        expandAll: false,
        valueSortSettings: {
            headerText: 'FY 2015##Sold Amount',
            headerDelimiter: '##',
            sortOrder: 'Descending'
        },
        drilledMembers: [{ name: 'Country', items: ['France'] }],
        columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
        values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }],
        rows: [{ name: 'Country' }, { name: 'Products' }],
        filters: [],
    },
    height: 350,
    enableValueSorting: true
});
pivotTableObj.appendTo('#PivotTable');

<!DOCTYPE html><html lang="en"><head>
    <title>EJ2 Pivot Grid</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta name="description" content="Typescript Pivot Grid Control">
    <meta name="author" content="Syncfusion">
    <link href="index.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-base/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-buttons/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-calendars/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-dropdowns/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-grids/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-charts/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-inputs/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-lists/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-navigations/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-popups/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-pivotview/styles/material.css" rel="stylesheet">
    
    <link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet">
    
<script src="https://cdn.syncfusion.com/ej2/20.1.57/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
</head>

<body>
    
    <div id="container">
        <div>
            <div id="PivotTable"></div>
        </div>
    </div>


<script>
var ele = document.getElementById('container');
if(ele) {
    ele.style.visibility = "visible";
 }   
        </script>
<script src="index.js" type="text/javascript"></script>
</body></html>

Event

ActionBegin

The event actionBegin triggers when clicking the value sort icon or the sort icon in the field button, which is present in both grouping bar and field list UI. This allows user to identify the current action being performed at runtime. It has the following parameters:

dataSourceSettings: It holds the current data source settings such as input data source, rows, columns, values, filters, format settings and so on.
actionName: It holds the name of the current action began. The following are the UI actions and their names:

Action	Action Name
`Sort field`	Sort field
`Value sort icon`	Sort value

fieldInfo: It holds the selected field information.

Note: This option is applicable only when the field based UI actions are performed such as filtering, sorting, removing field from grouping bar, editing and aggregation type change.

cancel: It allows user to restrict the current action.

In the below sample, sort action can be restricted by setting the args.cancel option to true in the actionBegin event.

Source

Preview

index.js

index.html

var pivotTableObj = new ej.pivotview.PivotView({
    dataSourceSettings: {
        dataSource: pivotData,
        expandAll: false,
        enableSorting: true,
        allowLabelFilter: true,
        allowValueFilter: true,
        columns: [{ name: 'Year', caption: 'Production Year' }],
        values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }],
        rows: [{ name: 'Country' }, { name: 'Products' }],
        formatSettings: [{ name: 'Amount', format: 'C0' }],
        filters: []
    },
    height: 350,
    showGroupingBar: true,
    showFieldList: true,
    actionBegin: function (args) {
        if (args.actionName == 'Sort field' && args.actionName == 'Sort value') {
            args.cancel = true;
        }
    }
});
pivotTableObj.appendTo('#PivotTable');

<!DOCTYPE html><html lang="en"><head>
    <title>EJ2 Pivot Grid</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta name="description" content="Typescript Pivot Grid Control">
    <meta name="author" content="Syncfusion">
    <link href="index.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-base/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-buttons/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-calendars/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-dropdowns/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-grids/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-inputs/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-lists/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-navigations/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-popups/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-pivotview/styles/material.css" rel="stylesheet">
    
    
<script src="https://cdn.syncfusion.com/ej2/20.1.57/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
</head>

<body>
    
    <div class="container">
        <div id="PivotTable"></div>
    </div>



<script>
var ele = document.getElementById('container');
if(ele) {
    ele.style.visibility = "visible";
 }   
        </script>
<script src="index.js" type="text/javascript"></script>
</body></html>

ActionComplete

The event actionComplete triggers when the UI actions such as value sorting or sorting via the field button, which is present in both grouping bar and field list UI, is completed. This allows user to identify the current UI actions being completed at runtime. It has the following parameters:

dataSourceSettings: It holds the current data source settings such as input data source, rows, columns, values, filters, format settings and so on.
actionName: It holds the name of the current action completed. The following are the UI actions and their names:

Action	Action Name
`Sort field`	Field sorted
`Value sort icon`	Value sorted

fieldInfo: It holds the selected field information.

Note: This option is applicable only when the field based UI actions are performed such as filtering, sorting, removing field from grouping bar, editing and aggregation type change.

actionInfo: It holds the unique information about the current UI action. For example, if sorting is completed, the event argument contains information such as sort order and the field name.

Source

Preview

index.js

index.html

var pivotTableObj = new ej.pivotview.PivotView({
    dataSourceSettings: {
        dataSource: pivotData,
        expandAll: false,
        enableSorting: true,
        allowLabelFilter: true,
        allowValueFilter: true,
        columns: [{ name: 'Year', caption: 'Production Year' }],
        values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }],
        rows: [{ name: 'Country' }, { name: 'Products' }],
        formatSettings: [{ name: 'Amount', format: 'C0' }],
        filters: []
    },
    height: 350,
    showGroupingBar: true,
    showFieldList: true,
    actionComplete: function (args) {
        if (args.actionName == 'Field sorted' && args.actionName == 'Value sorted') {
            // Triggers when the sort action is completed.
        }
    },
});
pivotTableObj.appendTo('#PivotTable');

<!DOCTYPE html><html lang="en"><head>
    <title>EJ2 Pivot Grid</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta name="description" content="Typescript Pivot Grid Control">
    <meta name="author" content="Syncfusion">
    <link href="index.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-base/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-buttons/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-calendars/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-dropdowns/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-grids/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-inputs/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-lists/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-navigations/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-popups/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-pivotview/styles/material.css" rel="stylesheet">
    
    
<script src="https://cdn.syncfusion.com/ej2/20.1.57/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
</head>

<body>
    
    <div class="container">
        <div id="PivotTable"></div>
    </div>



<script>
var ele = document.getElementById('container');
if(ele) {
    ele.style.visibility = "visible";
 }   
        </script>
<script src="index.js" type="text/javascript"></script>
</body></html>

ActionFailure

The event actionFailure triggers when the current UI action fails to achieve the desired result. It has the following parameters:

actionName: It holds the name of the current action failed. The following are the UI actions and their names:

Action	Action Name
`Sort field`	Sort field
`Value sort icon`	Sort value

errorInfo: It holds the error information of the current UI action.

Source

Preview

index.js

index.html

var pivotTableObj = new ej.pivotview.PivotView({
    dataSourceSettings: {
        dataSource: pivotData,
        expandAll: false,
        columns: [{ name: 'Year', caption: 'Production Year' }, { name: 'Quarter' }],
        values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }],
        rows: [{ name: 'Country' }, { name: 'Products' }],
        formatSettings: [{ name: 'Amount', format: 'C0' }],
        filters: []
    },
    showGroupingBar: true,
    showFieldList: true,
    allowCalculatedField: true,
    showToolbar: true,
    displayOption: { view: 'Both' },
    toolbar: ['New', 'Save', 'Rename', 'Remove', 'Load',
        'Grid', 'Chart', 'MDX', 'Export', 'SubTotal', 'GrandTotal', 'ConditionalFormatting', 'FieldList'],
    allowExcelExport: true,
    allowConditionalFormatting: true,
    allowPdfExport: true,
    actionFailure: function (args) {
        if (args.actionName == 'Sort field' && args.actionName == 'Sort value') {
            // Triggers when the current UI action fails to achieve the desired result.
        }
    },
    height: 350
});
pivotTableObj.appendTo('#PivotTable');

<!DOCTYPE html><html lang="en"><head>
    <title>EJ2 Pivot Grid</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta name="description" content="Typescript Pivot Grid Control">
    <meta name="author" content="Syncfusion">
    <link href="index.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-base/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-buttons/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-calendars/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-dropdowns/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-grids/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-inputs/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-lists/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-navigations/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-popups/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/20.1.57/ej2-pivotview/styles/material.css" rel="stylesheet">
    
    
<script src="https://cdn.syncfusion.com/ej2/20.1.57/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
</head>

<body>
    
    <div class="container">
        <div id="PivotTable"></div>
    </div>



<script>
var ele = document.getElementById('container');
if(ele) {
    ele.style.visibility = "visible";
 }   
        </script>
<script src="index.js" type="text/javascript"></script>
</body></html>

Contents

Member Sorting
Alphanumeric Sorting
Value Sorting
Event
ActionBegin
ActionComplete
ActionFailure

Contents

Member Sorting
Alphanumeric Sorting
Value Sorting
Event
ActionBegin
ActionComplete
ActionFailure

Please provide additional information

Thank you for your feedback and comments.We will rectify this as soon as possible!