A newer version of this page is available. Switch to the current version.

DevExtreme jQuery - PivotGridDataSource - fields

Configures pivot grid fields.

Type:

Array<Object>

Default Value: undefined

This property accepts an array of objects where each object configures a single field. Each pivot grid field must be associated with a data field using the dataField property. Fields with the specified area property are displayed in the pivot grid; other fields' headers are displayed in the field chooser.

jQuery
JavaScript
$(function() {
    var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
        // ...
        fields: [{
            dataField: "region",
            area: "row"
        }, {
            dataField: "date",
            dataType: "date",
            area: "column"
        }, {
            dataField: "sales",
            summaryType: "sum",
            area: "data"
        }]
    });

    $("#pivotGridContainer").dxPivotGrid({
        dataSource: pivotGridDataSource
    });
});
Angular
TypeScript
HTML
import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source";
import { DxPivotGridModule } from "devextreme-angular";
// ...
export class AppComponent {
    pivotGridDataSource: PivotGridDataSource;
    constructor() {
        this.pivotGridDataSource = new PivotGridDataSource({
            // ...
            fields: [{
                dataField: "region",
                area: "row"
            }, {
                dataField: "date",
                dataType: "date",
                area: "column"
            }, {
                dataField: "sales",
                summaryType: "sum",
                area: "data"
            }]
        });
    }
}

@NgModule({
    imports: [
        // ...
        DxPivotGridModule
    ],
    // ...
})
<dx-pivot-grid
    [dataSource]="pivotGridDataSource">
</dx-pivot-grid>
Vue
App.vue
<template>
    <DxPivotGrid
        :data-source="pivotGridDataSource"
    />
</template>

<script>
import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source';
import DxPivotGrid from 'devextreme-vue/pivot-grid';

const pivotGridDataSource = new PivotGridDataSource({
    // ...
    fields: [{
        dataField: 'region',
        area: 'row'
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column'
    }, {
        dataField: 'sales',
        summaryType: 'sum',
        area: 'data'
    }]
});

export default {
    components: {
        DxPivotGrid
    },
    data() {
        return {
            pivotGridDataSource
        }
    }
}
</script>
React
App.js
import React from 'react';

import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source';
import DxPivotGrid from 'devextreme-react/pivot-grid';

const pivotGridDataSource = new PivotGridDataSource({
    // ...
    fields: [{
        dataField: 'region',
        area: 'row'
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column'
    }, {
        dataField: 'sales',
        summaryType: 'sum',
        area: 'data'
    }]
});

class App extends React.Component {
    render() {
        return (
            <PivotGrid
                dataSource={pivotGridDataSource}
            />
        );
    }
}
export default App;

If the retrieveFields property is true, fields configured in the fields array are accompanied by auto-generated fields that do not belong to any area. However, a user can move them to any area using the field chooser.

See Also

allowCrossGroupCalculation

Specifies whether to take neighboring groups' summary values into account when calculating a running total and absolute or percent variation.

Type:

Boolean

Default Value: false

For example, a pivot grid row contains the following summary values split into three groups:

[ [1, 1, 1], [2, 2, 2], [3, 3, 3] ]

This row can be displayed differently depending on the allowCrossGroupCalculation property's value, provided that the running total is calculated by rows:

[ [1, 2, 3], [2, 4, 6], [3, 6, 9] ]    // allowCrossGroupCalculation: false
[ [1, 2, 3], [5, 7, 9], [12, 15, 18] ] // allowCrossGroupCalculation: true

View Demo

allowExpandAll

Specifies whether a user can expand/collapse all items within the same column or row header level using the context menu.

Type:

Boolean

Default Value: false

allowFiltering

Specifies whether a user can filter the field's values.

Type:

Boolean

Default Value: false

When this property is set to true, the field's header in the field chooser and on the field panel is supplied with a filter icon. Clicking it invokes the header filter displaying all unique field values. A user applies filtering by selecting or clearing the selection of the values.

View Demo

See Also

allowSorting

Specifies whether a user can change the field's sorting.

Type:

Boolean

Default Value: false

When this property is set to true, clicking the field's header in the field chooser and on the field panel changes the sorting direction. An arrow icon in the header indicates the current sorting direction.

View Demo

See Also

allowSortingBySummary

Specifies whether a user can sort the pivot grid by summary values instead of field values.

Type:

Boolean

Default Value: false

When this property is set to true, a user can use a column header's or row header's context menu to sort the pivot grid by this column's or row's summary values.

See Also

area

Specifies the field's area.

Type:

String

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

The field does not belong to any area if this property is undefined. However, the field's header is displayed in the field chooser, which enables the user to move this field to any area. In other cases, the field is displayed in the specified area.

Use the PivotGridArea enum to specify this property when the UI component is used as an ASP.NET MVC 5 Control or a DevExtreme-Based ASP.NET Core Control. This enum accepts the following values: Row, Column, Data, and Filter.

See Also

areaIndex

Specifies the field's order among the other fields in the same area. Corresponds to the field's order in the fields array by default.

Type:

Number

Default Value: undefined

jQuery
JavaScript
$(function() {
    var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
        // ...
        fields: [{
            dataField: "city",
            area: "row",
            areaIndex: 1
        }, {
            dataField: "country",
            area: "row",
            areaIndex: 0 // Countries nest cities
        },
        // ...
        ]
    });

    $("#pivotGridContainer").dxPivotGrid({
        dataSource: pivotGridDataSource
    });
});
Angular
TypeScript
HTML
import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source";
import { DxPivotGridModule } from "devextreme-angular";
// ...
export class AppComponent {
    pivotGridDataSource: PivotGridDataSource;
    constructor() {
        this.pivotGridDataSource = new PivotGridDataSource({
            // ...
            fields: [{
                dataField: "city",
                area: "row",
                areaIndex: 1
            }, {
                dataField: "country",
                area: "row",
                areaIndex: 0 // Countries nest cities
            },
            // ...
            ]
        });
    }
}

@NgModule({
    imports: [
        // ...
        DxPivotGridModule
    ],
    // ...
})
<dx-pivot-grid
    [dataSource]="pivotGridDataSource">
</dx-pivot-grid>
Vue
App.vue
<template>
    <DxPivotGrid
        :data-source="pivotGridDataSource"
    />
</template>

<script>
import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source';
import DxPivotGrid from 'devextreme-vue/pivot-grid';

const pivotGridDataSource = new PivotGridDataSource({
    // ...
    fields: [{
        dataField: 'city',
        area: 'row',
        areaIndex: 1
    }, {
        dataField: 'country',
        area: 'row',
        areaIndex: 0 // Countries nest cities
    },
    // ...
    ]
});

export default {
    components: {
        DxPivotGrid
    },
    data() {
        return {
            pivotGridDataSource
        }
    }
}
</script>
React
App.js
import React from 'react';

import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source';
import DxPivotGrid from 'devextreme-react/pivot-grid';

const pivotGridDataSource = new PivotGridDataSource({
    // ...
    fields: [{
        dataField: 'city',
        area: 'row',
        areaIndex: 1
    }, {
        dataField: 'country',
        area: 'row',
        areaIndex: 0 // Countries nest cities
    },
    // ...
    ]
});

class App extends React.Component {
    render() {
        return (
            <PivotGrid
                dataSource={pivotGridDataSource}
            />
        );
    }
}
export default App;
NOTE
The field's order affects the headers's hierarchy structure and is not related to the sorting concept.

calculateCustomSummary

Specifies a custom aggregate function. Applies only if the summaryType is "custom" and the remoteOperations is false. Cannot be used with an XmlaStore.

Type:

Function

Function parameters:
options:

Object

Information on the summary.

Object structure:
Name Type Description
summaryProcess

String

The summary value calculation stage: "start", "calculate", or "finalize".

totalValue any

The total summary value.

value any

A value to be aggregated. If you need to access an entire data object in this field, do not specify the dataField property.

A summary value calculation is conducted in three stages: start - in which the totalValue is initialized, calculate - in which it is modified, and finalize - in which the totalValue is adjusted. To identify the current stage, check the value of the summaryProcess field that belongs to the function's parameter.

jQuery
JavaScript
$(function() {
    var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
        // ...
        fields: [{
            // ...
            summaryType: "custom",
            calculateCustomSummary: function(options) {
                switch(options.summaryProcess) {
                    case "start":
                        // Initializing "totalValue" here
                        break;
                    case "calculate":
                        // Modifying "totalValue" here
                        break;
                    case "finalize":
                        // Assigning the final value to "totalValue" here
                        break;
                }
            }
        }]
    });

    $("#pivotGridContainer").dxPivotGrid({
        dataSource: pivotGridDataSource
    });
});
Angular
TypeScript
HTML
import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source";
import { DxPivotGridModule } from "devextreme-angular";
// ...
export class AppComponent {
    pivotGridDataSource: PivotGridDataSource;
    constructor() {
        this.pivotGridDataSource = new PivotGridDataSource({
            // ...
            fields: [{
                // ...
                summaryType: "custom",
                calculateCustomSummary: (options) => {
                    switch(options.summaryProcess) {
                        case "start":
                            // Initializing "totalValue" here
                            break;
                        case "calculate":
                            // Modifying "totalValue" here
                            break;
                        case "finalize":
                            // Assigning the final value to "totalValue" here
                            break;
                    }
                }
            }]
        });
    }
}

@NgModule({
    imports: [
        // ...
        DxPivotGridModule
    ],
    // ...
})
<dx-pivot-grid
    [dataSource]="pivotGridDataSource">
</dx-pivot-grid>
Vue
App.vue
<template>
    <DxPivotGrid
        :data-source="pivotGridDataSource"
    />
</template>

<script>
import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source';
import DxPivotGrid from 'devextreme-vue/pivot-grid';

const pivotGridDataSource = new PivotGridDataSource({
    // ...
    fields: [{
        // ...
        summaryType: 'custom',
        calculateCustomSummary: (options) => {
            switch(options.summaryProcess) {
                case 'start':
                    // Initializing "totalValue" here
                    break;
                case 'calculate':
                    // Modifying "totalValue" here
                    break;
                case 'finalize':
                    // Assigning the final value to "totalValue" here
                    break;
            }
        }
    }]
});

export default {
    components: {
        DxPivotGrid
    },
    data() {
        return {
            pivotGridDataSource
        }
    }
}
</script>
React
App.js
import React from 'react';

import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source';
import DxPivotGrid from 'devextreme-react/pivot-grid';

const pivotGridDataSource = new PivotGridDataSource({
    // ...
    fields: [{
        // ...
        summaryType: 'custom',
        calculateCustomSummary: (options) => {
            switch(options.summaryProcess) {
                case 'start':
                    // Initializing "totalValue" here
                    break;
                case 'calculate':
                    // Modifying "totalValue" here
                    break;
                case 'finalize':
                    // Assigning the final value to "totalValue" here
                    break;
            }
        }
    }]
});

class App extends React.Component {
    render() {
        return (
            <PivotGrid
                dataSource={pivotGridDataSource}
            />
        );
    }
}
export default App;
ASP.NET MVC Controls
Razor C#
@(Html.DevExtreme().PivotGrid()
    .DataSource(ds => ds
        // ...
        .Fields(fields => {
            fields.Add()
                // ...
                .SummaryType(SummaryType.Custom)
                .CalculateCustomSummary("calculateCustomSummary");
        })
    )
)

<script type="text/javascript">
    function calculateCustomSummary(options) {
        switch(options.summaryProcess) {
            case "start":
                // Initializing "totalValue" here
                break;
            case "calculate":
                // Modifying "totalValue" here
                break;
            case "finalize":
                // Assigning the final value to "totalValue" here
                break;
        }
    }
</script>
NOTE
The this keyword refers to the field's configuration.

calculateSummaryValue

Specifies a custom post-processing function for summary values.

Type:

Function

Function parameters:

The cell whose summary value is being processed.

Return Value:

Number

The cell's new summary value.

Default Value: undefined

This and other post-processing functions allow you to perform additional calculations on each summary value and take into account neighboring cells' summary values.

On the following image, pivot grid cells display indexes that show the order in which the calculateSummaryValue function is called. This order stays the same even if the layout is changed, for example, if rows or columns are sorted differently or the total columns are relocated.

DevExtreme PivotGrid: Summary calculation order

jQuery
JavaScript
$(function() {
    var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
        // ...
        fields: [{
            // ...
            calculateSummaryValue: function (summaryCell) {
                // Your code goes here
            }
        }]
    });

    $("#pivotGridContainer").dxPivotGrid({
        dataSource: pivotGridDataSource
    });
});
Angular
TypeScript
HTML
import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source";
import { DxPivotGridModule } from "devextreme-angular";
// ...
export class AppComponent {
    pivotGridDataSource: PivotGridDataSource;
    constructor() {
        this.pivotGridDataSource = new PivotGridDataSource({
            // ...
            fields: [{
                // ...
                calculateSummaryValue: function (summaryCell) {
                    // Your code goes here
                }
            }]
        });
    }
}

@NgModule({
    imports: [
        // ...
        DxPivotGridModule
    ],
    // ...
})
<dx-pivot-grid
    [dataSource]="pivotGridDataSource">
</dx-pivot-grid>
Vue
App.vue
<template>
    <DxPivotGrid
        :data-source="pivotGridDataSource"
    />
</template>

<script>
import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source';
import DxPivotGrid from 'devextreme-vue/pivot-grid';

const pivotGridDataSource = new PivotGridDataSource({
    // ...
    fields: [{
        // ...
        calculateSummaryValue: function (summaryCell) {
            // Your code goes here
        }
    }]
});

export default {
    components: {
        DxPivotGrid
    },
    data() {
        return {
            pivotGridDataSource
        }
    }
}
</script>
React
App.js
import React from 'react';

import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source';
import DxPivotGrid from 'devextreme-react/pivot-grid';

const pivotGridDataSource = new PivotGridDataSource({
    // ...
    fields: [{
        // ...
        calculateSummaryValue: function (summaryCell) {
            // Your code goes here
        }
    }]
});

class App extends React.Component {
    render() {
        return (
            <PivotGrid
                dataSource={pivotGridDataSource}
            />
        );
    }
}
export default App;
ASP.NET MVC Controls
Razor C#
@(Html.DevExtreme().PivotGrid()
    .DataSource(ds => ds
        // ...
        .Fields(fields => {
            fields.Add()
                // ...
                .CalculateSummaryValue("calculateSummaryValue");
        })
    )
)

<script type="text/javascript">
    function calculateSummaryValue (summaryCell) {
        // Your code goes here
    }
</script>
NOTE
The this keyword refers to the field's configuration.

caption

Specifies the field's caption to be displayed in the field chooser and on the field panel.

Type:

String

Default Value: undefined

customizeText

Customizes the text displayed in summary cells.

Type:

Function

Function parameters:
cellInfo:

Object

Information on the current cell.

Object structure:
Name Type Description
value

String

|

Number

|

Date

The cell's raw value.

valueText

String

The formatted value converted to a string.

Return Value:

String

The text for the cell to display.

jQuery
JavaScript
$(function() {
    var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
        // ...
        fields: [{
            // ...
            customizeText: function (cellInfo) {
                // Your code goes here
            }
        }]
    });

    $("#pivotGridContainer").dxPivotGrid({
        dataSource: pivotGridDataSource
    });
});
Angular
TypeScript
HTML
import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source";
import { DxPivotGridModule } from "devextreme-angular";
// ...
export class AppComponent {
    pivotGridDataSource: PivotGridDataSource;
    constructor() {
        this.pivotGridDataSource = new PivotGridDataSource({
            // ...
            fields: [{
                // ...
                customizeText: function (cellInfo) {
                    // Your code goes here
                }
            }]
        });
    }
}

@NgModule({
    imports: [
        // ...
        DxPivotGridModule
    ],
    // ...
})
<dx-pivot-grid
    [dataSource]="pivotGridDataSource">
</dx-pivot-grid>
Vue
App.vue
<template>
    <DxPivotGrid
        :data-source="pivotGridDataSource"
    />
</template>

<script>
import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source';
import DxPivotGrid from 'devextreme-vue/pivot-grid';

const pivotGridDataSource = new PivotGridDataSource({
    // ...
    fields: [{
        // ...
        customizeText: function (cellInfo) {
            // Your code goes here
        }
    }]
});

export default {
    components: {
        DxPivotGrid
    },
    data() {
        return {
            pivotGridDataSource
        }
    }
}
</script>
React
App.js
import React from 'react';

import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source';
import DxPivotGrid from 'devextreme-react/pivot-grid';

const pivotGridDataSource = new PivotGridDataSource({
    // ...
    fields: [{
        // ...
        customizeText: function (cellInfo) {
            // Your code goes here
        }
    }]
});

class App extends React.Component {
    render() {
        return (
            <PivotGrid
                dataSource={pivotGridDataSource}
            />
        );
    }
}
export default App;
ASP.NET MVC Controls
Razor C#
@(Html.DevExtreme().PivotGrid()
    .DataSource(ds => ds
        // ...
        .Fields(fields => {
            fields.Add()
                // ...
                .CustomizeText("customizeText");
        })
    )
)

<script type="text/javascript">
    function customizeText (cellInfo) {
        // Your code goes here
    }
</script>
NOTE
The this keyword refers to the field's configuration.

dataField

Specifies which data source field provides data for the pivot grid field.

Type:

String

Default Value: undefined

See Also

dataType

Casts field values to a specific data type.

Type:

String

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

If your data source stores, for example, numbers or dates as strings, specify the proper type using this property. Date-time values stored as strings should have one of the following formats: "yyyy/MM/dd", "yyyy/MM/dd HH:mm:ss", "yyyy-MM-ddTHH:mm:ssx", or "yyyy-MM-ddTHH:mm:ss".

Use the PivotGridDataType enum to specify this property when the UI component is used as an ASP.NET MVC 5 Control or a DevExtreme-Based ASP.NET Core Control. This enum accepts the following values: String, Number, and Date.

displayFolder

Specifies the name of the folder in which the field is located when displayed in the field chooser.

Type:

String

Default Value: undefined

expanded

Specifies whether to expand all items within the field's header level.

Type:

Boolean

Default Value: false

filterType

Specifies whether a user changes the current filter by including (selecting) or excluding (clearing the selection of) values.

Type:

String

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

This property accepts the following values:

  • include
    Values in the header filter are unselected initially. Users change the filter by selecting the values.

  • exclude
    All values in the header filter are selected initially. Users changes the filter by clearing the selection of certain values.

Use the FilterType enum to specify this property when the UI component is used as an ASP.NET MVC 5 Control or a DevExtreme-Based ASP.NET Core Control. This enum accepts the following values: Include and Exclude.

See Also

filterValues

Specifies the values by which the field is filtered.

Type:

Array<any>

Default Value: undefined

jQuery
JavaScript
$(function() {
    var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
        // ...
        fields: [{
            // ...
            filterType: "exclude",
            filterValues: [[2015], [2016]]
        }]
    });

    $("#pivotGridContainer").dxPivotGrid({
        dataSource: pivotGridDataSource
    });
});
Angular
TypeScript
HTML
import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source";
import { DxPivotGridModule } from "devextreme-angular";
// ...
export class AppComponent {
    pivotGridDataSource: PivotGridDataSource;
    constructor() {
        this.pivotGridDataSource = new PivotGridDataSource({
            // ...
            fields: [{
                // ...
                filterType: "exclude",
                filterValues: [[2015], [2016]]
            }]
        });
    }
}

@NgModule({
    imports: [
        // ...
        DxPivotGridModule
    ],
    // ...
})
<dx-pivot-grid
    [dataSource]="pivotGridDataSource">
</dx-pivot-grid>
Vue
App.vue
<template>
    <DxPivotGrid
        :data-source="pivotGridDataSource"
    />
</template>

<script>
import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source';
import DxPivotGrid from 'devextreme-vue/pivot-grid';

const pivotGridDataSource = new PivotGridDataSource({
    // ...
    fields: [{
        // ...
        filterType: 'exclude',
        filterValues: [[2015], [2016]]
    }]
});

export default {
    components: {
        DxPivotGrid
    },
    data() {
        return {
            pivotGridDataSource
        }
    }
}
</script>
React
App.js
import React from 'react';

import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source';
import DxPivotGrid from 'devextreme-react/pivot-grid';

const pivotGridDataSource = new PivotGridDataSource({
    // ...
    fields: [{
        // ...
        filterType: 'exclude',
        filterValues: [[2015], [2016]]
    }]
});

class App extends React.Component {
    render() {
        return (
            <PivotGrid
                dataSource={pivotGridDataSource}
            />
        );
    }
}
export default App;
See Also

format

Formats field values before they are displayed.

Type:

Format

Default Value: ''

See the format section for details on accepted values.

jQuery
JavaScript
$(function() {
    var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
        // ...
        fields: [{
            // ...
            format: {
                type: "largeNumber",
                precision: 2
            },
            // ===== or a shorter version =====
            format: "largeNumber"
        }]
    });

    $("#pivotGridContainer").dxPivotGrid({
        dataSource: pivotGridDataSource
    });
});
Angular
TypeScript
HTML
import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source";
import { DxPivotGridModule } from "devextreme-angular";
// ...
export class AppComponent {
    pivotGridDataSource: PivotGridDataSource;
    constructor() {
        this.pivotGridDataSource = new PivotGridDataSource({
            // ...
            fields: [{
                // ...
                format: {
                    type: "largeNumber",
                    precision: 2
                },
                // ===== or a shorter version =====
                format: "largeNumber"
            }]
        });
    }
}

@NgModule({
    imports: [
        // ...
        DxPivotGridModule
    ],
    // ...
})
<dx-pivot-grid
    [dataSource]="pivotGridDataSource">
</dx-pivot-grid>
Vue
App.vue
<template>
    <DxPivotGrid
        :data-source="pivotGridDataSource"
    />
</template>

<script>
import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source';
import DxPivotGrid from 'devextreme-vue/pivot-grid';

const pivotGridDataSource = new PivotGridDataSource({
    // ...
    fields: [{
        // ...
        format: {
            type: 'largeNumber',
            precision: 2
        },
        // ===== or a shorter version =====
        format: 'largeNumber'
    }]
});

export default {
    components: {
        DxPivotGrid
    },
    data() {
        return {
            pivotGridDataSource
        }
    }
}
</script>
React
App.js
import React from 'react';

import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source';
import DxPivotGrid from 'devextreme-react/pivot-grid';

const pivotGridDataSource = new PivotGridDataSource({
    // ...
    fields: [{
        // ...
        format: {
            type: 'largeNumber',
            precision: 2
        },
        // ===== or a shorter version =====
        format: 'largeNumber'
    }]
});

class App extends React.Component {
    render() {
        return (
            <PivotGrid
                dataSource={pivotGridDataSource}
            />
        );
    }
}
export default App;
ASP.NET MVC Controls
Razor C#
@(Html.DevExtreme().PivotGrid()
    .DataSource(ds => ds
        // ...
        .Fields(fields => {
            fields.Add()
                // ...
                .Format(f => f
                    .Type(Format.LargeNumber)
                    .Precision(2)
                )
                // ===== or a shorter version =====
                .Format(Format.LargeNumber)
        })
    )
)

Date formats require additional configuration. The PivotGridDataSource groups date field values by date component: Year-Quarter-Month. For this, the values are converted from dates to numbers, but you cannot use date formats to format numbers. To apply a date format in this case, override this grouping.

The following code declares a custom group for the ShippingDate data field. This configuration overrides this field's default grouping. Field values are grouped by year and then by month. Months are formatted as MMM (Jan, Feb, Mar, etc.). This format is applied to the Date values the selector function returns.

jQuery
index.js
$(function() {
    const pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
        // ...
        fields: [{
            dataField: "ShippingDate",
            dataType: "date",
            area: "column",
            groupName: "dateGroup"
        }, {
            groupName: "dateGroup",
            groupIndex: 0,
            groupInterval: "year"
        }, {
            groupName: "dateGroup",
            groupIndex: 1,
            format: "MMM",
            selector: function(data) {
                const year = new Date(data.date).getFullYear();
                const month = new Date(data.date).getMonth();
                return new Date(year, month);
            } 
        },
        // ...
        ]
    });

    $("#pivotGridContainer").dxPivotGrid({
        dataSource: pivotGridDataSource
    });
});
Angular
app.component.ts
app.component.html
app.module.ts
import { Component } from '@angular/core';
import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source';

@Component({
    selector: 'app-root',
    templateUrl: './app.component.html',
    styleUrls: ['./app.component.css']
})
export class AppComponent {
    pivotGridDataSource: PivotGridDataSource;
    constructor() {
        this.pivotGridDataSource = new PivotGridDataSource({
            // ...
            fields: [{
                dataField: "ShippingDate",
                dataType: "date",
                area: "column",
                groupName: "dateGroup"
            }, {
                groupName: "dateGroup",
                groupIndex: 0,
                groupInterval: "year"
            }, {
                groupName: "dateGroup",
                groupIndex: 1,
                format: "MMM",
                selector: function(data) {
                    const year = new Date(data.date).getFullYear();
                    const month = new Date(data.date).getMonth();
                    return new Date(year, month);
                } 
            },
            // ...
            ]
        });
    }
}
<dx-pivot-grid
    [dataSource]="pivotGridDataSource">
</dx-pivot-grid>
import { BrowserModule } from '@angular/platform-browser';
import { NgModule } from '@angular/core';
import { AppComponent } from './app.component';

import { DxPivotGridModule } from 'devextreme-angular';

@NgModule({
    declarations: [
        AppComponent
    ],
    imports: [
        BrowserModule,
        DxPivotGridModule
    ],
    providers: [ ],
    bootstrap: [AppComponent]
})
export class AppModule { }
Vue
App.vue
<template>
    <DxPivotGrid
        :data-source="pivotGridDataSource"
    />
</template>

<script>
import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source';
import DxPivotGrid from 'devextreme-vue/pivot-grid';

const pivotGridDataSource = new PivotGridDataSource({
    // ...
    fields: [{
        dataField: "ShippingDate",
        dataType: "date",
        area: "column",
        groupName: "dateGroup"
    }, {
        groupName: "dateGroup",
        groupIndex: 0,
        groupInterval: "year"
    }, {
        groupName: "dateGroup",
        groupIndex: 1,
        format: "MMM",
        selector: function(data) {
            const year = new Date(data.date).getFullYear();
            const month = new Date(data.date).getMonth();
            return new Date(year, month);
        } 
    },
    // ...
    ]
});

export default {
    components: {
        DxPivotGrid
    },
    data() {
        return {
            pivotGridDataSource
        }
    }
}
</script>
React
App.js
import React from 'react';

import 'devextreme/dist/css/dx.common.css';
import 'devextreme/dist/css/dx.light.css';

import PivotGrid from 'devextreme-react/pivot-grid';
import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source';

const pivotGridDataSource = new PivotGridDataSource({
    // ...
    fields: [{
        dataField: "ShippingDate",
        dataType: "date",
        area: "column",
        groupName: "dateGroup"
    }, {
        groupName: "dateGroup",
        groupIndex: 0,
        groupInterval: "year"
    }, {
        groupName: "dateGroup",
        groupIndex: 1,
        format: "MMM",
        selector: function(data) {
            const year = new Date(data.date).getFullYear();
            const month = new Date(data.date).getMonth();
            return new Date(year, month);
        } 
    },
    // ...
    ]
});

export default function App() {
    return (
        <PivotGrid
            dataSource={pivotGridDataSource}
        />
    );
}

When a format is exported to Excel, it is converted to an Excel format as follows:

  • Numeric
    Converted to Number format, except for "largeNumber", which is exported without formatting.

  • Currency
    Converted to the Currency format.

  • Date-Time
    Converted to the Date or Time format, depending on the cell value.
    Exceptions: "quarter" and "quarterAndYear" are exported as Short Date; "minute" and "millisecond" are exported as Long Time.

  • Function
    Cannot be converted automatically. Assign this function to the customizeText property instead of the format property.

  • Custom format string
    Cannot be converted, the cell value is exported without formatting. To export this format, return the formatted string from the customizeText callback as follows:

    jQuery
    index.js
    $(function() {
        var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
            // ...
            fields: [{
                // ...
                customizeText: (e) => {
                    return e.valueText;
                },
                dataType: "number",
                format: "0.##"
            }, {
                // ...
            }]
        });
        $("#pivotGrid").dxPivotGrid({
            // ...
            export: {
                enabled: true
            },
            dataSource: pivotGridDataSource
        });
    });
    Angular
    app.component.html
    app.component.ts
    app.module.ts
    <dx-pivot-grid ... 
        [dataSource]="pivotGridDataSource">
        <dxo-export [enabled]="true"></dxo-export>
    </dx-pivot-grid>
    import { Component } from '@angular/core';
    import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source';
    
    @Component({
        selector: 'app-root',
        templateUrl: './app.component.html',
        styleUrls: ['./app.component.css']
    })
    export class AppComponent {
        pivotGridDataSource: PivotGridDataSource;
        constructor() {
            this.pivotGridDataSource = new PivotGridDataSource({
                fields: [{
                    // ...
                    customizeText: (e) => {
                        return e.valueText;
                    },
                    dataType: "number",
                    format: "0.##"
                }, {
                    // ...
                }] 
            });
        }
    }
    import { BrowserModule } from '@angular/platform-browser';
    import { NgModule } from '@angular/core';
    import { AppComponent } from './app.component';
    
    import { DxPivotGridModule } from 'devextreme-angular';
    
    @NgModule({
        declarations: [
            AppComponent
        ],
        imports: [
            BrowserModule,
            DxPivotGridModule
        ],
        providers: [],
        bootstrap: [AppComponent]
    })
    export class AppModule { }
    Vue
    App.vue
    <template>
        <DxPivotGrid ...
            :data-source="PivotGridDataSource">
            <DxExport
                :enabled="true"
            />
        </DxPivotGrid>
    </template>
    
    <script>
    import 'devextreme/dist/css/dx.common.css';
    import 'devextreme/dist/css/dx.light.css';
    
    import DxPivotGrid, {
        DxExport
    } from 'devextreme-vue/pivot-grid';
    import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source';
    
    const pivotGridDataSource = new PivotGridDataSource({
        // ...
        fields: [{
            // ...
            customizeText: (e) => {
                return e.valueText;
            },
            dataType: "number",
            format: "0.##"
        }, {
            // ...
        }]
    });
    
    export default {
        components: {
            DxPivotGrid,
            DxExport
        },
        data() {
            return {
                pivotGridDataSource
            }
        }
    }
    </script>
    React
    App.js
    import React from 'react';
    
    import 'devextreme/dist/css/dx.common.css';
    import 'devextreme/dist/css/dx.light.css';
    
    import PivotGrid, {
        Export
    } from 'devextreme-react/pivot-grid';
    import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source';
    
    const pivotGridDataSource = new PivotGridDataSource({
        fields: [{
            // ...
            customizeText: (e) => {
                return e.valueText;
            },
            dataType: "number",
            format: "0.##"
        }, {
            // ...
        }] 
    });
    
    class App extends React.Component {
        render() {
            return (
                <PivotGrid ...
                    dataSource={pivotGridDataSource}>
                    <Export enabled={true} />
                </PivotGrid>
            );
        }   
    }
    export default App;
See Also

groupIndex

Specifies the field's index within its group.

Type:

Number

Default Value: undefined

See Also

groupInterval

Specifies how the field's values are combined into groups for the headers. Cannot be used with an XmlaStore.

Type:

String

|

Number

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

If the field's values are dates, specify this property with one of the accepted string values.

If the field's values are numbers, specify this property with a number that designates a step with which groups should be generated.

Use the PivotGridGroupInterval enum to specify this property when the UI component is used as an ASP.NET MVC 5 Control or a DevExtreme-Based ASP.NET Core Control. This enum accepts the following values: Year, Quarter, Month, DayOfWeek, and Day.

See Also

groupName

Specifies the name of the field's group.

Type:

String

Default Value: undefined

See Also

headerFilter

Configures the field's header filter.

Type:

Object

jQuery
JavaScript
$(function() {
    var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
        // ...
        fields: [{
            // ...
            allowFiltering: true,
            headerFilter: {
                allowSearch: true
            }
        }]
    });

    $("#pivotGridContainer").dxPivotGrid({
        dataSource: pivotGridDataSource
    });
});
Angular
TypeScript
HTML
import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source";
import { DxPivotGridModule } from "devextreme-angular";
// ...
export class AppComponent {
    pivotGridDataSource: PivotGridDataSource;
    constructor() {
        this.pivotGridDataSource = new PivotGridDataSource({
            // ...
            fields: [{
                // ...
                allowFiltering: true,
                headerFilter: {
                    allowSearch: true
                }
            }]
        });
    }
}

@NgModule({
    imports: [
        // ...
        DxPivotGridModule
    ],
    // ...
})
<dx-pivot-grid
    [dataSource]="pivotGridDataSource">
</dx-pivot-grid>
See Also

isMeasure

Specifies whether the field should be treated as a measure (a field providing data for calculation).

Type:

Boolean

Default Value: undefined

There are no restrictions for rearranging fields in the areas using the field chooser. However, we recommend restricting measures to the data area by setting their isMeasure property to true. You can also set this property to false for a non-measure field to prohibit placing it in the data area.

jQuery
JavaScript
$(function() {
    var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
        // ...
        fields: [{
            dataField: "sales",
            area: "data",
            isMeasure: true
        }, {
            dataField: "date",
            dataType: "date",
            area: "column",
            isMeasure: false
        },
        // ...
        ]
    });

    $("#pivotGridContainer").dxPivotGrid({
        dataSource: pivotGridDataSource
    });
});
Angular
TypeScript
HTML
import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source";
import { DxPivotGridModule } from "devextreme-angular";
// ...
export class AppComponent {
    pivotGridDataSource: PivotGridDataSource;
    constructor() {
        this.pivotGridDataSource = new PivotGridDataSource({
            // ...
            fields: [{
                dataField: "sales",
                area: "data",
                isMeasure: true
            }, {
                dataField: "date",
                dataType: "date",
                area: "column",
                isMeasure: false
            },
            // ...
            ]
        });
    }
}

@NgModule({
    imports: [
        // ...
        DxPivotGridModule
    ],
    // ...
})
<dx-pivot-grid
    [dataSource]="pivotGridDataSource">
</dx-pivot-grid>
Vue
App.vue
<template>
    <DxPivotGrid
        :data-source="pivotGridDataSource"
    />
</template>

<script>
import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source';
import DxPivotGrid from 'devextreme-vue/pivot-grid';

const pivotGridDataSource = new PivotGridDataSource({
    // ...
    fields: [{
        dataField: 'sales',
        area: 'data',
        isMeasure: true
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column',
        isMeasure: false
    },
    // ...
    ]
});

export default {
    components: {
        DxPivotGrid
    },
    data() {
        return {
            pivotGridDataSource
        }
    }
}
</script>
React
App.js
import React from 'react';

import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source';
import DxPivotGrid from 'devextreme-react/pivot-grid';

const pivotGridDataSource = new PivotGridDataSource({
    // ...
    fields: [{
        dataField: 'sales',
        area: 'data',
        isMeasure: true
    }, {
        dataField: 'date',
        dataType: 'date',
        area: 'column',
        isMeasure: false
    },
    // ...
    ]
});

class App extends React.Component {
    render() {
        return (
            <PivotGrid
                dataSource={pivotGridDataSource}
            />
        );
    }
}
export default App;
See Also

name

Specifies the field's identifier.

Type:

String

Default Value: undefined

Set this property to refer to the field in code, for example, when changing field options.

runningTotal

Specifies whether to calculate the running total by rows or by columns.

Type:

String

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

For example, a pivot grid row contains the following summary values:

[ 3, 3, 3, 3, 3 ]

If the runningTotal property is set to "row", each value in this row is added to the previous value:

[ 3, 6, 9, 12, 15 ]

This also applies to columns if the runningTotal property is set to "column".

Use the PivotGridRunningTotalMode enum to specify this property when the UI component is used as an ASP.NET MVC 5 Control or a DevExtreme-Based ASP.NET Core Control. This enum accepts the following values: Row and Column.

View Demo

See Also

selector

Specifies a function that combines the field's values into groups for the headers. Cannot be used with an XmlaStore or remote operations.

Type:

Function

Default Value: undefined

This function is an alternative to the groupInterval property and should return a group name that corresponds to a data object. The function's parameter represents this data object. For example, the selector function in the following code groups birth dates by decades:

jQuery
JavaScript
$(function() {
    var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
        // ...
        fields: [{
            caption: "Birth Decade",
            area: "column",
            selector: function (data) {
                var birthDate = new Date(data.birthDate);
                return Math.floor(birthDate.getFullYear() / 10) * 10;
            }
        }]
    });
});
Angular
TypeScript
import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source";
// ...
export class AppComponent {
    pivotGridDataSource: PivotGridDataSource;
    constructor() {
        this.pivotGridDataSource = new PivotGridDataSource({
            // ...
            fields: [{
                caption: "Birth Decade",
                area: "column",
                selector: function (data) {
                    let birthDate = new Date(data.birthDate);
                    return Math.floor(birthDate.getFullYear() / 10) * 10;
                }
            }]
        });
    }
}
Vue
App.vue
<template>
    <DxPivotGrid
        :data-source="pivotGridDataSource"
    />
</template>

<script>
import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source';
import DxPivotGrid from 'devextreme-vue/pivot-grid';

const pivotGridDataSource = new PivotGridDataSource({
    // ...
    fields: [{
        caption: 'Birth Decade',
        area: 'column',
        selector: function (data) {
            let birthDate = new Date(data.birthDate);
            return Math.floor(birthDate.getFullYear() / 10) * 10;
        }
    }]
});

export default {
    components: {
        DxPivotGrid
    },
    data() {
        return {
            pivotGridDataSource
        }
    }
}
</script>
React
App.js
import React from 'react';

import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source';
import DxPivotGrid from 'devextreme-react/pivot-grid';

const pivotGridDataSource = new PivotGridDataSource({
    // ...
    fields: [{
        caption: 'Birth Decade',
        area: 'column',
        selector: function (data) {
            let birthDate = new Date(data.birthDate);
            return Math.floor(birthDate.getFullYear() / 10) * 10;
        }
    }]
});

class App extends React.Component {
    render() {
        return (
            <PivotGrid
                dataSource={pivotGridDataSource}
            />
        );
    }
}
export default App;
ASP.NET MVC Controls
Razor C#
@(Html.DevExtreme().PivotGrid()
    .DataSource(ds => ds
        // ...
        .Fields(fields => {
            fields.Add()
                .Caption("Birth Decade")
                .Area(PivotGridArea.Column)
                .Selector("groupByDecade")
        })
    )
)

<script type="text/javascript">
    function groupByDecade (data) {
        var birthDate = new Date(data.birthDate);
        return Math.floor(birthDate.getFullYear() / 10) * 10;
    }
</script>

Another example: a selector that places values below and over 1000 into different groups:

jQuery
JavaScript
$(function() {
    var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
        // ...
        fields: [{
            caption: "Amount",
            area: "column",
            selector: function (data) {
                return data.amount > 1000 ? "> 1000" : "< 1000";
            }
        }]
    });
});
Angular
TypeScript
import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source";
// ...
export class AppComponent {
    pivotGridDataSource: PivotGridDataSource;
    constructor() {
        this.pivotGridDataSource = new PivotGridDataSource({
            // ...
            fields: [{
                caption: "Amount",
                area: "column",
                selector: function (data) {
                    return data.amount > 1000 ? "> 1000" : "< 1000";
                }
            }]
        });
    }
}
Vue
App.vue
<template>
    <DxPivotGrid
        :data-source="pivotGridDataSource"
    />
</template>

<script>
import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source';
import DxPivotGrid from 'devextreme-vue/pivot-grid';

const pivotGridDataSource = new PivotGridDataSource({
    // ...
    fields: [{
        caption: 'Amount',
        area: 'column',
        selector: function (data) {
            return data.amount > 1000 ? '> 1000' : '< 1000';
        }
    }]
});

export default {
    components: {
        DxPivotGrid
    },
    data() {
        return {
            pivotGridDataSource
        }
    }
}
</script>
React
App.js
import React from 'react';

import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source';
import DxPivotGrid from 'devextreme-react/pivot-grid';

const pivotGridDataSource = new PivotGridDataSource({
    // ...
    fields: [{
        caption: 'Amount',
        area: 'column',
        selector: function (data) {
            return data.amount > 1000 ? '> 1000' : '< 1000';
        }
    }]
});

class App extends React.Component {
    render() {
        return (
            <PivotGrid
                dataSource={pivotGridDataSource}
            />
        );
    }
}
export default App;
ASP.NET MVC Controls
Razor C#
@(Html.DevExtreme().PivotGrid()
    .DataSource(ds => ds
        // ...
        .Fields(fields => {
            fields.Add()
                .Caption("Amount")
                .Area(PivotGridArea.Column)
                .Selector("classifyValues")
        })
    )
)

<script type="text/javascript">
    function classifyValues (data) {
        return data.amount > 1000 ? "> 1000" : "< 1000";
    }
</script>
NOTE
The selector function cannot be used with remote operations. Instead, create a calculated data field on the server and bind a pivot grid field to it.
NOTE
The this keyword refers to the field's configuration.
See Also

showGrandTotals

Specifies whether to display the field's grand totals. Applies only if the field is in the data area.

Type:

Boolean

Default Value: true

See Also

showTotals

Specifies whether to display the field's totals.

Type:

Boolean

Default Value: true

NOTE
Setting this property to false hides the field's totals and summary values - only the grand totals are displayed. Set the showValues property to true to display summary values.
See Also

showValues

Specifies whether to display the field's summary values. Applies only if the field is in the data area. Inherits the showTotals' value by default.

Type:

Boolean

Default Value: undefined

sortBy

Specifies how the field's values in the headers should be sorted.

Type:

String

Default Value: undefined
Accepted Values: 'displayText' | 'value' | 'none'

Dimension attributes have a value and a display text. For example, a "Month" attribute's display text can be "January", "February", ..., "December"; its values can be 1, 2, ..., 12. "January" is first when sorting by values, and "April" is first when sorting by display text.

No sorting is applied when sortBy is set to "none".

Use the PivotGridSortBy enum to specify this property when the UI component is used as an ASP.NET MVC 5 Control or a DevExtreme-Based ASP.NET Core Control. This enum accepts the following values: DisplayText, Value, and None.

See Also

sortBySummaryField

Sorts the field's values in the headers by the specified measure's summary values. Accepts the measure's name, caption, dataField, or index in the fields array.

Type:

String

Default Value: undefined

This and the sortBySummaryPath properties can be used together to sort the field's values by a specific column's or row's summary values. If sortBySummaryPath is unspecified, the field's values are sorted by the specified measure's grand totals, as it is with the Region field's values in the following code:

jQuery
JavaScript
$(function() {
    var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
        // ...
        fields: [{
            caption: "Sales",
            dataField: "sales",
            area: "data",
            isMeasure: true,
            summaryType: "sum"
        }, {
            caption: "Region",
            dataField: "region",
            area: "row",
            sortBySummaryField: "Sales"
        },
        // ...
        ]
    });

    $("#pivotGridContainer").dxPivotGrid({
        dataSource: pivotGridDataSource
    });
});
Angular
TypeScript
HTML
import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source";
import { DxPivotGridModule } from "devextreme-angular";
// ...
export class AppComponent {
    pivotGridDataSource: PivotGridDataSource;
    constructor() {
        this.pivotGridDataSource = new PivotGridDataSource({
            // ...
            fields: [{
                caption: "Sales",
                dataField: "sales",
                area: "data",
                isMeasure: true,
                summaryType: "sum"
            }, {
                caption: "Region",
                dataField: "region",
                area: "row",
                sortBySummaryField: "Sales"
            },
            // ...
            ]
        });
    }
}

@NgModule({
    imports: [
        // ...
        DxPivotGridModule
    ],
    // ...
})
<dx-pivot-grid
    [dataSource]="pivotGridDataSource">
</dx-pivot-grid>
Vue
App.vue
<template>
    <DxPivotGrid
        :data-source="pivotGridDataSource"
    />
</template>

<script>
import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source';
import DxPivotGrid from 'devextreme-vue/pivot-grid';

const pivotGridDataSource = new PivotGridDataSource({
    // ...
    fields: [{
        caption: 'Sales',
        dataField: 'sales',
        area: 'data',
        isMeasure: true,
        summaryType: 'sum'
    }, {
        caption: 'Region',
        dataField: 'region',
        area: 'row',
        sortBySummaryField: 'Sales'
    },
    // ...
    ]
});

export default {
    components: {
        DxPivotGrid
    },
    data() {
        return {
            pivotGridDataSource
        }
    }
}
</script>
React
App.js
import React from 'react';

import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source';
import DxPivotGrid from 'devextreme-react/pivot-grid';

const pivotGridDataSource = new PivotGridDataSource({
    // ...
    fields: [{
        caption: 'Sales',
        dataField: 'sales',
        area: 'data',
        isMeasure: true,
        summaryType: 'sum'
    }, {
        caption: 'Region',
        dataField: 'region',
        area: 'row',
        sortBySummaryField: 'Sales'
    },
    // ...
    ]
});

class App extends React.Component {
    render() {
        return (
            <PivotGrid
                dataSource={pivotGridDataSource}
            />
        );
    }
}
export default App;
See Also

sortBySummaryPath

Specifies a path to the column or row whose summary values should be used to sort the field's values in the headers.

Type:

Array<Number | String>

Default Value: undefined

This and the sortBySummaryField properties are applied together. If this property is not specified, the field's values are sorted by the grand totals of the measure that sortBySummaryField specifies.

In the following example, geographic regions displayed in the row header are sorted by sales in the second quarter of 2015:

jQuery
JavaScript
$(function() {
    var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
        // ...
        fields: [{
            caption: "Sales",
            dataField: "sales",
            area: "data",
            isMeasure: true,
            summaryType: "sum"
        }, {
            caption: "Date",
            dataField: "date",
            dataType: "date",
            area: "column"
        }, {
            caption: "Region",
            dataField: "region",
            area: "row",
            sortBySummaryField: "Sales",
            sortBySummaryPath: [2015, 2]
        }]
    });

    $("#pivotGridContainer").dxPivotGrid({
        dataSource: pivotGridDataSource
    });
});
Angular
TypeScript
HTML
import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source";
import { DxPivotGridModule } from "devextreme-angular";
// ...
export class AppComponent {
    pivotGridDataSource: PivotGridDataSource;
    constructor() {
        this.pivotGridDataSource = new PivotGridDataSource({
            // ...
            fields: [{
                caption: "Sales",
                dataField: "sales",
                area: "data",
                isMeasure: true,
                summaryType: "sum"
            }, {
                caption: "Date",
                dataField: "date",
                dataType: "date",
                area: "column"
            }, {
                caption: "Region",
                dataField: "region",
                area: "row",
                sortBySummaryField: "Sales",
                sortBySummaryPath: [2015, 2]
            }]
        });
    }
}

@NgModule({
    imports: [
        // ...
        DxPivotGridModule
    ],
    // ...
})
<dx-pivot-grid
    [dataSource]="pivotGridDataSource">
</dx-pivot-grid>
Vue
App.vue
<template>
    <DxPivotGrid
        :data-source="pivotGridDataSource"
    />
</template>

<script>
import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source';
import DxPivotGrid from 'devextreme-vue/pivot-grid';

const pivotGridDataSource = new PivotGridDataSource({
    // ...
    fields: [{
        caption: 'Sales',
        dataField: 'sales',
        area: 'data',
        isMeasure: true,
        summaryType: 'sum'
    }, {
        caption: 'Date',
        dataField: 'date',
        dataType: 'date',
        area: 'column'
    }, {
        caption: 'Region',
        dataField: 'region',
        area: 'row',
        sortBySummaryField: 'Sales',
        sortBySummaryPath: [2015, 2]
    }]
});

export default {
    components: {
        DxPivotGrid
    },
    data() {
        return {
            pivotGridDataSource
        }
    }
}
</script>
React
App.js
import React from 'react';

import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source';
import DxPivotGrid from 'devextreme-react/pivot-grid';

const pivotGridDataSource = new PivotGridDataSource({
    // ...
    fields: [{
        caption: 'Sales',
        dataField: 'sales',
        area: 'data',
        isMeasure: true,
        summaryType: 'sum'
    }, {
        caption: 'Date',
        dataField: 'date',
        dataType: 'date',
        area: 'column'
    }, {
        caption: 'Region',
        dataField: 'region',
        area: 'row',
        sortBySummaryField: 'Sales',
        sortBySummaryPath: [2015, 2]
    }]
});

class App extends React.Component {
    render() {
        return (
            <PivotGrid
                dataSource={pivotGridDataSource}
            />
        );
    }
}
export default App;
See Also

sortingMethod

Specifies a custom comparison function that sorts the field's values in the headers.

Type:

Function

Function parameters:
a:

Object

A field value to be compared.

Object structure:
Name Type Description
children

Array<any>

The field value's children; available only when the corresponding header item is expanded.

value

String

|

Number

The field value.

b:

Object

A field value to be compared.

Object structure:
Name Type Description
children

Array<any>

The field value's children; available only when the corresponding header item is expanded.

value

String

|

Number

The field value.

Return Value:

Number

Specifies whether a goes before b.

Default Value: undefined

This function accepts two field values and should return a number indicating their sort order:

  • Less than zero
    a goes before b.
  • Zero
    a and b remain unchanged relative to each other.
  • Greater than zero
    a goes after b.

Inside this function, you can access the field's configuration using the this keyword:

jQuery
JavaScript
$(function() {
    var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
        // ...
        fields: [{
            // ...
            sortingMethod: function (a, b) {
                var fieldSortOrder = this.sortOrder;
                // ...
            }
        },
        // ...
        ]
    });
});
Angular
TypeScript
import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source";
// ...
export class AppComponent {
    pivotGridDataSource: PivotGridDataSource;
    constructor() {
        this.pivotGridDataSource = new PivotGridDataSource({
            // ...
            fields: [{
                // ...
                sortingMethod: function (a, b) {
                    let field = this as any;
                    let fieldSortOrder = field.sortOrder;
                    // ...
                }
            },
            // ...
            ]
        });
    }
}
Vue
App.vue
<template>
    <DxPivotGrid
        :data-source="pivotGridDataSource"
    />
</template>

<script>
import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source';
import DxPivotGrid from 'devextreme-vue/pivot-grid';

const pivotGridDataSource = new PivotGridDataSource({
    // ...
    fields: [{
        // ...
        sortingMethod: function (a, b) {
            let field = this as any;
            let fieldSortOrder = field.sortOrder;
            // ...
        }
    },
    // ...
    ]
});

export default {
    components: {
        DxPivotGrid
    },
    data() {
        return {
            pivotGridDataSource
        }
    }
}
</script>
React
App.js
import React from 'react';

import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source';
import DxPivotGrid from 'devextreme-react/pivot-grid';

const pivotGridDataSource = new PivotGridDataSource({
    // ...
    fields: [{
        // ...
        sortingMethod: function (a, b) {
            let field = this as any;
            let fieldSortOrder = field.sortOrder;
            // ...
        }
    },
    // ...
    ]
});

class App extends React.Component {
    render() {
        return (
            <PivotGrid
                dataSource={pivotGridDataSource}
            />
        );
    }
}
export default App;
ASP.NET MVC Controls
Razor C#
@(Html.DevExtreme().PivotGrid()
    .DataSource(ds => ds
        // ...
        .Fields(fields => {
            fields.Add()
                // ...
                .SortingMethod("sortingMethod");
        })
    )
)

<script type="text/javascript">
    function sortingMethod (a, b) {
        var fieldSortOrder = this.sortOrder;
        // ...
    }
</script>
NOTE
The this keyword refers to the field's configuration.
See Also

sortOrder

Specifies the field values' sorting order.

Type:

String

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

Use the SortOrder enum to specify this property when the UI component is used as an ASP.NET MVC 5 Control or a DevExtreme-Based ASP.NET Core Control. This enum accepts the following values: Asc and Desc.

See Also

summaryDisplayMode

Specifies a predefined post-processing function. Does not apply when the calculateSummaryValue property is set.

Type:

String

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

Post-processing functions allow you to perform additional calculations on each summary value and take into account neighboring cells' summary values.

The following list describes the available post-processing functions. These functions apply to one summary value at a time. This value is referred to as the "current value" in the descriptions.

  • "absoluteVariation"
    Calculates the absolute difference between the current and previous value in a row. Starts from the second value in the row because the first does not have a previous value.

  • "percentVariation"
    Same as the absolute variation, but the difference is calculated as a percentage.

  • "percentOfColumnTotal"
    Calculates the current value as a percentage in one of the column's intermediate totals or the column's grand total when there are no expanded rows.

  • "percentOfRowTotal"
    Calculates the current value as a percentage in one of the row's intermediate totals or the row's grand total when there are no expanded columns.

  • "percentOfColumnGrandTotal"
    Calculates the current value as a percentage in the column's grand total.

  • "percentOfRowGrandTotal"
    Calculates the current value as a percentage in the row's grand total.

  • "percentOfGrandTotal"
    Calculates the current value as a percentage in the grand total of the entire pivot grid.

You can implement the capability to select the post-processing function at runtime using instructions from the Runtime Summary Type Selection article.

To specify a custom post-processing function, use the calculateSummaryValue property.

Use the PivotGridSummaryDisplayMode enum to specify this property when the UI component is used as an ASP.NET MVC 5 Control or a DevExtreme-Based ASP.NET Core Control. This enum accepts the following values: AbsoluteVariation, PercentVariation, PercentOfColumnTotal, PercentOfRowTotal, PercentOfColumnGrandTotal, PercentOfRowGrandTotal, and PercentOfGrandTotal.

View Demo

summaryType

Specifies how to aggregate the field's data. Cannot be used with an XmlaStore.

Type:

String

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

The summary type defines how to calculate the summary values that are displayed in the pivot grid's cells. The calculation is performed on a subset of facts that correspond to each field value in three areas (column, row, and filter).

The following summary types are supported:

Use the SummaryType enum to specify this property when the UI component is used as an ASP.NET MVC 5 Control or a DevExtreme-Based ASP.NET Core Control. This enum accepts the following values: Sum, Min, Max, Avg, Count, and Custom. To apply a custom server-side aggregate function, use a string overload instead.

You can implement the capability to select the summary type at runtime using instructions from the Runtime Summary Type Selection article.

visible

Specifies whether the field is visible in the pivot grid and field chooser.

Type:

Boolean

Default Value: true

width

Specifies the field's width in pixels when the field is displayed in the pivot grid.

Type:

Number

Default Value: undefined

wordWrapEnabled

Specifies whether text that does not fit into a header item should be wrapped.

Type:

Boolean

Default Value: undefined

See Also