Configuration fields[]

An array of pivot grid fields.

Type: Array
Default Value: undefined

If the store type you use is not XmlaStore, you need to describe data from the data source for PivotGrid by assigning a list of fields to this option. Each pivot grid field must be associated with the field in the store using the dataField option.

Fields can be instantly placed into the pivot grid by assigning a value to the area option. If this option is undefined, the field will be displayed in the Field Chooser only.

NOTE: If this array is not specified and the retrieveFields option is set to true, the fields will be generated automatically and placed to the Field Chooser.

allowCrossGroupCalculation

Specifies whether to allow the predefined summary post-processing functions ('absoluteVariation' and 'percentVariation') and runningTotal to take values of different groups into account.

Type: Boolean
Default Value: false

For example, consider you have a runningTotal option set to 'row' and your pivot grid contains the following row split into three groups.

[[8, 3, -6], [5, 3, 0], [1, -4, 2]]

If the allowCrossGroupCalculation option is set to false, each group will be independent of the previous and the row will be displayed as follows.

[[8, 11, 5], [5, 8, 8], [1, -3, -1]]

With the allowCrossGroupCalculation option set to true, the running total will act regardless of group bounds and same row will be displayed as follows.

[[8, 11, 5], [10, 13, 13], [14, 10, 12]]
Show Example:
AngularJS
Knockout
jQuery

In this example, summary values are accumulated across each row as the runningTotal option is set to "row". These values can be accumulated within each expanded group or across all groups. This characteristic is controlled by the value of the allowCrossGroupCalculation option. You can change it at runtime using the switch below the pivot grid.

<div ng-controller="pivotGridController">
    <div style="height:340px; max-width:850px; margin:0 auto" dx-pivot-grid="{
        dataSource: dataSource,
        fieldChooser: { enabled: false }
    }"></div>
    <div style="height:40px; margin-top:5px; text-align:center">
        allowCrossGroupCalculation:&nbsp;
        <div style="display:inline-block; vertical-align:middle" dx-switch="{
            onText: 'true',
            offText: 'false',
            value: false,
            onValueChanged: changeAllowCrossGroupCalc
        }"></div>
    </div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row'
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column',
        filterType: 'exclude',
        filterValues: [[2013]]
    }, {
        groupName: 'date',
        groupInterval: 'year',
        expanded: true
    }, {
        dataField: "amount",
        summaryType: "sum",
        format: "currency",
        area: "data",
        runningTotal: 'row',
        allowCrossGroupCalculation: false
    }]
});

var changeAllowCrossGroupCalc = function (e) {
    pivotGridDataSource.field('amount', {
        allowCrossGroupCalculation: e.value
    });
    pivotGridDataSource.load();
};
    
angular
    .module('myApp', ['dx'])
    .controller('pivotGridController', function ($scope) {
        $scope.dataSource = pivotGridDataSource;
        $scope.changeAllowCrossGroupCalc = changeAllowCrossGroupCalc;
    });
    
angular.element(document).ready(function () {
   angular.bootstrap(document, ['myApp']);
});

In this example, summary values are accumulated across each row as the runningTotal option is set to "row". These values can be accumulated within each expanded group or across all groups. This characteristic is controlled by the value of the allowCrossGroupCalculation option. You can change it at runtime using the switch below the pivot grid.

<div style="height:340px; max-width:850px; margin:0 auto" data-bind="dxPivotGrid: {
    dataSource: dataSource,
    fieldChooser: { enabled: false }
}"></div>
<div style="height:40px; margin-top:5px; text-align:center">
    allowCrossGroupCalculation:&nbsp;
    <div style="display:inline-block; vertical-align:middle" data-bind="dxSwitch: {
        onText: 'true',
        offText: 'false',
        value: false,
        onValueChanged: changeAllowCrossGroupCalc
    }"></div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row'
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column',
        filterType: 'exclude',
        filterValues: [[2013]]
    }, {
        groupName: 'date',
        groupInterval: 'year',
        expanded: true
    }, {
        dataField: "amount",
        summaryType: "sum",
        format: "currency",
        area: "data",
        runningTotal: 'row',
        allowCrossGroupCalculation: false
    }]
});

var changeAllowCrossGroupCalc = function (e) {
    pivotGridDataSource.field('amount', {
        allowCrossGroupCalculation: e.value
    });
    pivotGridDataSource.load();
};
    
var viewModel = {
    dataSource: pivotGridDataSource,
    changeAllowCrossGroupCalc: changeAllowCrossGroupCalc
};

ko.applyBindings(viewModel);

In this example, summary values are accumulated across each row as the runningTotal option is set to "row". These values can be accumulated within each expanded group or across all groups. This characteristic is controlled by the value of the allowCrossGroupCalculation option. You can change it at runtime using the switch below the pivot grid.

<div id="pivotGridContainer" style="height:340px; max-width:850px; margin:0 auto"></div>
<div style="height:40px; margin-top:5px; text-align:center">
    allowCrossGroupCalculation:&nbsp;
    <div id="switchContainer" style="display:inline-block; vertical-align:middle"></div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row'
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column',
        filterType: 'exclude',
        filterValues: [[2013]]
    }, {
        groupName: 'date',
        groupInterval: 'year',
        expanded: true
    }, {
        dataField: "amount",
        summaryType: "sum",
        format: "currency",
        area: "data",
        runningTotal: 'row',
        allowCrossGroupCalculation: false
    }]
});

var changeAllowCrossGroupCalc = function (e) {
    pivotGridDataSource.field('amount', {
        allowCrossGroupCalculation: e.value
    });
    pivotGridDataSource.load();
};
    
$(function () {
    $("#pivotGridContainer").dxPivotGrid({
        dataSource: pivotGridDataSource,
        fieldChooser: { enabled: false }
    });
    
    $("#switchContainer").dxSwitch({
        onText: 'true',
        offText: 'false',
        value: false,
        onValueChanged: changeAllowCrossGroupCalc
    });
});

allowExpandAll

Allows an end-user to expand/collapse all header items within a header level.

Type: Boolean
Default Value: false

With this option enabled, an end-user can right-click a header level and choose the corresponding context menu item to expand or collapse all header items within this level.

Show Example:
AngularJS
Knockout
jQuery

In this example, you can change the allowExpandAll option of the "Year" column field at runtime. Turn the switch below the pivot grid to true. Then, right-click any "Year" column field. The invoked context menu will propose to expand/collapse all column fields on the same level.

<div ng-controller="pivotGridController">
    <div style="height:340px; max-width:850px; margin:0 auto" dx-pivot-grid="{
        dataSource: dataSource,
        fieldChooser: { enabled: false }
    }"></div>
    <div style="height:40px; margin-top:5px; text-align:center">
        allowExpandAll of the "Year" field:&nbsp;
        <div style="display:inline-block; vertical-align:middle" dx-switch="{
            onText: 'true',
            offText: 'false',
            value: false,
            onValueChanged: changeAllowExpandAll
        }"></div>
    </div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row'
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column',
        filterType: 'exclude',
        filterValues: [[2013]]
    }, {
        groupName: 'date',
        groupInterval: 'year',
        expanded: true,
        allowExpandAll: false
    }, {
        dataField: "amount",
        summaryType: "sum",
        format: "currency",
        area: "data"
    }]
});

var changeAllowExpandAll = function (e) {
    pivotGridDataSource.field(3, {
        allowExpandAll: e.value
    });
    pivotGridDataSource.load();
};

angular
    .module('myApp', ['dx'])
    .controller('pivotGridController', function ($scope) {
        $scope.dataSource = pivotGridDataSource;
        $scope.changeAllowExpandAll = changeAllowExpandAll;
    });
    
angular.element(document).ready(function () {
   angular.bootstrap(document, ['myApp']);
});

In this example, you can change the allowExpandAll option of the "Year" column field at runtime. Turn the switch below the pivot grid to true. Then, right-click any "Year" column field. The invoked context menu will propose to expand/collapse all column fields on the same level.

<div style="height:340px; max-width:850px; margin:0 auto" data-bind="dxPivotGrid: {
    dataSource: dataSource,
    fieldChooser: { enabled: false }
}"></div>
<div style="height:40px; margin-top:5px; text-align:center">
    allowExpandAll of the "Year" field:&nbsp;
    <div style="display:inline-block; vertical-align:middle" data-bind="dxSwitch: {
        onText: 'true',
        offText: 'false',
        value: false,
        onValueChanged: changeAllowExpandAll
    }"></div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row'
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column',
        filterType: 'exclude',
        filterValues: [[2013]]
    }, {
        groupName: 'date',
        groupInterval: 'year',
        expanded: true,
        allowExpandAll: false
    }, {
        dataField: "amount",
        summaryType: "sum",
        format: "currency",
        area: "data"
    }]
});

var changeAllowExpandAll = function (e) {
    pivotGridDataSource.field(3, {
        allowExpandAll: e.value
    });
    pivotGridDataSource.load();
};

var viewModel = {
    dataSource: pivotGridDataSource,
    changeAllowExpandAll: changeAllowExpandAll
};

ko.applyBindings(viewModel);

In this example, you can change the allowExpandAll option of the "Year" column field at runtime. Turn the switch below the pivot grid to true. Then, right-click any "Year" column field. The invoked context menu will propose to expand/collapse all column fields on the same level.

<div id="pivotGridContainer" style="height:340px; max-width:850px; margin:0 auto"></div>
<div style="height:40px; margin-top:5px; text-align:center">
    allowExpandAll of the "Year" field:&nbsp;
    <div id="switchContainer" style="display:inline-block; vertical-align:middle"></div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row'
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column',
        filterType: 'exclude',
        filterValues: [[2013]]
    }, {
        groupName: 'date',
        groupInterval: 'year',
        expanded: true,
        allowExpandAll: false
    }, {
        dataField: "amount",
        summaryType: "sum",
        format: "currency",
        area: "data"
    }]
});

var changeAllowExpandAll = function (e) {
    pivotGridDataSource.field(3, {
        allowExpandAll: e.value
    });
    pivotGridDataSource.load();
};

$(function () {
    $("#pivotGridContainer").dxPivotGrid({
        dataSource: pivotGridDataSource,
        fieldChooser: { enabled: false }
    });
    
    $("#switchContainer").dxSwitch({
        onText: 'true',
        offText: 'false',
        value: false,
        onValueChanged: changeAllowExpandAll
    });
});

allowFiltering

Allows an end-user to change filtering options.

Type: Boolean
Default Value: false

With this option enabled, an end-user can filter pivot grid data using the filter icons in the field chooser or on the field panel.

Show Example:
AngularJS
Knockout
jQuery

In this example, you can change the allowFiltering option of the "Region" row field at runtime. Turn the switch below the pivot grid to true. Then, invoke the Field Chooser using the icon in the upper-left corner of the pivot grid. You can notice the filtering icon that accompanies the "Region" field. This icon can be used to apply a filter to this field.

<div ng-controller="pivotGridController">
    <div style="height:350px; max-width:850px; margin:0 auto" dx-pivot-grid="{
        dataSource: dataSource,
        fieldChooser: {
            enabled: true,
            width: 420,
            height: 340
        }
    }"></div>
    <div style="height:40px; margin-top:5px; text-align:center">
        allowFiltering of the "Region" field:&nbsp;
        <div style="display:inline-block; vertical-align:middle" dx-switch="{
            onText: 'true',
            offText: 'false',
            value: false,
            onValueChanged: changeAllowFiltering
        }"></div>
    </div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row',
        expanded: true,
        allowFiltering: false
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column'
    }, {
        dataField: "amount",
        summaryType: "sum",
        format: "currency",
        area: "data"
    }]
});

var changeAllowFiltering = function (e) {
    pivotGridDataSource.field("region", {
        allowFiltering: e.value
    });
    pivotGridDataSource.load();
};

angular
    .module('myApp', ['dx'])
    .controller('pivotGridController', function ($scope) {
        $scope.dataSource = pivotGridDataSource;
        $scope.changeAllowFiltering = changeAllowFiltering;
    });
    
angular.element(document).ready(function () {
   angular.bootstrap(document, ['myApp']);
});

In this example, you can change the allowFiltering option of the "Region" row field at runtime. Turn the switch below the pivot grid to true. Then, invoke the Field Chooser using the icon in the upper-left corner of the pivot grid. You can notice the filtering icon that accompanies the "Region" field. This icon can be used to apply a filter to this field.

<div style="height:350px; max-width:850px; margin:0 auto" data-bind="dxPivotGrid: {
    dataSource: dataSource,
    fieldChooser: {
        enabled: true,
        width: 420,
        height: 340
    }
}"></div>
<div style="height:40px; margin-top:5px; text-align:center">
    allowFiltering of the "Region" field:&nbsp;
    <div style="display:inline-block; vertical-align:middle" data-bind="dxSwitch: {
        onText: 'true',
        offText: 'false',
        value: false,
        onValueChanged: changeAllowFiltering
    }"></div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row',
        expanded: true,
        allowFiltering: false
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column'
    }, {
        dataField: "amount",
        summaryType: "sum",
        format: "currency",
        area: "data"
    }]
});

var changeAllowFiltering = function (e) {
    pivotGridDataSource.field("region", {
        allowFiltering: e.value
    });
    pivotGridDataSource.load();
};

var viewModel = {
    dataSource: pivotGridDataSource,
    changeAllowFiltering: changeAllowFiltering
};

ko.applyBindings(viewModel);

In this example, you can change the allowFiltering option of the "Region" row field at runtime. Turn the switch below the pivot grid to true. Then, invoke the Field Chooser using the icon in the upper-left corner of the pivot grid. You can notice the filtering icon that accompanies the "Region" field. This icon can be used to apply a filter to this field.

<div id="pivotGridContainer" style="height:350px; max-width:850px; margin:0 auto"></div>
<div style="height:40px; margin-top:5px; text-align:center">
    allowFiltering of the "Region" field:&nbsp;
    <div id="switchContainer" style="display:inline-block; vertical-align:middle"></div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row',
        expanded: true,
        allowFiltering: false
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column'
    }, {
        dataField: "amount",
        summaryType: "sum",
        format: "currency",
        area: "data"
    }]
});

var changeAllowFiltering = function (e) {
    pivotGridDataSource.field("region", {
        allowFiltering: e.value
    });
    pivotGridDataSource.load();
};

$(function () {
    $("#pivotGridContainer").dxPivotGrid({
        dataSource: pivotGridDataSource,
        fieldChooser: {
            enabled: true,
            width: 420,
            height: 340
        }
    });
    
    $("#switchContainer").dxSwitch({
        onText: 'true',
        offText: 'false',
        value: false,
        onValueChanged: changeAllowFiltering
    });
});

allowSorting

Allows an end-user to change sorting options.

Type: Boolean
Default Value: false

With this option enabled, an end-user can sort pivot grid data by clicking the arrow icons in the field chooser or on the field panel.

Show Example:
AngularJS
Knockout
jQuery

In this example, you can change the allowSorting option of the "Region" row field at runtime. Turn the switch below the pivot grid to true. Then, invoke the Field Chooser using the icon in the upper-left corner of the pivot grid. You can notice the sorting icon that accompanies the "Region" field. Click this icon to sort this field in ascending/descending order.

<div ng-controller="pivotGridController">
    <div style="height:350px; max-width:850px; margin:0 auto" dx-pivot-grid="{
        dataSource: dataSource,
        fieldChooser: {
            enabled: true,
            width: 420,
            height: 340
        }
    }"></div>
    <div style="height:40px; margin-top:5px; text-align:center">
        allowSorting of the "Region" field:&nbsp;
        <div style="display:inline-block; vertical-align:middle" dx-switch="{
            onText: 'true',
            offText: 'false',
            value: false,
            onValueChanged: changeAllowSorting
        }"></div>
    </div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row',
        allowSorting: false
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column'
    }, {
        dataField: "amount",
        summaryType: "sum",
        format: "currency",
        area: "data"
    }]
});

var changeAllowSorting = function (e) {
    pivotGridDataSource.field("region", {
        allowSorting: e.value
    });
    pivotGridDataSource.load();
};

angular
    .module('myApp', ['dx'])
    .controller('pivotGridController', function ($scope) {
        $scope.dataSource = pivotGridDataSource;
        $scope.changeAllowSorting = changeAllowSorting;
    });
    
angular.element(document).ready(function () {
   angular.bootstrap(document, ['myApp']);
});

In this example, you can change the allowSorting option of the "Region" row field at runtime. Turn the switch below the pivot grid to true. Then, invoke the Field Chooser using the icon in the upper-left corner of the pivot grid. You can notice the sorting icon that accompanies the "Region" field. Click this icon to sort this field in ascending/descending order.

<div style="height:350px; max-width:850px; margin:0 auto" data-bind="dxPivotGrid: {
    dataSource: dataSource,
    fieldChooser: {
        enabled: true,
        width: 420,
        height: 340
    }
}"></div>
<div style="height:40px; margin-top:5px; text-align:center">
    allowSorting of the "Region" field:&nbsp;
    <div style="display:inline-block; vertical-align:middle" data-bind="dxSwitch: {
        onText: 'true',
        offText: 'false',
        value: false,
        onValueChanged: changeAllowSorting
    }"></div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row',
        allowSorting: false
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column'
    }, {
        dataField: "amount",
        summaryType: "sum",
        format: "currency",
        area: "data"
    }]
});

var changeAllowSorting = function (e) {
    pivotGridDataSource.field("region", {
        allowSorting: e.value
    });
    pivotGridDataSource.load();
};

var viewModel = {
    dataSource: pivotGridDataSource,
    changeAllowSorting: changeAllowSorting
};

ko.applyBindings(viewModel);

In this example, you can change the allowSorting option of the "Region" row field at runtime. Turn the switch below the pivot grid to true. Then, invoke the Field Chooser using the icon in the upper-left corner of the pivot grid. You can notice the sorting icon that accompanies the "Region" field. Click this icon to sort this field in ascending/descending order.

<div id="pivotGridContainer" style="height:350px; max-width:850px; margin:0 auto"></div>
<div style="height:40px; margin-top:5px; text-align:center">
    allowSorting of the "Region" field:&nbsp;
    <div id="switchContainer" style="display:inline-block; vertical-align:middle"></div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row',
        allowSorting: false
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column'
    }, {
        dataField: "amount",
        summaryType: "sum",
        format: "currency",
        area: "data"
    }]
});

var changeAllowSorting = function (e) {
    pivotGridDataSource.field("region", {
        allowSorting: e.value
    });
    pivotGridDataSource.load();
};

$(function () {
    $("#pivotGridContainer").dxPivotGrid({
        dataSource: pivotGridDataSource,
        fieldChooser: {
            enabled: true,
            width: 420,
            height: 340
        }
    });
    
    $("#switchContainer").dxSwitch({
        onText: 'true',
        offText: 'false',
        value: false,
        onValueChanged: changeAllowSorting
    });
});

allowSortingBySummary

Allows an end-user to sort columns by summary values.

Type: Boolean
Default Value: false

With this option enabled, an end-user can use the context menu of a column or row header to apply sorting by summary values.

Show Example:
AngularJS
Knockout
jQuery

In this example, you can change the allowSortingBySummary option of the "Region" row field at runtime. Turn the switch below the pivot grid to true. Then, right-click any column header to invoke a context menu. It will offer to sort the "Region" field by this column.

<div ng-controller="pivotGridController">
    <div style="height:300px; max-width:850px; margin:0 auto" dx-pivot-grid="{
        dataSource: dataSource,
        fieldChooser: { enabled: false }
    }"></div>
    <div style="height:40px; margin-top:5px; text-align:center">
        allowSortingBySummary of the "Region" field:&nbsp;
        <div style="display:inline-block; vertical-align:middle" dx-switch="{
            onText: 'true',
            offText: 'false',
            value: false,
            onValueChanged: changeAllowSortingBySummary
        }"></div>
    </div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row',
        allowSortingBySummary: false
    }, {
        dataField: "city",
        width: 150,
        area: "row",
        selector: function (data) {
            return data.city + " (" + data.country + ")";
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column'
    }, {
        dataField: "amount",
        summaryType: "sum",
        format: "currency",
        area: "data"
    }]
});

var changeAllowSortingBySummary = function (e) {
    pivotGridDataSource.field("region", {
        allowSortingBySummary: e.value
    });
    pivotGridDataSource.load();
};

angular
    .module('myApp', ['dx'])
    .controller('pivotGridController', function ($scope) {
        $scope.dataSource = pivotGridDataSource;
        $scope.changeAllowSortingBySummary = changeAllowSortingBySummary;
    });
    
angular.element(document).ready(function () {
   angular.bootstrap(document, ['myApp']);
});

In this example, you can change the allowSortingBySummary option of the "Region" row field at runtime. Turn the switch below the pivot grid to true. Then, right-click any column header to invoke a context menu. It will offer to sort the "Region" field by this column.

<div style="height:300px; max-width:850px; margin:0 auto" data-bind="dxPivotGrid: {
    dataSource: dataSource,
    fieldChooser: { enabled: false }
}"></div>
<div style="height:40px; margin-top:5px; text-align:center">
    allowSortingBySummary of the "Region" field:&nbsp;
    <div style="display:inline-block; vertical-align:middle" data-bind="dxSwitch: {
        onText: 'true',
        offText: 'false',
        value: false,
        onValueChanged: changeAllowSortingBySummary
    }"></div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row',
        allowSortingBySummary: false
    }, {
        dataField: "city",
        width: 150,
        area: "row",
        selector: function (data) {
            return data.city + " (" + data.country + ")";
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column'
    }, {
        dataField: "amount",
        summaryType: "sum",
        format: "currency",
        area: "data"
    }]
});

var changeAllowSortingBySummary = function (e) {
    pivotGridDataSource.field("region", {
        allowSortingBySummary: e.value
    });
    pivotGridDataSource.load();
};

var viewModel = {
    dataSource: pivotGridDataSource,
    changeAllowSortingBySummary: changeAllowSortingBySummary
};

ko.applyBindings(viewModel);

In this example, you can change the allowSortingBySummary option of the "Region" row field at runtime. Turn the switch below the pivot grid to true. Then, right-click any column header to invoke a context menu. It will offer to sort the "Region" field by this column.

<div id="pivotGridContainer" style="height:300px; max-width:850px; margin:0 auto"></div>
<div style="height:40px; margin-top:5px; text-align:center">
    allowSortingBySummary of the "Region" field:&nbsp;
    <div id="switchContainer" style="display:inline-block; vertical-align:middle"></div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row',
        allowSortingBySummary: false
    }, {
        dataField: "city",
        width: 150,
        area: "row",
        selector: function (data) {
            return data.city + " (" + data.country + ")";
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column'
    }, {
        dataField: "amount",
        summaryType: "sum",
        format: "currency",
        area: "data"
    }]
});

var changeAllowSortingBySummary = function (e) {
    pivotGridDataSource.field("region", {
        allowSortingBySummary: e.value
    });
    pivotGridDataSource.load();
};

$(function () {
    $("#pivotGridContainer").dxPivotGrid({
        dataSource: pivotGridDataSource,
        fieldChooser: { enabled: false }
    });
    
    $("#switchContainer").dxSwitch({
        onText: 'true',
        offText: 'false',
        value: false,
        onValueChanged: changeAllowSortingBySummary
    });
});

area

Type of the area where the field is located.

Type: String
Default Value: undefined
Accepted Values: undefined | 'row' | 'column' | 'data' | 'filter'

If this option is undefined, the field is displayed in the Field Chooser only. An end-user can place this field to any area. Otherwise, the field is displayed in the corresponding area of the pivot grid and its area can be changed by an end-user using the Field Chooser.

NOTE: You can allow users to place the field to the Data Fields area only by setting the isMeasure option to true. To deny using the integrated Field Chooser, set the PivotGrid | fieldChooser | enabled option to false.

areaIndex

Index among the other fields displayed within the same area.

Type: Number
Default Value: undefined

You can change the field order within an area by using this option. By default, the field order in the pivot grid is similar to the fields order in the fields array.

NOTE: The order of fields affects the hierarchy structure in the header of the pivot grid. This option should not be confused with the sorting concept.

Show Example:
AngularJS
Knockout
jQuery

In this example, the "Year" and "Quarter" fields belong to the column area. Originally, "Year" goes before "Quarter" in the hierarchy. To swap them, click the button located below the pivot grid. Internally, this action will swap the values of the areaIndex option of these fields.

<div ng-controller="pivotGridController">
    <div style="height:300px; max-width:850px; margin:0 auto" dx-pivot-grid="{
        dataSource: dataSource,
        fieldChooser: { enabled: false }
    }"></div>
    <div style="width:160px; display:block; margin:0 auto" dx-button="{
        text: 'Swap area indexes',
        onClick: swapAreaIndexes
    }"></div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row'
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        groupInterval: 'year',
        area: 'column',
        caption: 'Year',
        areaIndex: 0
    }, {
        dataField: 'date',
        dataType: 'date',
        groupInterval: 'quarter',
        area: 'column',
        caption: 'Quarter',
    }, {
        dataField: "amount",
        summaryType: "sum",
        format: "currency",
        area: "data"
    }]
});

var swapAreaIndexes = function (e) {
    var updatedYearIndex = pivotGridDataSource.field('Quarter').areaIndex;
    var updatedQuarterIndex = pivotGridDataSource.field('Year').areaIndex;

    pivotGridDataSource.field("Year", {
        areaIndex: updatedYearIndex
    });
    pivotGridDataSource.field("Quarter", {
        areaIndex: updatedQuarterIndex
    });

    pivotGridDataSource.load();
};

angular
    .module('myApp', ['dx'])
    .controller('pivotGridController', function ($scope) {
        $scope.dataSource = pivotGridDataSource;
        $scope.swapAreaIndexes = swapAreaIndexes;
    });
    
angular.element(document).ready(function () {
   angular.bootstrap(document, ['myApp']);
});

In this example, the "Year" and "Quarter" fields belong to the column area. Originally, "Year" goes before "Quarter" in the hierarchy. To swap them, click the button located below the pivot grid. Internally, this action will swap the values of the areaIndex option of these fields.

<div style="height:300px; max-width:850px; margin:0 auto" data-bind="dxPivotGrid: {
    dataSource: dataSource,
    fieldChooser: { enabled: false }
}"></div>
<div style="width:160px; display:block; margin:0 auto" data-bind="dxButton: {
    text: 'Swap area indexes',
    onClick: swapAreaIndexes
}"></div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row'
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        groupInterval: 'year',
        area: 'column',
        caption: 'Year',
        areaIndex: 0
    }, {
        dataField: 'date',
        dataType: 'date',
        groupInterval: 'quarter',
        area: 'column',
        caption: 'Quarter',
    }, {
        dataField: "amount",
        summaryType: "sum",
        format: "currency",
        area: "data"
    }]
});

var swapAreaIndexes = function (e) {
    var updatedYearIndex = pivotGridDataSource.field('Quarter').areaIndex;
    var updatedQuarterIndex = pivotGridDataSource.field('Year').areaIndex;

    pivotGridDataSource.field("Year", {
        areaIndex: updatedYearIndex
    });
    pivotGridDataSource.field("Quarter", {
        areaIndex: updatedQuarterIndex
    });

    pivotGridDataSource.load();
};

var viewModel = {
    dataSource: pivotGridDataSource,
    swapAreaIndexes: swapAreaIndexes
};

ko.applyBindings(viewModel);

In this example, the "Year" and "Quarter" fields belong to the column area. Originally, "Year" goes before "Quarter" in the hierarchy. To swap them, click the button located below the pivot grid. Internally, this action will swap the values of the areaIndex option of these fields.

<div id="pivotGridContainer" style="height:300px; max-width:850px; margin:0 auto"></div>
<div id="buttonContainer" style="width:160px; display:block; margin:0 auto"></div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row'
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        groupInterval: 'year',
        area: 'column',
        caption: 'Year',
        areaIndex: 0
    }, {
        dataField: 'date',
        dataType: 'date',
        groupInterval: 'quarter',
        area: 'column',
        caption: 'Quarter',
    }, {
        dataField: "amount",
        summaryType: "sum",
        format: "currency",
        area: "data"
    }]
});

var swapAreaIndexes = function (e) {
    var updatedYearIndex = pivotGridDataSource.field('Quarter').areaIndex;
    var updatedQuarterIndex = pivotGridDataSource.field('Year').areaIndex;

    pivotGridDataSource.field("Year", {
        areaIndex: updatedYearIndex
    });
    pivotGridDataSource.field("Quarter", {
        areaIndex: updatedQuarterIndex
    });

    pivotGridDataSource.load();
};

$(function () {
    $("#pivotGridContainer").dxPivotGrid({
        dataSource: pivotGridDataSource,
        fieldChooser: { enabled: false }
    });
    
    $("#buttonContainer").dxButton({
        text: 'Swap area indexes',
        onClick: swapAreaIndexes
    });
});

calculateCustomSummary

Allows you to use a custom aggregate function to calculate the summary values. Cannot be used for the XmlaStore store type.

Type: function(options)
Function parameters:
options: Object
Summary information.
Object structure:
summaryProcess: String
Indicates the stage of the summary value calculation; possible values are 'start', 'calculate' or 'finalize'.
value: any
The data value for aggregation.
totalValue: any
The resulting summary value.

If predefined aggregate functions do not meet your requirements, implement a custom one and assign it to the calculateCustomSummary option. If the summaryType property is set to 'custom', this function will be called for each value from the data field.

The calculation of a summary value is conducted in several phases. Usually, you need to initialize totalValue on start. Then, you modify totalValue in the calculation phase. In the final phase, you adjust totalValue. To identify the current phase, use the summaryProcess field of the object passed as a parameter.

The following code demonstrates a general structure of the calculateCustomSummary function.

JavaScript
var pivotGridOptions = {
    dataSource: {
        //...
        fields: [{
            //...
            summaryType: 'custom',
            calculateCustomSummary: function (options) {
                if (options.summaryProcess == 'start') {
                    // Initializing "totalValue" here
                }
                if (options.summaryProcess == 'calculate') {
                    // Modifying "totalValue" here
                }
                if (options.summaryProcess == 'finalize') {
                    // Assigning the final value to "totalValue" here
                }
            }
        },
        //...
        ]
    }
}

calculateSummaryValue

Specifies a callback function that allows you to modify summary values after they are calculated.

Type: function(e)
Function parameters:
The object for accessing the data of the current cell and the neighboring cells.
Return Value: Number
The new cell value.
Default Value: undefined

With this function, you can perform additional calculations on each summary value and take into account the values of the neighboring cells. The function takes the Summary Cell object as an argument and returns the new summary value. The summary value calculated by aggregation of facts (according to the summaryType) is replaced with the value returned by this function.

There are several predefined post-processing functions. You can enable one of them by using the summaryDisplayMode option.

caption

A caption that will be displayed in the pivot grid's field chooser and field panel to identify the field.

Type: String
Default Value: undefined

customizeText

Specifies a callback function that returns the text to be displayed in the cells of a field.

Type: function(cellInfo)
Function parameters:
cellInfo: Object
The value of a cell.
Object structure:
A cell value as it is specified in the data source.
valueText: String
A cell value with applied formatting.
Return Value: String
The text to be displayed in a cell.

When implementing a callback function for this option, you can access the value of a cell using the object passed as the function's parameter. Alternatively, you can use the this object within the function. This object has the same structure as the object passed as the parameter.

dataField

Name of the data source field containing data for the pivot grid field.

Type: String
Default Value: undefined

dataType

Specifies a type of field values.

Type: String
Default Value: undefined
Accepted Values: 'string' | 'number' | 'date'

displayFolder

The name of the folder in which the field is located.

Type: String
Default Value: undefined

You can use folders to organize fields in the "All Fields" section of the Field Chooser.

Show Example:
AngularJS
Knockout
jQuery

In this example, all fields are specified explicitly as the retrieveFields option is set to false. There are fields that define date constituents: "Year", "Quarter" and "Month". To be collected into one folder in the field chooser, these fields have an identical value of the displayFolder option. Click the icon in the upper-left grid corner to invoke the field chooser and note the folder.

<div ng-controller="pivotGridController">
    <div style="height:600px; max-width:850px; margin:0 auto" dx-pivot-grid="{
        dataSource: dataSource
    }"></div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    retrieveFields: false,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row',
        expanded: true
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column',
        groupInterval: 'year',
        caption: 'Year',
        displayFolder: 'Date'
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column',
        groupInterval: 'quarter',
        caption: 'Quarter',
        displayFolder: 'Date'
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column',
        groupInterval: 'month',
        caption: 'Month',
        displayFolder: 'Date'
    }, {
        dataField: 'amount',
        summaryType: 'sum',
        format: 'currency',
        area: 'data'
    }]
});

angular
    .module('myApp', ['dx'])
    .controller('pivotGridController', function ($scope) {
        $scope.dataSource = pivotGridDataSource;
    });
    
angular.element(document).ready(function () {
   angular.bootstrap(document, ['myApp']);
});

In this example, all fields are specified explicitly as the retrieveFields option is set to false. There are fields that define date constituents: "Year", "Quarter" and "Month". To be collected into one folder in the field chooser, these fields have an identical value of the displayFolder option. Click the icon in the upper-left grid corner to invoke the field chooser and note the folder.

<div style="height:600px; max-width:850px; margin:0 auto" data-bind="dxPivotGrid: {
    dataSource: dataSource
}"></div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    retrieveFields: false,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row',
        expanded: true
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column',
        groupInterval: 'year',
        caption: 'Year',
        displayFolder: 'Date'
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column',
        groupInterval: 'quarter',
        caption: 'Quarter',
        displayFolder: 'Date'
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column',
        groupInterval: 'month',
        caption: 'Month',
        displayFolder: 'Date'
    }, {
        dataField: 'amount',
        summaryType: 'sum',
        format: 'currency',
        area: 'data'
    }]
});

var viewModel = {
    dataSource: pivotGridDataSource
};

ko.applyBindings(viewModel);

In this example, all fields are specified explicitly as the retrieveFields option is set to false. There are fields that define date constituents: "Year", "Quarter" and "Month". To be collected into one folder in the field chooser, these fields have an identical value of the displayFolder option. Click the icon in the upper-left grid corner to invoke the field chooser and note the folder.

<div id="pivotGridContainer" style="height:600px; max-width:850px; margin:0 auto"></div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    retrieveFields: false,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row',
        expanded: true
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column',
        groupInterval: 'year',
        caption: 'Year',
        displayFolder: 'Date'
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column',
        groupInterval: 'quarter',
        caption: 'Quarter',
        displayFolder: 'Date'
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column',
        groupInterval: 'month',
        caption: 'Month',
        displayFolder: 'Date'
    }, {
        dataField: 'amount',
        summaryType: 'sum',
        format: 'currency',
        area: 'data'
    }]
});

$(function () {
    $("#pivotGridContainer").dxPivotGrid({
        dataSource: pivotGridDataSource
    });
});

expanded

Indicates whether all header items of the field's header level are expanded.

Type: Boolean
Default Value: false

Show Example:
AngularJS
Knockout
jQuery

In this example, the "Year" field appears expanded, because its expanded option being set to true.

<div ng-controller="pivotGridController">
    <div style="height:365px; max-width:750px; margin:0 auto" dx-pivot-grid="pivotGridOptions"></div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row'
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column'
    }, {
        groupName: 'date',
        groupInterval: 'year',
        expanded: true
    }, {
        dataField: "amount",
        summaryType: "sum",
        format: "currency",
        area: "data"
    }]
});

angular
    .module('myApp', ['dx'])
    .controller('pivotGridController', function ($scope) {
        $scope.pivotGridOptions = {
            dataSource: pivotGridDataSource,
            fieldChooser: { enabled: false }
        };
    });
    
angular.element(document).ready(function () {
   angular.bootstrap(document, ['myApp']);
});

In this example, the "Year" field appears expanded, because its expanded option being set to true.

<div style="height:365px; max-width:750px; margin:0 auto" data-bind="dxPivotGrid: pivotGridOptions"></div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row'
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column'
    }, {
        groupName: 'date',
        groupInterval: 'year',
        expanded: true
    }, {
        dataField: "amount",
        summaryType: "sum",
        format: "currency",
        area: "data"
    }]
});

var viewModel = {
    pivotGridOptions: {
        dataSource: pivotGridDataSource,
        fieldChooser: { enabled: false }
    }
};

ko.applyBindings(viewModel);

In this example, the "Year" field appears expanded, because its expanded option being set to true.

<div id="pivotGridContainer" style="height:365px; max-width:750px; margin:0 auto"></div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row'
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column'
    }, {
        groupName: 'date',
        groupInterval: 'year',
        expanded: true
    }, {
        dataField: "amount",
        summaryType: "sum",
        format: "currency",
        area: "data"
    }]
});

$(function () {
    $("#pivotGridContainer").dxPivotGrid({
        dataSource: pivotGridDataSource,
        fieldChooser: { enabled: false }
    });
});

filterType

The filter type for the current field.

Type: String
Default Value: 'include'
Accepted Values: 'include' | 'exclude'

You can exclude values from data of the data source or include values to keep only them.

Show Example:
AngularJS
Knockout
jQuery

In this example, you can apply a filter to the "Date" field at runtime using a set of widgets located below the pivot grid. Use the CheckBox widgets to select years that must be pushed in the filterValues array. Turn the Switch widget to choose whether to include or exclude the selected years from the pivot grid.

<div ng-controller="pivotGridController">
    <div style="height:290px; max-width:850px; margin:0 auto" dx-pivot-grid="{
        dataSource: dataSource,
        fieldChooser: { enabled: false }
    }"></div>
    <div style="height:70px; width:250px; margin:0 auto; margin-top:5px; display:block; vertical-align:top">
        filterType:&nbsp;
        <div style="vertical-align:middle; margin-bottom:5px" dx-switch="{
            onText: 'include',
            offText: 'exclude',
            value: true,
            width: 80,
            onValueChanged: changeFilterType
        }"></div><br/>
        <div style="vertical-align:middle" dx-check-box="{
            text: '2013',
            value: true,
            onValueChanged: changeFilterValues
        }"></div>&nbsp;
        <div style="vertical-align:middle" dx-check-box="{
            text: '2014',
            value: true,
            onValueChanged: changeFilterValues
        }"></div>&nbsp;
        <div style="vertical-align:middle" dx-check-box="{
            text: '2015',
            value: true,
            onValueChanged: changeFilterValues
        }"></div>
    </div>
</div>
var filterValues = [[2013], [2014], [2015]];

var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row'
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column',
        filterType: 'include',
        filterValues: filterValues
    }, {
        dataField: 'amount',
        summaryType: 'sum',
        format: 'currency',
        area: 'data'
    }]
});

var changeFilterType = function (e) {
    var updFilterType = e.value ? 'include' : 'exclude';
    pivotGridDataSource.field("date", {
        filterType: updFilterType
    });
    pivotGridDataSource.load();
};

var changeFilterValues = function (e) {
    var year = Number(e.component.option('text'));
    if (e.value) {
        filterValues.push([year]);
    }
    else {
        var index;
        $.each(filterValues, function (i, filter) {
            if (filter[0] == year) {
                index = i;
                return false;
            }
        });
        filterValues.splice(index, 1);
    }
    pivotGridDataSource.load();
};

angular
    .module('myApp', ['dx'])
    .controller('pivotGridController', function ($scope) {
        $scope.dataSource = pivotGridDataSource;
        $scope.changeFilterType = changeFilterType;
        $scope.changeFilterValues = changeFilterValues;
    });
    
angular.element(document).ready(function () {
   angular.bootstrap(document, ['myApp']);
});

In this example, you can apply a filter to the "Date" field at runtime using a set of widgets located below the pivot grid. Use the CheckBox widgets to select years that must be pushed in the filterValues array. Turn the Switch widget to choose whether to include or exclude the selected years from the pivot grid.

<div style="height:290px; max-width:850px; margin:0 auto" data-bind="dxPivotGrid: {
    dataSource: dataSource,
    fieldChooser: { enabled: false }
}"></div>
<div style="height:70px; width:250px; margin:0 auto; margin-top:5px; display:block; vertical-align:top">
    filterType:&nbsp;
    <div style="vertical-align:middle; margin-bottom:5px" data-bind="dxSwitch: {
        onText: 'include',
        offText: 'exclude',
        value: true,
        width: 80,
        onValueChanged: changeFilterType
    }"></div><br/>
    <div style="vertical-align:middle" data-bind="dxCheckBox: {
        text: '2013',
        value: true,
        onValueChanged: changeFilterValues
    }"></div>&nbsp;
    <div style="vertical-align:middle" data-bind="dxCheckBox: {
        text: '2014',
        value: true,
        onValueChanged: changeFilterValues
    }"></div>&nbsp;
    <div style="vertical-align:middle" data-bind="dxCheckBox: {
        text: '2015',
        value: true,
        onValueChanged: changeFilterValues
    }"></div>
</div>
var filterValues = [[2013], [2014], [2015]];

var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row'
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column',
        filterType: 'include',
        filterValues: filterValues
    }, {
        dataField: 'amount',
        summaryType: 'sum',
        format: 'currency',
        area: 'data'
    }]
});

var changeFilterType = function (e) {
    var updFilterType = e.value ? 'include' : 'exclude';
    pivotGridDataSource.field("date", {
        filterType: updFilterType
    });
    pivotGridDataSource.load();
};

var changeFilterValues = function (e) {
    var year = Number(e.component.option('text'));
    if (e.value) {
        filterValues.push([year]);
    }
    else {
        var index;
        $.each(filterValues, function (i, filter) {
            if (filter[0] == year) {
                index = i;
                return false;
            }
        });
        filterValues.splice(index, 1);
    }
    pivotGridDataSource.load();
};

var viewModel = {
    dataSource: pivotGridDataSource,
    changeFilterType: changeFilterType,
    changeFilterValues: changeFilterValues
};

ko.applyBindings(viewModel);

In this example, you can apply a filter to the "Date" field at runtime using a set of widgets located below the pivot grid. Use the CheckBox widgets to select years that must be pushed in the filterValues array. Turn the Switch widget to choose whether to include or exclude the selected years from the pivot grid.

<div id="pivotGridContainer" style="height:290px; max-width:850px; margin:0 auto"></div>
<div style="height:70px; width:250px; margin:0 auto; margin-top:5px; display:block; vertical-align:top">
    filterType:&nbsp;
    <div id="switchContainer" style="vertical-align:middle; margin-bottom:5px"></div><br/>
    <div id="checkbox2013" style="vertical-align:middle"></div>&nbsp;
    <div id="checkbox2014" style="vertical-align:middle"></div>&nbsp;
    <div id="checkbox2015" style="vertical-align:middle"></div>
</div>
var filterValues = [[2013], [2014], [2015]];

var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row'
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column',
        filterType: 'include',
        filterValues: filterValues
    }, {
        dataField: 'amount',
        summaryType: 'sum',
        format: 'currency',
        area: 'data'
    }]
});

var changeFilterType = function (e) {
    var updFilterType = e.value ? 'include' : 'exclude';
    pivotGridDataSource.field("date", {
        filterType: updFilterType
    });
    pivotGridDataSource.load();
};

var changeFilterValues = function (e) {
    var year = Number(e.component.option('text'));
    if (e.value) {
        filterValues.push([year]);
    }
    else {
        var index;
        $.each(filterValues, function (i, filter) {
            if (filter[0] == year) {
                index = i;
                return false;
            }
        });
        filterValues.splice(index, 1);
    }
    pivotGridDataSource.load();
};

$(function () {
    $("#pivotGridContainer").dxPivotGrid({
        dataSource: pivotGridDataSource,
        fieldChooser: { enabled: false }
    });
    
    $("#switchContainer").dxSwitch({
        onText: 'include',
        offText: 'exclude',
        value: true,
        width: 80,
        onValueChanged: changeFilterType
    });
    
    $("#checkbox2013").dxCheckBox({
        text: '2013',
        value: true,
        onValueChanged: changeFilterValues
    });
    
    $("#checkbox2014").dxCheckBox({
        text: '2014',
        value: true,
        onValueChanged: changeFilterValues
    });
    
    $("#checkbox2015").dxCheckBox({
        text: '2015',
        value: true,
        onValueChanged: changeFilterValues
    });
});

filterValues

The filter values for the current field.

Type: Array
Default Value: undefined

You can exclude these values from the data returned by the data source or keep them only by including these values.

Show Example:
AngularJS
Knockout
jQuery

In this example, you can apply a filter to the "Date" field at runtime using a set of widgets located below the pivot grid. Use the CheckBox widgets to select years that must be pushed in the filterValues array. Turn the Switch widget to choose whether to include or exclude the selected years from the pivot grid.

<div ng-controller="pivotGridController">
    <div style="height:290px; max-width:850px; margin:0 auto" dx-pivot-grid="{
        dataSource: dataSource,
        fieldChooser: { enabled: false }
    }"></div>
    <div style="height:70px; width:250px; margin:0 auto; margin-top:5px; display:block; vertical-align:top">
        filterType:&nbsp;
        <div style="vertical-align:middle; margin-bottom:5px" dx-switch="{
            onText: 'include',
            offText: 'exclude',
            value: true,
            width: 80,
            onValueChanged: changeFilterType
        }"></div><br/>
        <div style="vertical-align:middle" dx-check-box="{
            text: '2013',
            value: true,
            onValueChanged: changeFilterValues
        }"></div>&nbsp;
        <div style="vertical-align:middle" dx-check-box="{
            text: '2014',
            value: true,
            onValueChanged: changeFilterValues
        }"></div>&nbsp;
        <div style="vertical-align:middle" dx-check-box="{
            text: '2015',
            value: true,
            onValueChanged: changeFilterValues
        }"></div>
    </div>
</div>
var filterValues = [[2013], [2014], [2015]];

var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row'
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column',
        filterType: 'include',
        filterValues: filterValues
    }, {
        dataField: 'amount',
        summaryType: 'sum',
        format: 'currency',
        area: 'data'
    }]
});

var changeFilterType = function (e) {
    var updFilterType = e.value ? 'include' : 'exclude';
    pivotGridDataSource.field("date", {
        filterType: updFilterType
    });
    pivotGridDataSource.load();
};

var changeFilterValues = function (e) {
    var year = Number(e.component.option('text'));
    if (e.value) {
        filterValues.push([year]);
    }
    else {
        var index;
        $.each(filterValues, function (i, filter) {
            if (filter[0] == year) {
                index = i;
                return false;
            }
        });
        filterValues.splice(index, 1);
    }
    pivotGridDataSource.load();
};

angular
    .module('myApp', ['dx'])
    .controller('pivotGridController', function ($scope) {
        $scope.dataSource = pivotGridDataSource;
        $scope.changeFilterType = changeFilterType;
        $scope.changeFilterValues = changeFilterValues;
    });
    
angular.element(document).ready(function () {
   angular.bootstrap(document, ['myApp']);
});

In this example, you can apply a filter to the "Date" field at runtime using a set of widgets located below the pivot grid. Use the CheckBox widgets to select years that must be pushed in the filterValues array. Turn the Switch widget to choose whether to include or exclude the selected years from the pivot grid.

<div style="height:290px; max-width:850px; margin:0 auto" data-bind="dxPivotGrid: {
    dataSource: dataSource,
    fieldChooser: { enabled: false }
}"></div>
<div style="height:70px; width:250px; margin:0 auto; margin-top:5px; display:block; vertical-align:top">
    filterType:&nbsp;
    <div style="vertical-align:middle; margin-bottom:5px" data-bind="dxSwitch: {
        onText: 'include',
        offText: 'exclude',
        value: true,
        width: 80,
        onValueChanged: changeFilterType
    }"></div><br/>
    <div style="vertical-align:middle" data-bind="dxCheckBox: {
        text: '2013',
        value: true,
        onValueChanged: changeFilterValues
    }"></div>&nbsp;
    <div style="vertical-align:middle" data-bind="dxCheckBox: {
        text: '2014',
        value: true,
        onValueChanged: changeFilterValues
    }"></div>&nbsp;
    <div style="vertical-align:middle" data-bind="dxCheckBox: {
        text: '2015',
        value: true,
        onValueChanged: changeFilterValues
    }"></div>
</div>
var filterValues = [[2013], [2014], [2015]];

var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row'
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column',
        filterType: 'include',
        filterValues: filterValues
    }, {
        dataField: 'amount',
        summaryType: 'sum',
        format: 'currency',
        area: 'data'
    }]
});

var changeFilterType = function (e) {
    var updFilterType = e.value ? 'include' : 'exclude';
    pivotGridDataSource.field("date", {
        filterType: updFilterType
    });
    pivotGridDataSource.load();
};

var changeFilterValues = function (e) {
    var year = Number(e.component.option('text'));
    if (e.value) {
        filterValues.push([year]);
    }
    else {
        var index;
        $.each(filterValues, function (i, filter) {
            if (filter[0] == year) {
                index = i;
                return false;
            }
        });
        filterValues.splice(index, 1);
    }
    pivotGridDataSource.load();
};

var viewModel = {
    dataSource: pivotGridDataSource,
    changeFilterType: changeFilterType,
    changeFilterValues: changeFilterValues
};

ko.applyBindings(viewModel);

In this example, you can apply a filter to the "Date" field at runtime using a set of widgets located below the pivot grid. Use the CheckBox widgets to select years that must be pushed in the filterValues array. Turn the Switch widget to choose whether to include or exclude the selected years from the pivot grid.

<div id="pivotGridContainer" style="height:290px; max-width:850px; margin:0 auto"></div>
<div style="height:70px; width:250px; margin:0 auto; margin-top:5px; display:block; vertical-align:top">
    filterType:&nbsp;
    <div id="switchContainer" style="vertical-align:middle; margin-bottom:5px"></div><br/>
    <div id="checkbox2013" style="vertical-align:middle"></div>&nbsp;
    <div id="checkbox2014" style="vertical-align:middle"></div>&nbsp;
    <div id="checkbox2015" style="vertical-align:middle"></div>
</div>
var filterValues = [[2013], [2014], [2015]];

var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row'
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column',
        filterType: 'include',
        filterValues: filterValues
    }, {
        dataField: 'amount',
        summaryType: 'sum',
        format: 'currency',
        area: 'data'
    }]
});

var changeFilterType = function (e) {
    var updFilterType = e.value ? 'include' : 'exclude';
    pivotGridDataSource.field("date", {
        filterType: updFilterType
    });
    pivotGridDataSource.load();
};

var changeFilterValues = function (e) {
    var year = Number(e.component.option('text'));
    if (e.value) {
        filterValues.push([year]);
    }
    else {
        var index;
        $.each(filterValues, function (i, filter) {
            if (filter[0] == year) {
                index = i;
                return false;
            }
        });
        filterValues.splice(index, 1);
    }
    pivotGridDataSource.load();
};

$(function () {
    $("#pivotGridContainer").dxPivotGrid({
        dataSource: pivotGridDataSource,
        fieldChooser: { enabled: false }
    });
    
    $("#switchContainer").dxSwitch({
        onText: 'include',
        offText: 'exclude',
        value: true,
        width: 80,
        onValueChanged: changeFilterType
    });
    
    $("#checkbox2013").dxCheckBox({
        text: '2013',
        value: true,
        onValueChanged: changeFilterValues
    });
    
    $("#checkbox2014").dxCheckBox({
        text: '2014',
        value: true,
        onValueChanged: changeFilterValues
    });
    
    $("#checkbox2015").dxCheckBox({
        text: '2015',
        value: true,
        onValueChanged: changeFilterValues
    });
});

format

Specifies a display format for field values.

Type: Format
Default Value: ''

NOTE: DevExtreme widgets provide a wide choice of predefined formats. If you are, however, going to use custom formats, link the Globalize library to your project. Learn how to do this from the Installation topic.

See Also
  • format - provides a comprehensive overview of formatting capabilities.

groupIndex

The index of the field within a group.

Type: Number
Default Value: undefined

Show Example:
AngularJS
Knockout
jQuery

In this example, the "Region", "Country" and "City" fields are collected in the "Location" group. Their hierarchy in this group is specified by the groupIndex option.

<div ng-controller="pivotGridController">
    <div style="height:600px; max-width:850px; margin:0 auto" dx-pivot-grid="{
        dataSource: dataSource
    }"></div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        groupName: 'Location',
        area: 'row',
        expanded: true
    }, {
        dataField: 'region',
        groupName: 'Location',
        groupIndex: 0,
        width: 120
    }, {
        dataField: 'city',
        groupName: 'Location',
        groupIndex: 2,
        width: 80
    }, {
        dataField: 'country',
        groupName: 'Location',
        groupIndex: 1,
        width: 80
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column'
    }, {
        dataField: 'amount',
        summaryType: 'sum',
        format: 'currency',
        area: 'data'
    }]
});

angular
    .module('myApp', ['dx'])
    .controller('pivotGridController', function ($scope) {
        $scope.dataSource = pivotGridDataSource;
    });
    
angular.element(document).ready(function () {
   angular.bootstrap(document, ['myApp']);
});

In this example, the "Region", "Country" and "City" fields are collected in the "Location" group. Their hierarchy in this group is specified by the groupIndex option.

<div style="height:600px; max-width:850px; margin:0 auto" data-bind="dxPivotGrid: {
    dataSource: dataSource
}"></div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        groupName: 'Location',
        area: 'row',
        expanded: true
    }, {
        dataField: 'region',
        groupName: 'Location',
        groupIndex: 0,
        width: 120
    }, {
        dataField: 'city',
        groupName: 'Location',
        groupIndex: 2,
        width: 80
    }, {
        dataField: 'country',
        groupName: 'Location',
        groupIndex: 1,
        width: 80
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column'
    }, {
        dataField: 'amount',
        summaryType: 'sum',
        format: 'currency',
        area: 'data'
    }]
});

var viewModel = {
    dataSource: pivotGridDataSource
};

ko.applyBindings(viewModel);

In this example, the "Region", "Country" and "City" fields are collected in the "Location" group. Their hierarchy in this group is specified by the groupIndex option.

<div id="pivotGridContainer" style="height:600px; max-width:850px; margin:0 auto"></div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        groupName: 'Location',
        area: 'row',
        expanded: true
    }, {
        dataField: 'region',
        groupName: 'Location',
        groupIndex: 0,
        width: 120
    }, {
        dataField: 'city',
        groupName: 'Location',
        groupIndex: 2,
        width: 80
    }, {
        dataField: 'country',
        groupName: 'Location',
        groupIndex: 1,
        width: 80
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column'
    }, {
        dataField: 'amount',
        summaryType: 'sum',
        format: 'currency',
        area: 'data'
    }]
});

$(function () {
    $("#pivotGridContainer").dxPivotGrid({
        dataSource: pivotGridDataSource
    });
});

groupInterval

Specifies how the values of the current field are combined into groups. Cannot be used for the XmlaStore store type.

Type: String|Number
Default Value: undefined
Accepted Values: 'year' | 'quarter' | 'month' | 'day' | 'dayOfWeek'

Show Example:
AngularJS
Knockout
jQuery

In this example, you can change the groupInterval option of the "Date" field using the select box below the pivot grid.

<div ng-controller="pivotGridController">
    <div style="height:300px; max-width:750px; margin:0 auto" dx-pivot-grid="{
        dataSource: pivotGridDataSource,
        fieldChooser: { enabled: false }
    }"></div>
    <div style="height:40px; margin-top:5px; text-align:center">
        "Date" field's "groupInterval":&nbsp;
        <div style="width:250px; display:inline-block; vertical-align:middle" dx-select-box="{
            dataSource: selectBoxDataSource,
            valueExpr: 'value',
            displayExpr: 'name',
            value: 'year',
            onValueChanged: changeGroupInterval
        }"></div>
    </div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row'
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column',
        groupInterval: 'year'
    }, {
        dataField: 'amount',
        summaryType: 'sum',
        format: 'currency',
        area: 'data'
    }]
});

var selectBoxDataSource = [
    { name: 'Year', value: 'year' },
    { name: 'Quarter', value: 'quarter' },
    { name: 'Month', value: 'month' },
    { name: 'Day', value: 'day' },
    { name: 'Day of Week', value: 'dayOfWeek' },
];

var changeGroupInterval = function (e) {
    pivotGridDataSource.field('date', {
        groupInterval: e.value
    });
    pivotGridDataSource.load();
};

angular
    .module('myApp', ['dx'])
    .controller('pivotGridController', function ($scope) {
        $scope.pivotGridDataSource = pivotGridDataSource;
        $scope.selectBoxDataSource = selectBoxDataSource;
        $scope.changeGroupInterval = changeGroupInterval;
    });
    
angular.element(document).ready(function () {
   angular.bootstrap(document, ['myApp']);
});

In this example, you can change the groupInterval option of the "Date" field using the select box below the pivot grid.

<div style="height:300px; max-width:750px; margin:0 auto" data-bind="dxPivotGrid: {
    dataSource: pivotGridDataSource,
    fieldChooser: { enabled: false }
}"></div>
<div style="height:40px; margin-top:5px; text-align:center">
    "Date" field's "groupInterval":&nbsp;
    <div style="width:250px; display:inline-block; vertical-align:middle" data-bind="dxSelectBox: {
        dataSource: selectBoxDataSource,
        valueExpr: 'value',
        displayExpr: 'name',
        value: 'year',
        onValueChanged: changeGroupInterval
    }"></div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row'
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column',
        groupInterval: 'year'
    }, {
        dataField: 'amount',
        summaryType: 'sum',
        format: 'currency',
        area: 'data'
    }]
});

var selectBoxDataSource = [
    { name: 'Year', value: 'year' },
    { name: 'Quarter', value: 'quarter' },
    { name: 'Month', value: 'month' },
    { name: 'Day', value: 'day' },
    { name: 'Day of Week', value: 'dayOfWeek' },
];

var changeGroupInterval = function (e) {
    pivotGridDataSource.field('date', {
        groupInterval: e.value
    });
    pivotGridDataSource.load();
};

var viewModel = {
    pivotGridDataSource: pivotGridDataSource,
    selectBoxDataSource: selectBoxDataSource,
    changeGroupInterval: changeGroupInterval
};

ko.applyBindings(viewModel);

In this example, you can change the groupInterval option of the "Date" field using the select box below the pivot grid.

<div id="pivotGridContainer" style="height:300px; max-width:750px; margin:0 auto"></div>
<div style="height:40px; margin-top:5px; text-align:center">
    "Date" field's "groupInterval":&nbsp;
    <div id="selectBoxContainer" style="width:250px; display:inline-block; vertical-align:middle"></div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row'
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column',
        groupInterval: 'year'
    }, {
        dataField: 'amount',
        summaryType: 'sum',
        format: 'currency',
        area: 'data'
    }]
});

var selectBoxDataSource = [
    { name: 'Year', value: 'year' },
    { name: 'Quarter', value: 'quarter' },
    { name: 'Month', value: 'month' },
    { name: 'Day', value: 'day' },
    { name: 'Day of Week', value: 'dayOfWeek' },
];

var changeGroupInterval = function (e) {
    pivotGridDataSource.field('date', {
        groupInterval: e.value
    });
    pivotGridDataSource.load();
};

$(function () {
    $('#pivotGridContainer').dxPivotGrid({
        dataSource: pivotGridDataSource,
        fieldChooser: { enabled: false }
    });
    
    $('#selectBoxContainer').dxSelectBox({
        dataSource: selectBoxDataSource,
        valueExpr: 'value',
        displayExpr: 'name',
        value: 'year',
        onValueChanged: changeGroupInterval
    });
});

groupName

The name of the group to which the field belongs.

Type: String
Default Value: undefined

isMeasure

Specifies whether the field should be treated as a Data Field.

Type: Boolean
Default Value: undefined

Use this option to specify what areas are available for the field to be placed into.

You can deny end-users the option of putting the field into the "row", "column" and "filter" areas by setting this option to true.

If this option is set to false, the field is treated as a dimension attribute and could not be placed into the "data" area.

By default, the field has no area restrictions.

precision

Deprecated

Use the format | precision option instead.

Specifies a precision for formatted field values.

Type: Number
Default Value: undefined

runningTotal

Specifies whether to summarize each next summary value with the previous one by rows or columns.

Type: String
Default Value: undefined
Accepted Values: 'row' | 'column'

After aggregating and post-processing stages, you can perform the last calculation on the resulting summary values — adding of each summary value to the previous one (starting from the second) by rows or columns. For example, if you have the following row values

[2, 6, -3, 0, 8]

and the runningTotal option is set to 'row', the pivot grid will display the row as follows.

[2, 8, 5, 5, 13]
Show Example:
AngularJS
Knockout
jQuery

This example illustrates the calculation of running totals in PivotGrid. To choose whether to perform it against rows or columns, use the select box below the pivot grid.

<div ng-controller="pivotGridController">
    <div style="height:300px; max-width:750px; margin:0 auto" dx-pivot-grid="{
        dataSource: pivotGridDataSource,
        fieldChooser: { enabled: false }
    }"></div>
    <div style="height:40px; margin-top:5px; text-align:center">
        Calculate running totals against&nbsp;
        <div style="width:250px; display:inline-block; vertical-align:middle" dx-select-box="{
            dataSource: selectBoxDataSource,
            valueExpr: 'value',
            displayExpr: 'name',
            value: 'row',
            onValueChanged: changeRunningTotal
        }"></div>
    </div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row'
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column'
    }, {
        dataField: 'amount',
        summaryType: 'sum',
        format: 'currency',
        area: 'data',
        runningTotal: 'row'
    }]
});

var selectBoxDataSource = [
    { name: 'rows', value: 'row' },
    { name: 'columns', value: 'column' }
];

var changeRunningTotal = function (e) {
    pivotGridDataSource.field('amount', {
        runningTotal: e.value
    });
    pivotGridDataSource.load();
};

angular
    .module('myApp', ['dx'])
    .controller('pivotGridController', function ($scope) {
        $scope.pivotGridDataSource = pivotGridDataSource;
        $scope.selectBoxDataSource = selectBoxDataSource;
        $scope.changeRunningTotal = changeRunningTotal;
    });
    
angular.element(document).ready(function () {
   angular.bootstrap(document, ['myApp']);
});

This example illustrates the calculation of running totals in PivotGrid. To choose whether to perform it against rows or columns, use the select box below the pivot grid.

<div style="height:300px; max-width:750px; margin:0 auto" data-bind="dxPivotGrid: {
    dataSource: pivotGridDataSource,
    fieldChooser: { enabled: false }
}"></div>
<div style="height:40px; margin-top:5px; text-align:center">
    Calculate running totals against&nbsp;
    <div style="width:250px; display:inline-block; vertical-align:middle" data-bind="dxSelectBox: {
        dataSource: selectBoxDataSource,
        valueExpr: 'value',
        displayExpr: 'name',
        value: 'row',
        onValueChanged: changeRunningTotal
    }"></div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row'
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column'
    }, {
        dataField: 'amount',
        summaryType: 'sum',
        format: 'currency',
        area: 'data',
        runningTotal: 'row'
    }]
});

var selectBoxDataSource = [
    { name: 'rows', value: 'row' },
    { name: 'columns', value: 'column' }
];

var changeRunningTotal = function (e) {
    pivotGridDataSource.field('amount', {
        runningTotal: e.value
    });
    pivotGridDataSource.load();
};

var viewModel = {
    pivotGridDataSource: pivotGridDataSource,
    selectBoxDataSource: selectBoxDataSource,
    changeRunningTotal: changeRunningTotal
};

ko.applyBindings(viewModel);

This example illustrates the calculation of running totals in PivotGrid. To choose whether to perform it against rows or columns, use the select box below the pivot grid.

<div id="pivotGridContainer" style="height:300px; max-width:750px; margin:0 auto"></div>
<div style="height:40px; margin-top:5px; text-align:center">
    Calculate running totals against&nbsp;
    <div id="selectBoxContainer" style="width:250px; display:inline-block; vertical-align:middle"></div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row'
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column'
    }, {
        dataField: 'amount',
        summaryType: 'sum',
        format: 'currency',
        area: 'data',
        runningTotal: 'row'
    }]
});

var selectBoxDataSource = [
    { name: 'rows', value: 'row' },
    { name: 'columns', value: 'column' }
];

var changeRunningTotal = function (e) {
    pivotGridDataSource.field('amount', {
        runningTotal: e.value
    });
    pivotGridDataSource.load();
};

$(function () {
    $('#pivotGridContainer').dxPivotGrid({
        dataSource: pivotGridDataSource,
        fieldChooser: { enabled: false }
    });
    
    $('#selectBoxContainer').dxSelectBox({
        dataSource: selectBoxDataSource,
        valueExpr: 'value',
        displayExpr: 'name',
        value: 'row',
        onValueChanged: changeRunningTotal
    });
});

selector

Specifies the function that determines how to split data from the data source into ranges for header items. Cannot be used for the XmlaStore store type.

Type: function(data)
Default Value: undefined

When implementing a callback function for this option, you can access the value of a field using the object passed as the function's parameter. This function must return the range name according to the data value passed into it.

The following code implements a selector function that groups the birth dates by decades.

JavaScript
function (data) {
    return Math.floor(data.birthdate.getFullYear() / 10) * 10 
}

Another example: the following function divides values into "> 1000" and "< 1000" groups against a threshold value of 1000.

JavaScript
function (data) {
    return data.amount > 1000 ? '> 1000' : '< 1000'
}

showGrandTotals

Specifies whether or not to display Grand Total values for the field.

Type: Boolean
Default Value: true

NOTE: This options takes effect only when set for a field located in the "data" area.

Show Example:
AngularJS
Knockout
jQuery

In this example, you can change the showGrandTotals option of the "Amount" field at runtime. By default, this option is true. Turn the switch below the pivot grid to false. This will cause the grand total values to be hidden.

<div ng-controller="pivotGridController">
    <div style="height:290px; max-width:850px; margin:0 auto" dx-pivot-grid="{
        dataSource: dataSource,
        fieldChooser: { enabled: false }
    }"></div>
    <div style="height:40px; margin-top:5px; text-align:center">
        showGrandTotals of the "Amount" field:&nbsp;
        <div style="display:inline-block; vertical-align:middle" dx-switch="{
            onText: 'true',
            offText: 'false',
            value: true,
            onValueChanged: changeShowGrandTotals
        }"></div>
    </div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row'
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column'
    }, {
        dataField: "amount",
        summaryType: "sum",
        format: "currency",
        area: "data",
        showGrandTotals: true
    }]
});

var changeShowGrandTotals = function (e) {
    pivotGridDataSource.field("amount", {
        showGrandTotals: e.value
    });
    pivotGridDataSource.load();
};

angular
    .module('myApp', ['dx'])
    .controller('pivotGridController', function ($scope) {
        $scope.dataSource = pivotGridDataSource;
        $scope.changeShowGrandTotals = changeShowGrandTotals;
    });
    
angular.element(document).ready(function () {
   angular.bootstrap(document, ['myApp']);
});

In this example, you can change the showGrandTotals option of the "Amount" field at runtime. By default, this option is true. Turn the switch below the pivot grid to false. This will cause the grand total values to be hidden.

<div style="height:290px; max-width:850px; margin:0 auto" data-bind="dxPivotGrid: {
    dataSource: dataSource,
    fieldChooser: { enabled: false }
}"></div>
<div style="height:40px; margin-top:5px; text-align:center">
    showGrandTotals of the "Amount" field:&nbsp;
    <div style="display:inline-block; vertical-align:middle" data-bind="dxSwitch: {
        onText: 'true',
        offText: 'false',
        value: true,
        onValueChanged: changeShowGrandTotals
    }"></div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row'
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column'
    }, {
        dataField: "amount",
        summaryType: "sum",
        format: "currency",
        area: "data",
        showGrandTotals: true
    }]
});

var changeShowGrandTotals = function (e) {
    pivotGridDataSource.field("amount", {
        showGrandTotals: e.value
    });
    pivotGridDataSource.load();
};

var viewModel = {
    dataSource: pivotGridDataSource,
    changeShowGrandTotals: changeShowGrandTotals
};

ko.applyBindings(viewModel);

In this example, you can change the showGrandTotals option of the "Amount" field at runtime. By default, this option is true. Turn the switch below the pivot grid to false. This will cause the grand total values to be hidden.

<div id="pivotGridContainer" style="height:290px; max-width:850px; margin:0 auto"></div>
<div style="height:40px; margin-top:5px; text-align:center">
    showGrandTotals of the "Amount" field:&nbsp;
    <div id="switchContainer" style="display:inline-block; vertical-align:middle"></div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row'
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column'
    }, {
        dataField: "amount",
        summaryType: "sum",
        format: "currency",
        area: "data",
        showGrandTotals: true
    }]
});

var changeShowGrandTotals = function (e) {
    pivotGridDataSource.field("amount", {
        showGrandTotals: e.value
    });
    pivotGridDataSource.load();
};

$(function () {
    $("#pivotGridContainer").dxPivotGrid({
        dataSource: pivotGridDataSource,
        fieldChooser: { enabled: false }
    });
    
    $("#switchContainer").dxSwitch({
        onText: 'true',
        offText: 'false',
        value: true,
        onValueChanged: changeShowGrandTotals
    });
});

showTotals

Specifies whether or not to display Total values for the field.

Type: Boolean
Default Value: true

NOTE: Setting this option to false for a data field hides all summary values except for Grand Total values.

Show Example:
AngularJS
Knockout
jQuery

In this example, you can change the showTotals option of the "Quarter" column field at runtime. By default, this option is true. Turn the switch below the pivot grid to false to hide the total values of the "Quarter" field. Please note that the "Year" field, despite being a parent field for "Quarter", does not hide its total values.

<div ng-controller="pivotGridController">
    <div style="height:350px; max-width:850px; margin:0 auto" dx-pivot-grid="{
        dataSource: dataSource,
        fieldChooser: { enabled: false }
    }"></div>
    <div style="height:40px; margin-top:5px; text-align:center">
        showTotals of the "Date" field:&nbsp;
        <div style="display:inline-block; vertical-align:middle" dx-switch="{
            onText: 'true',
            offText: 'false',
            value: true,
            onValueChanged: changeShowTotals
        }"></div>
    </div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row'
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column',
        expanded: true,
        filterType: 'include',
        filterValues: [[2015]]
    }, {
        groupName: 'date',
        groupInterval: 'quarter',
        showTotals: true
    }, {
        dataField: "amount",
        summaryType: "sum",
        format: "currency",
        area: "data"
    }]
});

var changeShowTotals = function (e) {
    pivotGridDataSource.field(3, {
        showTotals: e.value
    });
    pivotGridDataSource.load();
};

angular
    .module('myApp', ['dx'])
    .controller('pivotGridController', function ($scope) {
        $scope.dataSource = pivotGridDataSource;
        $scope.changeShowTotals = changeShowTotals;
    });
    
angular.element(document).ready(function () {
   angular.bootstrap(document, ['myApp']);
});

In this example, you can change the showTotals option of the "Quarter" column field at runtime. By default, this option is true. Turn the switch below the pivot grid to false to hide the total values of the "Quarter" field. Please note that the "Year" field, despite being a parent field for "Quarter", does not hide its total values.

<div style="height:350px; max-width:850px; margin:0 auto" data-bind="dxPivotGrid: {
    dataSource: dataSource,
    fieldChooser: { enabled: false }
}"></div>
<div style="height:40px; margin-top:5px; text-align:center">
    showTotals of the "Date" field:&nbsp;
    <div style="display:inline-block; vertical-align:middle" data-bind="dxSwitch: {
        onText: 'true',
        offText: 'false',
        value: true,
        onValueChanged: changeShowTotals
    }"></div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row'
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column',
        expanded: true,
        filterType: 'include',
        filterValues: [[2015]]
    }, {
        groupName: 'date',
        groupInterval: 'quarter',
        showTotals: true
    }, {
        dataField: "amount",
        summaryType: "sum",
        format: "currency",
        area: "data"
    }]
});

var changeShowTotals = function (e) {
    pivotGridDataSource.field(3, {
        showTotals: e.value
    });
    pivotGridDataSource.load();
};

var viewModel = {
    dataSource: pivotGridDataSource,
    changeShowTotals: changeShowTotals
};

ko.applyBindings(viewModel);

In this example, you can change the showTotals option of the "Quarter" column field at runtime. By default, this option is true. Turn the switch below the pivot grid to false to hide the total values of the "Quarter" field. Please note that the "Year" field, despite being a parent field for "Quarter", does not hide its total values.

<div id="pivotGridContainer" style="height:350px; max-width:850px; margin:0 auto"></div>
<div style="height:40px; margin-top:5px; text-align:center">
    showTotals of the "Date" field:&nbsp;
    <div id="switchContainer" style="display:inline-block; vertical-align:middle"></div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row'
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column',
        expanded: true,
        filterType: 'include',
        filterValues: [[2015]]
    }, {
        groupName: 'date',
        groupInterval: 'quarter',
        showTotals: true
    }, {
        dataField: "amount",
        summaryType: "sum",
        format: "currency",
        area: "data"
    }]
});

var changeShowTotals = function (e) {
    pivotGridDataSource.field(3, {
        showTotals: e.value
    });
    pivotGridDataSource.load();
};

$(function () {
    $("#pivotGridContainer").dxPivotGrid({
        dataSource: pivotGridDataSource,
        fieldChooser: { enabled: false }
    });
    
    $("#switchContainer").dxSwitch({
        onText: 'true',
        offText: 'false',
        value: true,
        onValueChanged: changeShowTotals
    });
});

sortBy

Specifies how field data should be sorted. Can be used for the XmlaStore store type only.

Type: String
Default Value: undefined
Accepted Values: 'displayText' | 'value'

In XMLA, each data object of a dimension attribute has a value and a display text. For example, month attribute display texts are 'January', 'February', ..., 'December'; and values are 1, 2, ..., 12. Sorting by value will make the first month be 'January', whereas, sorting by display text will perform alphabet sorting and make the first month be 'April'.

Show Example:
AngularJS
Knockout
jQuery

In this example, months in a column field can be sorted either alphabetically or according to their order in a year. Use the select box below the grid to change the order of months.

<div ng-controller="pivotGridController">
    <div style="height:250px; max-width:750px; margin:0 auto" dx-pivot-grid="{
        dataSource: pivotGridDataSource,
        fieldChooser: { enabled: false }
    }"></div>
    <div style="height:40px; margin-top:5px; text-align:center">
        Sort months by:&nbsp;
        <div style="width:250px; display:inline-block; vertical-align:middle" dx-select-box="{
            dataSource: selectBoxDataSource,
            valueExpr: 'value',
            displayExpr: 'name',
            value: 'value',
            onValueChanged: changeSortBy
        }"></div>
    </div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: {
        type: 'xmla',
        url: 'https://demos.devexpress.com/Services/OLAP/msmdpump.dll',
        catalog: 'Adventure Works DW Standard Edition',
        cube: 'Adventure Works'
    },
    retrieveFields: false,
    fields: [
        { dataField: '[Product].[Category]', area: 'row' },
        { dataField: '[Product].[Subcategory]', area: 'row', width: 130 },
        {
            dataField: '[Ship Date].[Calendar Year]',
            area: 'column',
            filterType: 'include',
            filterValues: ['CY 2004'],
            caption: '2004',
            expanded: true
        },
        { dataField: "[Ship Date].[Month of Year]", area: "column", sortBy: 'value' },
        { dataField: '[Measures].[Customer Count]', area: 'data' }
    ]
});

var selectBoxDataSource = [
    { name: 'displayText', value: 'displayText' },
    { name: 'value', value: 'value' }
];

var changeSortBy = function (e) {
    pivotGridDataSource.field(3, {
        sortBy: e.value
    });
    pivotGridDataSource.load();
};

angular
    .module('myApp', ['dx'])
    .controller('pivotGridController', function ($scope) {
        $scope.pivotGridDataSource = pivotGridDataSource;
        $scope.selectBoxDataSource = selectBoxDataSource;
        $scope.changeSortBy = changeSortBy;
    });
    
angular.element(document).ready(function () {
   angular.bootstrap(document, ['myApp']);
});

In this example, months in a column field can be sorted either alphabetically or according to their order in a year. Use the select box below the grid to change the order of months.

<div style="height:250px; max-width:750px; margin:0 auto" data-bind="dxPivotGrid: {
    dataSource: pivotGridDataSource,
    fieldChooser: { enabled: false }
}"></div>
<div style="height:40px; margin-top:5px; text-align:center">
    Sort months by:&nbsp;
    <div style="width:250px; display:inline-block; vertical-align:middle" data-bind="dxSelectBox: {
        dataSource: selectBoxDataSource,
        valueExpr: 'value',
        displayExpr: 'name',
        value: 'value',
        onValueChanged: changeSortBy
    }"></div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: {
        type: 'xmla',
        url: 'https://demos.devexpress.com/Services/OLAP/msmdpump.dll',
        catalog: 'Adventure Works DW Standard Edition',
        cube: 'Adventure Works'
    },
    retrieveFields: false,
    fields: [
        { dataField: '[Product].[Category]', area: 'row' },
        { dataField: '[Product].[Subcategory]', area: 'row', width: 130 },
        {
            dataField: '[Ship Date].[Calendar Year]',
            area: 'column',
            filterType: 'include',
            filterValues: ['CY 2004'],
            caption: '2004',
            expanded: true
        },
        { dataField: "[Ship Date].[Month of Year]", area: "column", sortBy: 'value' },
        { dataField: '[Measures].[Customer Count]', area: 'data' }
    ]
});

var selectBoxDataSource = [
    { name: 'displayText', value: 'displayText' },
    { name: 'value', value: 'value' }
];

var changeSortBy = function (e) {
    pivotGridDataSource.field(3, {
        sortBy: e.value
    });
    pivotGridDataSource.load();
};

var viewModel = {
    pivotGridDataSource: pivotGridDataSource,
    selectBoxDataSource: selectBoxDataSource,
    changeSortBy: changeSortBy
};

ko.applyBindings(viewModel);

In this example, months in a column field can be sorted either alphabetically or according to their order in a year. Use the select box below the grid to change the order of months.

<div id="pivotGridContainer" style="height:250px; max-width:750px; margin:0 auto"></div>
<div style="height:40px; margin-top:5px; text-align:center">
    Sort months by:&nbsp;
    <div id="selectBoxContainer" style="width:250px; display:inline-block; vertical-align:middle"></div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: {
        type: 'xmla',
        url: 'https://demos.devexpress.com/Services/OLAP/msmdpump.dll',
        catalog: 'Adventure Works DW Standard Edition',
        cube: 'Adventure Works'
    },
    retrieveFields: false,
    fields: [
        { dataField: '[Product].[Category]', area: 'row' },
        { dataField: '[Product].[Subcategory]', area: 'row', width: 130 },
        {
            dataField: '[Ship Date].[Calendar Year]',
            area: 'column',
            filterType: 'include',
            filterValues: ['CY 2004'],
            caption: '2004',
            expanded: true
        },
        { dataField: "[Ship Date].[Month of Year]", area: "column", sortBy: 'value' },
        { dataField: '[Measures].[Customer Count]', area: 'data' }
    ]
});

var selectBoxDataSource = [
    { name: 'displayText', value: 'displayText' },
    { name: 'value', value: 'value' }
];

var changeSortBy = function (e) {
    pivotGridDataSource.field(3, {
        sortBy: e.value
    });
    pivotGridDataSource.load();
};

$(function () {
    $('#pivotGridContainer').dxPivotGrid({
        dataSource: pivotGridDataSource,
        fieldChooser: { enabled: false }
    });
    
    $('#selectBoxContainer').dxSelectBox({
        dataSource: selectBoxDataSource,
        valueExpr: 'value',
        displayExpr: 'name',
        value: 'value',
        onValueChanged: changeSortBy
    });
});

sortBySummaryField

Sorts the header items of this field by the summary values of another field.

Type: String
Default Value: undefined

NOTE: Oftentimes, the sortBySummaryField and sortBySummaryPath options are used together to sort header items. If the value of the sortBySummaryPath option is an empty array or undefined, the header items are sorted by the grand total values of the data field specified here.

Show Example:
AngularJS
Knockout
jQuery

In this example, the "Region" items are sorted by the summary values of the "Amount" field. Pay attention to the "Region" field configuration. It has the sortBySummaryField and sortBySummaryPath options specified. You can change the value of the latter option at runtime using the select box below the pivot grid. It will sort the "Region" items by the summary values of another column.

<div ng-controller="pivotGridController">
    <div style="height:280px; max-width:750px; margin:0 auto" dx-pivot-grid="{
        dataSource: pivotGridDataSource
    }"></div>
    <div style="height:40px; margin-top:5px; text-align:center">
        Choose a Column:&nbsp;
        <div style="width:250px; display:inline-block; vertical-align:middle" dx-select-box="{
            dataSource: selectBoxDataSource,
            displayExpr: 'text',
            valueExpr: 'value',
            onValueChanged: changeSummaryPath
        }"></div>
    </div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row',
        sortBySummaryField: 'amount',
        sortBySummaryPath: [2015]
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column'
    }, {
        dataField: "amount",
        summaryType: "sum",
        format: "currency",
        area: "data"
    }]
});

var selectBoxDataSource = [
    { text: '2013', value: [2013] },
    { text: '2014', value: [2014] },
    { text: '2015', value: [2015] },
    { text: 'Grand Total', value: [] }
];

var changeSummaryPath = function (e) {
    pivotGridDataSource.field("region", {
        sortBySummaryPath: e.value
    });
    pivotGridDataSource.load();
};

angular
    .module('myApp', ['dx'])
    .controller('pivotGridController', function ($scope) {
        $scope.pivotGridDataSource = pivotGridDataSource;
        $scope.selectBoxDataSource - selectBoxDataSource;
        $scope.changeSummaryPath = changeSummaryPath;
    });
    
angular.element(document).ready(function () {
   angular.bootstrap(document, ['myApp']);
});

In this example, the "Region" items are sorted by the summary values of the "Amount" field. Pay attention to the "Region" field configuration. It has the sortBySummaryField and sortBySummaryPath options specified. You can change the value of the latter option at runtime using the select box below the pivot grid. It will sort the "Region" items by the summary values of another column.

<div style="height:280px; max-width:750px; margin:0 auto" data-bind="dxPivotGrid: {
    dataSource: pivotGridDataSource
}"></div>
<div style="height:40px; margin-top:5px; text-align:center">
    Choose a Column:&nbsp;
    <div style="width:250px; display:inline-block; vertical-align:middle" data-bind="dxSelectBox: {
        dataSource: selectBoxDataSource,
        displayExpr: 'text',
        valueExpr: 'value',
        onValueChanged: changeSummaryPath
    }"></div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row',
        sortBySummaryField: 'amount',
        sortBySummaryPath: [2015]
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column'
    }, {
        dataField: "amount",
        summaryType: "sum",
        format: "currency",
        area: "data"
    }]
});

var selectBoxDataSource = [
    { text: '2013', value: [2013] },
    { text: '2014', value: [2014] },
    { text: '2015', value: [2015] },
    { text: 'Grand Total', value: [] }
];

var changeSummaryPath = function (e) {
    pivotGridDataSource.field("region", {
        sortBySummaryPath: e.value
    });
    pivotGridDataSource.load();
};

var viewModel = {
    pivotGridDataSource: pivotGridDataSource,
    selectBoxDataSource: selectBoxDataSource,
    changeSummaryPath: changeSummaryPath
};

ko.applyBindings(viewModel);

In this example, the "Region" items are sorted by the summary values of the "Amount" field. Pay attention to the "Region" field configuration. It has the sortBySummaryField and sortBySummaryPath options specified. You can change the value of the latter option at runtime using the select box below the pivot grid. It will sort the "Region" items by the summary values of another column.

<div id="pivotGridContainer" style="height:280px; max-width:750px; margin:0 auto"></div>
<div style="height:40px; margin-top:5px; text-align:center">
    Choose a Column:&nbsp;
    <div id="selectBoxContainer" style="width:250px; display:inline-block; vertical-align:middle"></div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row',
        sortBySummaryField: 'amount',
        sortBySummaryPath: [2015]
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column'
    }, {
        dataField: "amount",
        summaryType: "sum",
        format: "currency",
        area: "data"
    }]
});

var selectBoxDataSource = [
    { text: '2013', value: [2013] },
    { text: '2014', value: [2014] },
    { text: '2015', value: [2015] },
    { text: 'Grand Total', value: [] }
];

var changeSummaryPath = function (e) {
    pivotGridDataSource.field("region", {
        sortBySummaryPath: e.value
    });
    pivotGridDataSource.load();
};

$(function () {
    $("#pivotGridContainer").dxPivotGrid({
        dataSource: pivotGridDataSource
    });
    
    $("#selectBoxContainer").dxSelectBox({
        dataSource: selectBoxDataSource,
        displayExpr: 'text',
        valueExpr: 'value',
        onValueChanged: changeSummaryPath
    });
});

sortBySummaryPath

The array of field names that specify a path to column/row whose summary field is used for sorting of this field's header items.

Type: Array
Default Value: undefined

NOTE: The sortBySummaryPath option applies together with the sortBySummaryField option. When the value of the sortBySummaryPath option is an empty array or undefined, the header items are sorted by grand total values. The data field providing facts for these values is specified by the sortBySummaryField option.

Show Example:
AngularJS
Knockout
jQuery

In this example, the "Region" items are sorted by the summary values of the "Amount" field. Pay attention to the "Region" field configuration. It has the sortBySummaryField and sortBySummaryPath options specified. You can change the value of the latter option at runtime using the select box below the pivot grid. It will sort the "Region" items by the summary values of another column.

<div ng-controller="pivotGridController">
    <div style="height:280px; max-width:750px; margin:0 auto" dx-pivot-grid="{
        dataSource: pivotGridDataSource
    }"></div>
    <div style="height:40px; margin-top:5px; text-align:center">
        Choose a Column:&nbsp;
        <div style="width:250px; display:inline-block; vertical-align:middle" dx-select-box="{
            dataSource: selectBoxDataSource,
            displayExpr: 'text',
            valueExpr: 'value',
            onValueChanged: changeSummaryPath
        }"></div>
    </div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row',
        sortBySummaryField: 'amount',
        sortBySummaryPath: [2015]
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column'
    }, {
        dataField: "amount",
        summaryType: "sum",
        format: "currency",
        area: "data"
    }]
});

var selectBoxDataSource = [
    { text: '2013', value: [2013] },
    { text: '2014', value: [2014] },
    { text: '2015', value: [2015] },
    { text: 'Grand Total', value: [] }
];

var changeSummaryPath = function (e) {
    pivotGridDataSource.field("region", {
        sortBySummaryPath: e.value
    });
    pivotGridDataSource.load();
};

angular
    .module('myApp', ['dx'])
    .controller('pivotGridController', function ($scope) {
        $scope.pivotGridDataSource = pivotGridDataSource;
        $scope.selectBoxDataSource - selectBoxDataSource;
        $scope.changeSummaryPath = changeSummaryPath;
    });
    
angular.element(document).ready(function () {
   angular.bootstrap(document, ['myApp']);
});

In this example, the "Region" items are sorted by the summary values of the "Amount" field. Pay attention to the "Region" field configuration. It has the sortBySummaryField and sortBySummaryPath options specified. You can change the value of the latter option at runtime using the select box below the pivot grid. It will sort the "Region" items by the summary values of another column.

<div style="height:280px; max-width:750px; margin:0 auto" data-bind="dxPivotGrid: {
    dataSource: pivotGridDataSource
}"></div>
<div style="height:40px; margin-top:5px; text-align:center">
    Choose a Column:&nbsp;
    <div style="width:250px; display:inline-block; vertical-align:middle" data-bind="dxSelectBox: {
        dataSource: selectBoxDataSource,
        displayExpr: 'text',
        valueExpr: 'value',
        onValueChanged: changeSummaryPath
    }"></div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row',
        sortBySummaryField: 'amount',
        sortBySummaryPath: [2015]
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column'
    }, {
        dataField: "amount",
        summaryType: "sum",
        format: "currency",
        area: "data"
    }]
});

var selectBoxDataSource = [
    { text: '2013', value: [2013] },
    { text: '2014', value: [2014] },
    { text: '2015', value: [2015] },
    { text: 'Grand Total', value: [] }
];

var changeSummaryPath = function (e) {
    pivotGridDataSource.field("region", {
        sortBySummaryPath: e.value
    });
    pivotGridDataSource.load();
};

var viewModel = {
    pivotGridDataSource: pivotGridDataSource,
    selectBoxDataSource: selectBoxDataSource,
    changeSummaryPath: changeSummaryPath
};

ko.applyBindings(viewModel);

In this example, the "Region" items are sorted by the summary values of the "Amount" field. Pay attention to the "Region" field configuration. It has the sortBySummaryField and sortBySummaryPath options specified. You can change the value of the latter option at runtime using the select box below the pivot grid. It will sort the "Region" items by the summary values of another column.

<div id="pivotGridContainer" style="height:280px; max-width:750px; margin:0 auto"></div>
<div style="height:40px; margin-top:5px; text-align:center">
    Choose a Column:&nbsp;
    <div id="selectBoxContainer" style="width:250px; display:inline-block; vertical-align:middle"></div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row',
        sortBySummaryField: 'amount',
        sortBySummaryPath: [2015]
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        }
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column'
    }, {
        dataField: "amount",
        summaryType: "sum",
        format: "currency",
        area: "data"
    }]
});

var selectBoxDataSource = [
    { text: '2013', value: [2013] },
    { text: '2014', value: [2014] },
    { text: '2015', value: [2015] },
    { text: 'Grand Total', value: [] }
];

var changeSummaryPath = function (e) {
    pivotGridDataSource.field("region", {
        sortBySummaryPath: e.value
    });
    pivotGridDataSource.load();
};

$(function () {
    $("#pivotGridContainer").dxPivotGrid({
        dataSource: pivotGridDataSource
    });
    
    $("#selectBoxContainer").dxSelectBox({
        dataSource: selectBoxDataSource,
        displayExpr: 'text',
        valueExpr: 'value',
        onValueChanged: changeSummaryPath
    });
});

sortingMethod

Specifies how to sort the header items.

Type: function(a, b)
Function parameters:
a: Object
A header item.
Object structure:
value: String|Number
A header item value.
children: Array
An array of header item's children; available only when the item is expanded.
b: Object
A header item.
Object structure:
value: String|Number
A header item value.
children: Array
An array of header item's children; available only when the item is expanded.
Return Value: Number
Indicates whether a comes before b.
Default Value: undefined

You can order the header items per your requirements by specifying a comparison function. This function accepts two parameters that represent two header items and should return the value on which the order will be based. The value of these header items can be accessed using the value field. You can base your sorting method on the header item's children that are available in the children field.

The function returns a number that has one of three values listed below.

  • Less than zero
    The first function parameter comes before the second in the resulting order

  • Zero
    The first and the second parameters remain unchanged relative to each other

  • Greater than zero
    The second function parameter comes before the first in the resulting order

sortOrder

Specifies the sort order of field values.

Type: String
Default Value: 'asc'
Accepted Values: 'asc' | 'desc'

summaryDisplayMode

Specifies the summary post-processing algorithm.

Type: String
Default Value: undefined
Accepted Values: 'absoluteVariation' | 'percentVariation' | 'percentOfColumnTotal' | 'percentOfRowTotal' | 'percentOfColumnGrandTotal' | 'percentOfRowGrandTotal' | 'percentOfGrandTotal'

This option allows you to set up additional calculations on summary values (calculated by aggregation of facts according to the summaryType). This allows you to take into account the neighboring summary values or total values. You can use one of the predefined post-processing functions (display modes) or implement your own one using the calculateSummaryValue option. The default summary display modes are listed below.

NOTE: All post-processing functions are designed to iterate through pivot grid summary values. The current value concept refers to a summary value at any iteration. In other words, the current value is any summary value for which the post-processing function can be applied.

  • "absoluteVariation"
    Calculates the absolute difference between the current and the previous row value. The processing starts from the second column because the first value does not have the previous one.

  • "percentVariation"
    Calculates the relative difference between the current and the previous row value assuming the previous value is 100%. If the current value is lower than the previous one, the percent variation will be negative. The processing starts from the second column because the first value does not have the previous one.

  • "percentOfColumnTotal"
    Calculates the relative difference between the current value and the parental total assuming the latter is 100%. Note that the 100% value is displayed only for Grand Totals because they have no parental total row and the percentage cannot be calculated. All intermediate total rows will display the percentage values calculated with respect to their parental total rows, whereas for each inner summary row parental total values will be assumed to be 100% internally.

  • "percentOfRowTotal"
    Calculates the relative difference between the current value and the parental total assuming the latter is 100%. Note that the 100% value is displayed only for Grand Totals because they have no parental total column and the percentage cannot be calculated. All intermediate total columns will display the percentage values calculated with respect to their parental total columns, whereas for each inner summary column parental total values will be assumed to be 100% internally.

  • "percentOfColumnGrandTotal"
    Calculates the relative difference between the current value and the corresponding grand total row value assuming the latter is 100%.

  • "percentOfRowGrandTotal"
    Calculates the relative difference between the current value and the corresponding grand total column value assuming the latter is 100%.

  • "percentOfGrandTotal"
    Calculates the relative difference between the current value and the grand total value of the entire pivot grid assuming the latter is 100%.

You can allow the user to change the summary display mode at runtime. Learn how to implement this capability from the Runtime Summary Type Selection topic.

Show Example:
AngularJS
Knockout
jQuery

In this example, the pivot grid contains two data fields with an identical dataField value. The "Sales Total" field displays the data field values and the "Var" field displays the result of post-processing those values. Right-click the "Var" field on the field panel and select an item from the appeared context menu to switch the post-processing algorithm (summary display mode).

<div ng-controller="pivotGridController">
    <div style="height:365px; max-width:750px; margin:0 auto" dx-pivot-grid="pivotGridOptions"></div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        caption: "Region",
        width: 120,
        dataField: "region",
        area: "row"
    }, {
        caption: "City",
        dataField: "city",
        width: 100,
        area: "row"
    }, {
        dataField: "date",
        dataType: "date",
        area: "column"
    }, {
        caption: "Sales Total",
        dataField: "amount",
        dataType: "number",
        summaryType: "sum",
        format: "currency",
        area: "data"
    }, {
        caption: "Var",
        dataField: "amount",
        dataType: "number",
        summaryDisplayMode: 'absoluteVariation',
        area: "data"
    }]
});

angular
    .module('myApp', ['dx'])
    .controller('pivotGridController', function ($scope) {
        $scope.pivotGridDataSource = pivotGridDataSource;
        $scope.pivotGridOptions = {
            dataSource: $scope.pivotGridDataSource,
            fieldChooser: { enabled: false },
            fieldPanel: {
                visible: true,
                allowFieldDragging: false,
                showFilterFields: false
            },
            onContextMenuPreparing: function (e) {
                if (e.field.caption == "Var" ) {
                    var dataSource = e.component.getDataSource();

                    var changeSummaryDisplayMode = function (clickedItem) {
                        dataSource.field(e.field.index, {
                            summaryDisplayMode: clickedItem.itemData.value
                        });
                        dataSource.load();
                    };

                    var items = [
                        { text: 'Absolute Variation', value: 'absoluteVariation', onItemClick: changeSummaryDisplayMode },
                        { text: 'Percent Variation', value: 'percentVariation', onItemClick: changeSummaryDisplayMode },
                        { text: 'Percent of Column Total', value: 'percentOfColumnTotal', onItemClick: changeSummaryDisplayMode },
                        { text: 'Percent of Row Total', value: 'percentOfRowTotal', onItemClick: changeSummaryDisplayMode },
                        { text: 'Percent of Column Grand Total', value: 'percentOfColumnGrandTotal', onItemClick: changeSummaryDisplayMode },
                        { text: 'Percent of Row Grand Total', value: 'percentOfRowGrandTotal', onItemClick: changeSummaryDisplayMode },
                        { text: 'Percent of Grand Total', value: 'percentOfGrandTotal', onItemClick: changeSummaryDisplayMode }
                    ];

                    $.each(items, function (_, item) {
                        if (item.value == dataSource.field(e.field.index).summaryDisplayMode)
                            item.selected = true;
                    });

                    Array.prototype.push.apply(e.items, items);
                }
            }
        }
    });
    
angular.element(document).ready(function () {
   angular.bootstrap(document, ['myApp']);
});

In this example, the pivot grid contains two data fields with an identical dataField value. The "Sales Total" field displays the data field values and the "Var" field displays the result of post-processing those values. Right-click the "Var" field on the field panel and select an item from the appeared context menu to switch the post-processing algorithm (summary display mode).

<div style="height:365px; max-width:750px; margin:0 auto" data-bind="dxPivotGrid: pivotGridOptions"></div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        caption: "Region",
        width: 120,
        dataField: "region",
        area: "row"
    }, {
        caption: "City",
        dataField: "city",
        width: 100,
        area: "row"
    }, {
        dataField: "date",
        dataType: "date",
        area: "column"
    }, {
        caption: "Sales Total",
        dataField: "amount",
        dataType: "number",
        summaryType: "sum",
        format: "currency",
        area: "data"
    }, {
        caption: "Var",
        dataField: "amount",
        dataType: "number",
        summaryDisplayMode: 'absoluteVariation',
        area: "data"
    }]
});

var viewModel = {
    pivotGridOptions: {
        dataSource: pivotGridDataSource,
        fieldChooser: { enabled: false },
        fieldPanel: {
            visible: true,
            allowFieldDragging: false,
            showFilterFields: false
        },
        onContextMenuPreparing: function (e) {
            if (e.field.caption == "Var" ) {
                var dataSource = e.component.getDataSource();

                var changeSummaryDisplayMode = function (clickedItem) {
                    dataSource.field(e.field.index, {
                        summaryDisplayMode: clickedItem.itemData.value
                    });
                    dataSource.load();
                };

                var items = [
                    { text: 'Absolute Variation', value: 'absoluteVariation', onItemClick: changeSummaryDisplayMode },
                    { text: 'Percent Variation', value: 'percentVariation', onItemClick: changeSummaryDisplayMode },
                    { text: 'Percent of Column Total', value: 'percentOfColumnTotal', onItemClick: changeSummaryDisplayMode },
                    { text: 'Percent of Row Total', value: 'percentOfRowTotal', onItemClick: changeSummaryDisplayMode },
                    { text: 'Percent of Column Grand Total', value: 'percentOfColumnGrandTotal', onItemClick: changeSummaryDisplayMode },
                    { text: 'Percent of Row Grand Total', value: 'percentOfRowGrandTotal', onItemClick: changeSummaryDisplayMode },
                    { text: 'Percent of Grand Total', value: 'percentOfGrandTotal', onItemClick: changeSummaryDisplayMode }
                ];

                $.each(items, function (_, item) {
                    if (item.value == dataSource.field(e.field.index).summaryDisplayMode)
                        item.selected = true;
                });

                Array.prototype.push.apply(e.items, items);
            }
        }
    }
};

ko.applyBindings(viewModel);

In this example, the pivot grid contains two data fields with an identical dataField value. The "Sales Total" field displays the data field values and the "Var" field displays the result of post-processing those values. Right-click the "Var" field on the field panel and select an item from the appeared context menu to switch the post-processing algorithm (summary display mode).

<div id="pivotGridContainer" style="height:365px; max-width:750px; margin:0 auto"></div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        caption: "Region",
        width: 120,
        dataField: "region",
        area: "row"
    }, {
        caption: "City",
        dataField: "city",
        width: 100,
        area: "row"
    }, {
        dataField: "date",
        dataType: "date",
        area: "column"
    }, {
        caption: "Sales Total",
        dataField: "amount",
        dataType: "number",
        summaryType: "sum",
        format: "currency",
        area: "data"
    }, {
        caption: "Var",
        dataField: "amount",
        dataType: "number",
        summaryDisplayMode: 'absoluteVariation',
        area: "data"
    }]
});

$(function () {
    $('#pivotGridContainer').dxPivotGrid({
        dataSource: pivotGridDataSource,
        fieldChooser: { enabled: false },
        fieldPanel: {
            visible: true,
            allowFieldDragging: false,
            showFilterFields: false
        },
        onContextMenuPreparing: function (e) {
            if (e.field.caption == "Var" ) {
                var dataSource = e.component.getDataSource();

                var changeSummaryDisplayMode = function (clickedItem) {
                    dataSource.field(e.field.index, {
                        summaryDisplayMode: clickedItem.itemData.value
                    });
                    dataSource.load();
                };

                var items = [
                    { text: 'Absolute Variation', value: 'absoluteVariation', onItemClick: changeSummaryDisplayMode },
                    { text: 'Percent Variation', value: 'percentVariation', onItemClick: changeSummaryDisplayMode },
                    { text: 'Percent of Column Total', value: 'percentOfColumnTotal', onItemClick: changeSummaryDisplayMode },
                    { text: 'Percent of Row Total', value: 'percentOfRowTotal', onItemClick: changeSummaryDisplayMode },
                    { text: 'Percent of Column Grand Total', value: 'percentOfColumnGrandTotal', onItemClick: changeSummaryDisplayMode },
                    { text: 'Percent of Row Grand Total', value: 'percentOfRowGrandTotal', onItemClick: changeSummaryDisplayMode },
                    { text: 'Percent of Grand Total', value: 'percentOfGrandTotal', onItemClick: changeSummaryDisplayMode }
                ];

                $.each(items, function (_, item) {
                    if (item.value == dataSource.field(e.field.index).summaryDisplayMode)
                        item.selected = true;
                });

                Array.prototype.push.apply(e.items, items);
            }
        }
    });
});

summaryType

Specifies how to aggregate field data. Cannot be used for the XmlaStore store type.

Type: String
Default Value: 'count'
Accepted Values: 'sum' | 'min' | 'max' | 'avg' | 'count' | 'custom'

The following list gives an overview of available summary types.

  • "sum"
    Sums up all facts within an area.
  • "min"
    Calculates the minimum fact's value.
  • "max"
    Calculates the maximum fact's value.
  • "avg"
    Calculates the average of all facts within an area.
  • "count"
    Calculates the number of facts within an area.
  • "custom"
    Allows you to specify a custom aggregate function using the calculateCustomSummary option.

You can allow the user to change the summary type at runtime. Learn how to implement this capability from the Runtime Summary Type Selection topic.

Show Example:
AngularJS
Knockout
jQuery

In this example, you can select the summary type of the "Sales" data field at runtime. Right-click this data field on the field panel and choose the required summary type. Note that along with the summary type, the data field's caption and the format of summary values change.

<div ng-controller="pivotGridController">
    <div style="height:365px; max-width:750px; margin:0 auto" dx-pivot-grid="pivotGridOptions"></div>
</div>
angular
    .module('myApp', ['dx'])
    .controller('pivotGridController', function ($scope) {
        $scope.sales = sales;
        $scope.pivotGridOptions = {
            dataSource: {
                store: $scope.sales,
                fields: [{
                    caption: "Region",
                    width: 120,
                    dataField: "region",
                    area: "row" 
                }, {
                    caption: "City",
                    dataField: "city",
                    width: 100,
                    area: "row"
                }, {
                    dataField: "date",
                    dataType: "date",
                    area: "column"
                }, {
                    caption: "Sales (Sum)",
                    dataField: "amount",
                    dataType: "number",
                    summaryType: "sum",
                    format: "currency",
                    area: "data"
                }]
            },
            fieldChooser: { enabled: false },
            fieldPanel: {
                visible: true,
                allowFieldDragging: false,
                showFilterFields: false
            },
            onContextMenuPreparing: function (e) {
                if (e.field && e.field.area == "data") {
                    var dataSource = e.component.getDataSource();
                    
                    var changeSummaryType = function (clickedItem) {
                        dataSource.field(e.field.index, {
                            caption: "Sales (" + clickedItem.itemData.text + ")",
                            format: clickedItem.itemData.value == 'count' ? undefined : 'currency',
                            summaryType: clickedItem.itemData.value
                        });
                        dataSource.load();
                    };
                    
                    var items = [
                        { text: 'Sum', value: 'sum', onItemClick: changeSummaryType },
                        { text: 'Avg', value: 'avg', onItemClick: changeSummaryType },
                        { text: 'Min', value: 'min', onItemClick: changeSummaryType },
                        { text: 'Max', value: 'max', onItemClick: changeSummaryType },
                        { text: 'Count', value: 'count', onItemClick: changeSummaryType }
                    ];
                    
                    $.each(items, function (_, item) {
                        if (item.value == dataSource.field(e.field.index).summaryType)
                            item.selected = true;
                    });
                    
                    Array.prototype.push.apply(e.items, items);
                }
            }
        };
    });

angular.element(document).ready(function () {
   angular.bootstrap(document, ['myApp']);
});

In this example, you can select the summary type of the "Sales" data field at runtime. Right-click this data field on the field panel and choose the required summary type. Note that along with the summary type, the data field's caption and the format of summary values change.

<div style="height:365px; max-width:750px; margin:0 auto" data-bind="dxPivotGrid: pivotGridOptions"></div>
var viewModel = {
    pivotGridOptions: {
        dataSource: {
            store: sales,
            fields: [{
                caption: "Region",
                width: 120,
                dataField: "region",
                area: "row" 
            }, {
                caption: "City",
                dataField: "city",
                width: 100,
                area: "row"
            }, {
                dataField: "date",
                dataType: "date",
                area: "column"
            }, {
                caption: "Sales (Sum)",
                dataField: "amount",
                dataType: "number",
                summaryType: "sum",
                format: "currency",
                area: "data"
            }]
        },
        fieldChooser: { enabled: false },
        fieldPanel: {
            visible: true,
            allowFieldDragging: false,
            showFilterFields: false
        },
        onContextMenuPreparing: function (e) {
            if (e.field && e.field.area == "data") {
                var dataSource = e.component.getDataSource();
                
                var changeSummaryType = function (clickedItem) {
                    dataSource.field(e.field.index, {
                        caption: "Sales (" + clickedItem.itemData.text + ")",
                        format: clickedItem.itemData.value == 'count' ? undefined : 'currency',
                        summaryType: clickedItem.itemData.value
                    });
                    dataSource.load();
                };
                
                var items = [
                    { text: 'Sum', value: 'sum', onItemClick: changeSummaryType },
                    { text: 'Avg', value: 'avg', onItemClick: changeSummaryType },
                    { text: 'Min', value: 'min', onItemClick: changeSummaryType },
                    { text: 'Max', value: 'max', onItemClick: changeSummaryType },
                    { text: 'Count', value: 'count', onItemClick: changeSummaryType }
                ];
                
                $.each(items, function (_, item) {
                    if (item.value == dataSource.field(e.field.index).summaryType)
                        item.selected = true;
                });
                
                Array.prototype.push.apply(e.items, items);
            }
        }
    }
};

ko.applyBindings(viewModel);

In this example, you can select the summary type of the "Sales" data field at runtime. Right-click this data field on the field panel and choose the required summary type. Note that along with the summary type, the data field's caption and the format of summary values change.

<div id="pivotGridContainer" style="height:365px; max-width:750px; margin:0 auto"></div>
$(function () {
    $("#pivotGridContainer").dxPivotGrid({
        dataSource: {
            store: sales,
            fields: [{
                caption: "Region",
                width: 120,
                dataField: "region",
                area: "row" 
            }, {
                caption: "City",
                dataField: "city",
                width: 100,
                area: "row"
            }, {
                dataField: "date",
                dataType: "date",
                area: "column"
            }, {
                caption: "Sales (Sum)",
                dataField: "amount",
                dataType: "number",
                summaryType: "sum",
                format: "currency",
                area: "data"
            }]
        },
        fieldChooser: { enabled: false },
        fieldPanel: {
            visible: true,
            allowFieldDragging: false,
            showFilterFields: false
        },
        onContextMenuPreparing: function (e) {
            if (e.field && e.field.area == "data") {
                var dataSource = e.component.getDataSource();
                
                var changeSummaryType = function (clickedItem) {
                    dataSource.field(e.field.index, {
                        caption: "Sales (" + clickedItem.itemData.text + ")",
                        format: clickedItem.itemData.value == 'count' ? undefined : 'currency',
                        summaryType: clickedItem.itemData.value
                    });
                    dataSource.load();
                };
                
                var items = [
                    { text: 'Sum', value: 'sum', onItemClick: changeSummaryType },
                    { text: 'Avg', value: 'avg', onItemClick: changeSummaryType },
                    { text: 'Min', value: 'min', onItemClick: changeSummaryType },
                    { text: 'Max', value: 'max', onItemClick: changeSummaryType },
                    { text: 'Count', value: 'count', onItemClick: changeSummaryType }
                ];
                
                $.each(items, function (_, item) {
                    if (item.value == dataSource.field(e.field.index).summaryType)
                        item.selected = true;
                });
                
                Array.prototype.push.apply(e.items, items);
            }
        }
    });
});

visible

A boolean value specifying whether or not the field is visible in the pivot grid and the Field Chooser.

Type: Boolean
Default Value: true

Show Example:
AngularJS
Knockout
jQuery

In this example, you can change the visibility of the "City" field at runtime using the switch below the grid. Note that the field appears/disappears not only in the grid, but in the field chooser as well.

<div ng-controller="pivotGridController">
    <div style="height:290px; max-width:850px; margin:0 auto" dx-pivot-grid="{
        dataSource: dataSource,
        fieldChooser: { enabled: false }
    }"></div>
    <div style="height:400px; width:850px; margin-top:5px; margin:0 auto">
        <div style="width:400px; display:inline-block" dx-pivot-grid-field-chooser="{
            dataSource: dataSource
        }"></div>
        <div style="margin-top:40px; margin-left:100px; display:inline-block; vertical-align:top">
            Show the "City" field:&nbsp;
            <div style="vertical-align:middle" dx-switch="{
                onText: 'true',
                offText: 'false',
                value: true,
                onValueChanged: showCityField
            }"></div>
        </div>
    </div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row'
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        },
        visible: true
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column'
    }, {
        dataField: 'amount',
        summaryType: 'sum',
        format: 'currency',
        area: 'data'
    }]
});

var showCityField = function (e) {
    pivotGridDataSource.field('city', {
        visible: e.value
    });
    pivotGridDataSource.load();
};

angular
    .module('myApp', ['dx'])
    .controller('pivotGridController', function ($scope) {
        $scope.dataSource = pivotGridDataSource;
        $scope.showCityField = showCityField;
    });
    
angular.element(document).ready(function () {
   angular.bootstrap(document, ['myApp']);
});

In this example, you can change the visibility of the "City" field at runtime using the switch below the grid. Note that the field appears/disappears not only in the grid, but in the field chooser as well.

<div style="height:290px; max-width:850px; margin:0 auto" data-bind="dxPivotGrid: {
    dataSource: dataSource,
    fieldChooser: { enabled: false }
}"></div>
<div style="height:400px; width:850px; margin-top:5px; margin:0 auto">
    <div style="width:400px; display:inline-block" data-bind="dxPivotGridFieldChooser: {
        dataSource: dataSource
    }"></div>
    <div style="margin-top:40px; margin-left:100px; display:inline-block; vertical-align:top">
        Show the "City" field:&nbsp;
        <div style="vertical-align:middle" data-bind="dxSwitch: {
            onText: 'true',
            offText: 'false',
            value: true,
            onValueChanged: showCityField
        }"></div>
    </div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row'
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        },
        visible: true
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column'
    }, {
        dataField: 'amount',
        summaryType: 'sum',
        format: 'currency',
        area: 'data'
    }]
});

var showCityField = function (e) {
    pivotGridDataSource.field('city', {
        visible: e.value
    });
    pivotGridDataSource.load();
};

var viewModel = {
    dataSource: pivotGridDataSource,
    showCityField: showCityField
};

ko.applyBindings(viewModel);

In this example, you can change the visibility of the "City" field at runtime using the switch below the grid. Note that the field appears/disappears not only in the grid, but in the field chooser as well.

<div id="pivotGridContainer" style="height:290px; max-width:850px; margin:0 auto"></div>
<div style="height:400px; width:850px; margin-top:5px; margin:0 auto">
    <div id="fieldChooserContainer" style="width:400px; display:inline-block"></div>
    <div style="margin-top:40px; margin-left:100px; display:inline-block; vertical-align:top">
        Show the "City" field:&nbsp;
        <div id="switchContainer" style="vertical-align:middle"></div>
    </div>
</div>
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    store: sales,
    fields: [{
        dataField: 'region',
        width: 120,
        area: 'row'
    }, {
        dataField: 'city',
        width: 150,
        area: 'row',
        selector: function (data) {
            return data.city + ' (' + data.country + ')';
        },
        visible: true
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column'
    }, {
        dataField: 'amount',
        summaryType: 'sum',
        format: 'currency',
        area: 'data'
    }]
});

var showCityField = function (e) {
    pivotGridDataSource.field('city', {
        visible: e.value
    });
    pivotGridDataSource.load();
};

$(function () {
    $("#pivotGridContainer").dxPivotGrid({
        dataSource: pivotGridDataSource,
        fieldChooser: { enabled: false }
    });
    
    $("#fieldChooserContainer").dxPivotGridFieldChooser({
        dataSource: pivotGridDataSource
    });
    
    $("#switchContainer").dxSwitch({
        onText: 'true',
        offText: 'false',
        value: true,
        onValueChanged: showCityField
    });
});

width

Specifies the absolute width of the field in the pivot grid.

Type: Number
Default Value: undefined

wordWrapEnabled

Specifies whether or not long text in header items should be wrapped.

Type: Boolean
Default Value: undefined
See Also
  • PivotGrid | wordWrapEnabled - applies word wrap to all fields in PivotGrid.