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.

filter

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

Type:

Filter Expression

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;

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:

fields:

Array<PivotGrid Field>

All field configurations.

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:

scrolling.mode should be "virtual".
Data should come from a remote source because other data processing operations also become remote when you enable paging.
allowExpandAll and allowSortingBySummary are ignored.
Running totals and the "absoluteVariation" and "percentVariation" summary post-processing functions are calculated only for loaded data.
Grouped items in the header filter are not paginated.

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

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.

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.

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 Options

jQuery

Angular

Vue

React

fields[]

jQuery

Angular

Vue

React

See Also

filter

jQuery

Angular

Vue

React

See Also

onChanged

jQuery

Angular

Vue

React

onFieldsPrepared

jQuery

Angular

Vue

React

onLoadError

jQuery

Angular

Vue

React

onLoadingChanged

jQuery

Angular

Vue

React

paginate

remoteOperations

See Also

retrieveFields

See Also

store

jQuery

Angular