DevExtreme jQuery - PivotGridDataSource Options

This section describes properties that configure the PivotGridDataSource.

NOTE

The PivotGridDataSource allows you to specify CustomStore properties 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';
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
        }
    })
});

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

fields[]

Configures pivot grid fields.

Type:

Array<Object>

Default Value: undefined

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

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

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

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

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

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

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

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

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

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

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

View Demo

See Also

filter

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

Possible variants:

  • Binary filter
    Supported operators: "=", "<>", ">", ">=", "<", "<=", "startswith", "endswith", "contains", "notcontains".
    Example:

    [ "dataField", "=", 3 ]
  • Unary filter
    Supported operators: binary operators, "!".
    Example:

    [ "!", [ "dataField", "=", 3 ] ]
  • Complex filter
    Supported operators: binary and unary operators, "and", "or".
    Example:

    [
        [ "dataField", "=", 10 ],
        "and",
        [
            [ "anotherDataField", "<", 3 ],
            "or",
            [ "anotherDataField", ">", 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, sorting, summary calculation) should be performed on the server.

Type:

Boolean

Default Value: false

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

JavaScript
{
    skip: 0,
    take: 20
}
NOTE
If you use local stores (ArrayStore or LocalStore) in the PivotGridDataSource, disable the remoteOperations property to ensure that usage scenarios works properly.

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" },
        // ... 
    ]
}

A sorting operation executes on a back-end if you enable the remoteOperations property and set the 'scrolling.mode' property to 'virtual'. In all other cases, a sorting operation is executed locally.

Refer to the 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 property, the PivotGrid contains only the fields configured in the fields array. With this property 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

|

Object

|

Array<Object>

This property 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 property.

  • Array
    Assigning an array to the store property 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.