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

DevExtreme jQuery - PivotGridDataSource Options

This section describes options that configure the PivotGridDataSource.

NOTE

The PivotGridDataSource allows specifying CustomStore options in its configuration object, as shown in the following code:

jQuery
JavaScript
var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
    load: function (loadOptions) {
        // Loading data objects
    },
    byKey: function (key) {
        // Retrieving a data object by key
    }
});
Angular
TypeScript
import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source";
import CustomStore from "devextreme/data/custom_store";
// ...
export class AppComponent {
    pivotGridDataSource: PivotGridDataSource;
    constructor() {
        this.pivotGridDataSource = new PivotGridDataSource({
            store: new CustomStore({
                load: (loadOptions) => {
                    // Loading data objects
                },
                byKey: (key) => {
                    // Retrieving a data object by key
                }
            })
        });
    }
}
Vue
App.vue
<script>
import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source';
import CustomStore from 'devextreme/data/custom_store';

const pivotGridDataSource = new PivotGridDataSource({
    store: new CustomStore({
        load: (loadOptions) => {
            // Loading data objects
        },
        byKey: (key) => {
            // Retrieving a data object by key
        }
    })
});

export default {
    // ...
    data() {
        return {
            pivotGridDataSource
        }
    }
}
</script>
React
App.js
// ...
import PivotGridDataSource from 'devextreme/ui/pivot_grid/data_source';
iimport CustomStore from 'devextreme/data/custom_store';

const pivotGridDataSource = new PivotGridDataSource({
    store: new CustomStore({
        load: (loadOptions) => {
            // Loading data objects
        },
        byKey: (key) => {
            // Retrieving a data object by key
        }
    })
});

class App extends React.Component {
    // ...
}
export default App;

fields[]

Configures pivot grid fields.

Type:

Array<Object>

Default Value: undefined

This option 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 option. Fields with the specified area option 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 option 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

filter

Specifies data filtering conditions. Cannot be used with an XmlaStore.

Possible variants:

  • Binary filter

    [ "dataField", "=", 3 ]
  • Unary filter

    [ "!", [ "dataField", "=", 3 ] ]
  • Complex filter

    [
        [ "dataField", "=", 10 ],
        "and",
        [
            [ "otherDataField", "<", 3 ],
            "or",
            [ "otherDataField", ">", 11 ]
        ]
    ]
jQuery
JavaScript
$(function() {
    var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
        // ...
        filter: [ "date", "startswith", "2014" ]
    });

    $("#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({
            // ...
            filter: [ "date", "startswith", "2014" ]
        });
    }
}

@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({
    // ...
    filter: [ 'date', 'startswith', '2014' ]
});

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({
    // ...
    filter: [ 'date', 'startswith', '2014' ]
});

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

onChanged

A function that is executed after data is successfully loaded.

Type:

Function

jQuery
JavaScript
$(function() {
    var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
        onChanged: function () {
            // 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({
            onChanged: () => {
                // 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({
    // ...
    onChanged: () => {
        // 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({
    // ...
    onChanged: () => {
        // Your code goes here
    }
});

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

onFieldsPrepared

A function that is executed when all fields are loaded from the store and they are ready to be displayed in the PivotGrid.

Type:

Function

Function parameters:

jQuery
JavaScript
$(function() {
    var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
        onFieldsPrepared: function (fields) {
            // 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({
            onFieldsPrepared: (fields) => {
                // 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({
    // ...
    onFieldsPrepared: (fields) => {
        // 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({
    // ...
    onFieldsPrepared: (fields) => {
        // Your code goes here
    }
});

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

onLoadError

A function that is executed when data loading fails.

Type:

Function

Function parameters:
error:

Object

The error.

jQuery
JavaScript
$(function() {
    var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
        onLoadError: function (error) {
            console.log(error.message);
        }
    });

    $("#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({
            onLoadError: (error) => {
                console.log(error.message);
            }
        });
    }
}

@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({
    // ...
    onLoadError: (error) => {
        console.log(error.message);
    }
});

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({
    // ...
    onLoadError: (error) => {
        console.log(error.message);
    }
});

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

onLoadingChanged

A function that is executed when the data loading status changes.

Type:

Function

Function parameters:
isLoading:

Boolean

Indicates whether data is being loaded.

jQuery
JavaScript
$(function() {
    var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
        onLoadingChanged: function (isLoading) {
            // 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({
            onLoadingChanged: (isLoading) => {
                // 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({
    // ...
    onLoadingChanged: (isLoading) => {
        // 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({
    // ...
    onLoadingChanged: (isLoading) => {
        // Your code goes here
    }
});

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

paginate

Specifies whether the PivotGridDataSource should load data in portions. Can be used only with an XmlaStore.

Type:

Boolean

Default Value: false

Paging has the following specifics:

View Demo

remoteOperations

Specifies whether the data processing operations (filtering, grouping, summary calculation) should be performed on the server.

Type:

Boolean

Default Value: false

If you enable this option, the PivotGrid sends several requests to load data. At first launch, the widget sends a request to get the data structure. It contains the following loadOptions:

JavaScript
{
    skip: 0,
    take: 20
}

The server should return an array of data objects. It may contain a single object if this object reflects the entire data structure.

Subsequent requests are different and contain the following loadOptions:

JavaScript
{
    filter: [
        [ "dataFieldName1", "operator", "value" ],
        "and", // "or"
        [ "dataFieldName2", "operator", "value" ],
        // Filter for date fields
        // The following date components are supported:
        // year, month (from 1 to 12), day, dayOfWeek (from 0 to 6)
        // ['dateField.year', '>', '2000'],
        // "and", // "or"
        // ['dateField.dayOfWeek', '=', '4']
        // ...
    ],
    group: [
        // Group expression for numbers
        { selector: "dataFieldName1", groupInterval: 100, desc: false },
        // Group expression for dates
        { selector: "dataFieldName2", groupInterval: "year", desc: false },
        { selector: "dataFieldName2", groupInterval: "month", desc: false },
        // Group expression for strings
        { selector: "dataFieldName3", desc: true },
        // ...
    ],
    totalSummary: [
        { selector: "dataFieldName1", summaryType: "sum" }, 
        { selector: "dataFieldName2", summaryType: "min" },
        // ... 
    ],
    groupSummary: [
        { selector: "dataFieldName1", summaryType: "sum" }, 
        { selector: "dataFieldName2", summaryType: "min" },
        // ... 
    ]
}

Refer to the Custom Data Sources | Server-Side Data Processing article for more information on how DevExtreme components communicate with the server.

View Demo

See Also

retrieveFields

Specifies whether to auto-generate pivot grid fields from the store's data.

Type:

Boolean

Default Value: true

If you disable this option, the PivotGrid contains only the fields configured in the fields array. With this option enabled, these fields are accompanied by auto-generated fields, which do not belong to any area by default and are only available in the field chooser.

See Also

store

Configures the DataSource's underlying store.

Type:

Store

|

Store Configuration

|

XmlaStore

|

XmlaStore Configuration

|

Array<Object>

|

Object

This option accepts one of the following:

  • Store instance
    An XmlaStore, ArrayStore, LocalStore, ODataStore, or CustomStore instance.

  • Store configuration object
    An XmlaStore, ArrayStore, LocalStore, or ODataStore configuration object. Make sure to set the type option.

  • Array
    Assigning an array to the store option automatically creates an ArrayStore in the PivotGridDataSource.

jQuery
JavaScript
$(function() {
    var pivotGridDataSource = new DevExpress.data.PivotGridDataSource({
        store: new DevExpress.data.XmlaStore({
            // XmlaStore instance
        })
        // ===== or =====
        store: {
            type: "xmla",
            // XmlaStore configuration object
        }
    });

    $("#pivotGridContainer").dxPivotGrid({
        dataSource: pivotGridDataSource
    });
});
Angular
TypeScript
HTML
import PivotGridDataSource from "devextreme/ui/pivot_grid/data_source";
import XmlaStore from "devextreme/ui/pivot_grid/xmla_store";
import { DxPivotGridModule } from "devextreme-angular";
// ...
export class AppComponent {
    pivotGridDataSource: PivotGridDataSource;
    constructor() {
        this.pivotGridDataSource = new PivotGridDataSource({
            store: new XmlaStore({
                // XmlaStore instance
            })
            // ===== or =====
            store: {
                type: "xmla",
                // XmlaStore configuration object
            }
        });
    }
}

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

See the Use CustomStore topic for information on how to implement custom data access logic.