DevExtreme jQuery - PivotGridDataSource - fields

Configures pivot grid fields.

import { Field } from "devextreme/ui/pivot_grid/data_source"

Type:

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.

View Demo

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

Allows users to expand/collapse all header items within the same header level. Ignored if the PivotGridDataSource's paginate property is true.

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

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

allowSortingBySummary

Allows users to sort the pivot grid by summary values instead of field values. Ignored if the PivotGridDataSource's paginate property is true.

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.

area

Specifies the field's area.

Type:

PivotGridArea

Default Value: undefined

Accepted Values: undefined

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.

View Demo

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.

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.

View on GitHub

calculateSummaryValue

Specifies a custom post-processing function for summary values.

Type:

Function

Function parameters:

e:

Summary Cell

The cell whose summary value is being processed.

Return Value:

Number

| null

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 cell summary values. You can hide specific rows if the calculateSummaryValue property returns null, and the hideEmptySummaryCells property is true.

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

The following code snippet shows how to use calculateSummaryValue to implement a custom "Profit/Loss" column.

DevExtreme PivotGrid: Profit/Loss column

jQuery

JavaScript

$(function() {
    const pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
        // ...
        fields: [{
            caption: "Profit/Loss",
            dataType: "number",
            format: "currency",
            area: "data",
            calculateSummaryValue: function(summaryCell) {
                const prevCell = summaryCell.prev('column', true);
                if (prevCell) {
                    const prevVal = prevCell.value("Total Amount");
                    const currentVal = summaryCell.value("Total Amount");
                    return currentVal - prevVal;
                }
                return null;
            }
        }]
    });

    $("#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: "Profit/Loss",
                dataType: "number",
                format: "currency",
                area: "data",
                calculateSummaryValue: function(summaryCell) {
                    const prevCell = summaryCell.prev('column', true);
                    if (prevCell) {
                        const prevVal = prevCell.value("Total Amount");
                        const currentVal = summaryCell.value("Total Amount");
                        return currentVal - prevVal;
                    }
                    return null;
                }
            }]
        });
    }
}

@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: "Profit/Loss",
        dataType: "number",
        format: "currency",
        area: "data",
        calculateSummaryValue: function(summaryCell) {
            const prevCell = summaryCell.prev('column', true);
            if (prevCell) {
                const prevVal = prevCell.value("Total Amount");
                const currentVal = summaryCell.value("Total Amount");
                return currentVal - prevVal;
            }
            return null;
        }
    }]
});

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: "Profit/Loss",
        dataType: "number",
        format: "currency",
        area: "data",
        calculateSummaryValue: function(summaryCell) {
            const prevCell = summaryCell.prev('column', true);
            if (prevCell) {
                const prevVal = prevCell.value("Total Amount");
                const currentVal = summaryCell.value("Total Amount");
                return currentVal - prevVal;
            }
            return null;
        }
    }]
});

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) {
        const prevCell = summaryCell.prev('column', true);
        if (prevCell) {
            const prevVal = prevCell.value("Total Amount");
            const currentVal = summaryCell.value("Total Amount");
            return currentVal - prevVal;
        }
        return null;
    }
</script>

The "Profit/Loss" cell displays a difference between "Total Amount" cells in this case.

CalculateSummaryValue: How the cell value is counted

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

View Demo

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.

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

View Demo

dataType

Casts field values to a specific data type.

Type:

PivotGridDataType

Default Value: undefined

View Demo

displayFolder

Specifies the name of the directory 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

View Demo

filterType

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

Type:

FilterType

Default Value: '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.

This property changes when the user clicks the Select All checkbox in the header filter (only if header filter displays plain data):

Select All	filterType / filterValues
	`filterType: "include"` `filterValues: null`
	`filterType: "exclude"` `filterValues: null`

NOTE

If your data is hierarchical and PivotGrid is bound to OLAP, do not set filterType to "exclude".

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;

View Demo

format

Formats field values before they are displayed.

Type:

Format

Default Value: ''

See the format section for information 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.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.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.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;

groupIndex

Specifies the field's index within its group.

Type:

Number

Default Value: undefined

groupInterval

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

Type:

PivotGridGroupInterval

|

Number

Default Value: undefined

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

View Demo

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

groupName

Specifies the name of the field's group.

Type:

String

Default Value: undefined

View Demo

headerFilter

Configures the field's header filter.

Type:

Object

jQuery

JavaScript

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

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

Angular

app.component.html

app.component.ts

app.module.ts

<dx-pivot-grid
    [dataSource]="pivotGridDataSource">
</dx-pivot-grid>

import { Component } from '@angular/core'; 

@Component({ 
    selector: 'app-root', 
    templateUrl: './app.component.html', 
    styleUrls: ['./app.component.css'] 
})

export class AppComponent {
    pivotGridDataSource: PivotGridDataSource;
    constructor() {
        this.pivotGridDataSource = new PivotGridDataSource({
            // ...
            fields: [{
                // ...
                allowFiltering: true,
                headerFilter: {
                    // ...
                    search: {
                        enabled: true,
                        // ...
                    }
                }
            }]
        });
    }
}

import { BrowserModule } from '@angular/platform-browser'; 
import { NgModule } from '@angular/core'; 
import { AppComponent } from './app.component'; 
import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source";
import { DxPivotGridModule } from "devextreme-angular";

@NgModule({ 
    declarations: [ 
        AppComponent 
    ], 
    imports: [ 
        BrowserModule, 
        DxPivotGridModule
    ], 
    providers: [ ], 
    bootstrap: [AppComponent] 
}) 

export class AppModule { }

Vue

App.vue (Options API)

App.vue (Composition API)

<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: [{
        // ...
        allowFiltering: true,
        headerFilter: {
            // ...
            search: {
                enabled: true,
                // ...
            }
        }
    }]
});

export default {
    components: {
        DxPivotGrid
    },
    data() {
        return {
            pivotGridDataSource
        }
    }
}
</script>

<template>
    <DxPivotGrid
        :data-source="pivotGridDataSource"
    />
</template>

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

const pivotGridDataSource = new PivotGridDataSource({
    // ...
    fields: [{
        // ...
        allowFiltering: true,
        headerFilter: {
            // ...
            search: {
                enabled: true,
                // ...
            }
        }
    }]
});
</script>

React

App.js

import React from 'react';  
import 'devextreme/dist/css/dx.light.css'; 

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

const pivotGridDataSource = new PivotGridDataSource({
    // ...
    fields: [{
        // ...
        allowFiltering: true,
        headerFilter: {
            // ...
            search: {
                enabled: true,
                // ...
            }
        }
    }]
});

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

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;

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:

PivotGridRunningTotalMode

Default Value: undefined

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

View Demo

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.

View Demo

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

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.

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:

PivotGridSortBy

Default Value: undefined

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

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;

View Demo

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;

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.

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.

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.

sortOrder

Specifies the field values' sorting order.

Type:

SortOrder

Default Value: 'asc'

View Demo

summaryDisplayMode

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

Type:

PivotGridSummaryDisplayMode

Default Value: undefined

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.

jQuery

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.

View Demo

summaryType

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

Type:

SummaryType

|

String

Default Value: 'count'

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:

"sum"
"min"
"max"
"avg"
"count"
"custom"
Applies a custom client-side aggregate function (calculateCustomSummary). Refer to Client-Side Data Aggregation for more information.
Any other type
Applies a custom server-side aggregate function. Refer to Server-Side Data Aggregation for more information.

View Demo

jQuery

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

View Demo

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

General Information

.NET Subscription

Common Tools

Quality Assurance and Productivity

Desktop

Controls

Tools

Frameworks and Libraries

Web

Controls and Extensions

Tools

Maintenance Mode

Mobile

Enterprise and Analytic Tools

End-User Documentation

General Information

.NET Subscription

Common Tools

Quality Assurance and Productivity

Desktop

Controls

Tools

Frameworks and Libraries

Web

Controls and Extensions

Tools

Maintenance Mode

Mobile

Enterprise and Analytic Tools

End-User Documentation

DevExtreme jQuery - PivotGridDataSource - fields

jQuery

Angular

Vue

React

See Also

allowCrossGroupCalculation

allowExpandAll

See Also

allowFiltering

See Also

allowSorting

See Also

allowSortingBySummary

See Also

area

See Also

areaIndex

jQuery

Angular

Vue

React

calculateCustomSummary

jQuery

Angular

Vue

React

ASP.NET MVC Controls

calculateSummaryValue

jQuery

Angular

Vue

React

ASP.NET MVC Controls

caption

customizeText

jQuery

Angular

Vue

React

ASP.NET MVC Controls

dataField

See Also

dataType

See Also

displayFolder

See Also

expanded

See Also

filterType