All docs
V20.2
24.2 (CTP)
24.1
23.2
23.1
22.2
22.1
21.2
21.1
20.2
20.1
19.2
The page you are viewing does not exist in version 19.2.
19.1
The page you are viewing does not exist in version 19.1.
18.2
The page you are viewing does not exist in version 18.2.
18.1
The page you are viewing does not exist in version 18.1.
17.2
The page you are viewing does not exist in version 17.2.
A newer version of this page is available. Switch to the current version.

JavaScript/jQuery PivotGrid - Use CustomStore

Custom Store is a Store that allows you to connect a UI component to a web service with its own data accessing logic. This article describes the peculiarities of a custom store implementation related to working with the PivotGrid UI component.

Local Operations

By default, the UI component performs all the operations locally. In the load function of the pivot grid data source, you can just define from what web service return data.

JavaScript
var pivotGridDataSource = {
    load: function(){
        return  $.getJSON('http://mydomain.com/MyDataService');
    }
}

In this case, all operations (filtering, grouping and summary calculation) are performed on the client side.

NOTE
Using local operations may affect grid performance if the data set is large enough (the number of records is about one million).

Remote Operations

Remote operations help to improve the PivotGrid performance when the amount of data is large. To enable remote operations, set the remoteOperations property of the PivotGridDataSource object to true.

JavaScript
var pivotGridDataSource = {
    remoteOperations: true,
    // ...
}

Now, the PivotGrid needs a special configuration of the CustomStore on the client and implementation of filtering, grouping and summary calculation on the server. If the server does not perform these operations yet, employ one of the following extensions by DevExtreme. They implement server-side data processing and also configure the CustomStore for you.

View Demo

See Also

If these extensions do not suit your needs, configure the CustomStore and implement server-side data processing by yourself, following the instructions given below. Define the load function for the CustomStore. This function accepts a bag of loadOptions and passes them to the server. The server must process data according to the loadOptions and send processed data back.

The example below shows how to implement the load function. Note that in this example, the CustomStore is not declared explicitly. Instead, its load operation is implemented directly in the PivotGridDataSource configuration object to shorten the example.

jQuery
index.js
$(function(){
    $("#pivotGrid").dxPivotGrid({
        dataSource: {
            // ...
            remoteOperations: true,
            load: function (loadOptions) {
                let d = $.Deferred();
                $.getJSON('http://mydomain.com/MyDataService', {
                    // Passing settings to the server

                    // Pass if the remoteOperations property is set to true
                    take: loadOptions.take,
                    skip: loadOptions.skip,
                    group: loadOptions.group ? JSON.stringify(loadOptions.group) : "",
                    filter: loadOptions.filter ? JSON.stringify(loadOptions.filter) : "",
                    totalSummary: loadOptions.totalSummary ? JSON.stringify(loadOptions.totalSummary) : "",
                    groupSummary: loadOptions.groupSummary ? JSON.stringify(loadOptions.groupSummary) : ""
                }).done(function (result) {
                    // You can process the received data here

                    if("data" in result)
                        d.resolve(result.data, { summary: result.summary });
                    else
                        d.resolve(result);
                });
                return d.promise();
            }
        }
    });
});
Angular
app.component.html
app.component.ts
app.module.ts
<dx-pivot-grid [dataSource]="dataSource"> 
</dx-pivot-grid>
import { Component } from '@angular/core';
import { HttpClient } from '@angular/common/http';

@Component({
  styleUrls: ["./app.component.css"],
  selector: "demo-app",
  templateUrl: "./app.component.html"
})
export class AppComponent {
    dataSource: any;

    constructor(httpClient: HttpClient) {
        function isNotEmpty(value: any): boolean {
            return value !== undefined && value !== null && value !== "";
        }
        this.dataSource = {
            remoteOperations: true,
            store: new CustomStore({
                // ...
                load: function (loadOptions: any) {
                    let params: HttpParams = new HttpParams();
                    // Passing settings to the server

                    // Pass if the remoteOperations property is set to true
                    [
                        "skip",
                        "take",
                        "filter",
                        "totalSummary",
                        "group",
                        "groupSummary"
                    ].forEach(function (i) {
                        if (i in loadOptions && isNotEmpty(loadOptions[i]))
                            params = params.set(i, JSON.stringify(loadOptions[i]));
                    });
                    return httpClient
                        .get(
                            "http://mydomain.com/MyDataService",
                            { params: params }
                        )
                        .toPromise()
                        .then((result: any) => {
                            if ("data" in result) {
                                return {
                                    data: result.data,
                                    summary: result.summary
                                }
                            } else {
                                return result;
                            }
                        })
                        .catch((error) => {
                            throw "Data Loading Error";
                        });
                }
            })
        };
    }
}
import { BrowserModule } from '@angular/platform-browser';
import { NgModule } from '@angular/core';
import { AppComponent } from './app.component';
import { HttpClientModule, HttpParams } from '@angular/common/http';
import { DxPivotGridModule } from "devextreme-angular";
import CustomStore from "devextreme/data/custom_store";

@NgModule({
    declarations: [
        AppComponent
    ],
    imports: [
        BrowserModule,
        DxPivotGridModule,
    ],
    providers: [],
    bootstrap: [AppComponent]
})
export class AppModule { }
Vue
App.vue
<template>
    <DxPivotGrid
        :data-source="dataSource"
    />
</template>
<script>
import { DxPivotGrid } from "devextreme-vue/pivot-grid";

import CustomStore from "devextreme/data/custom_store";
import "whatwg-fetch";

function isNotEmpty(value) {
  return value !== undefined && value !== null && value !== "";
}

export default {
    components: {
        DxPivotGrid,
    },
    data() {
        return {
            dataSource: {
                remoteOperations: true,
                store: new CustomStore({
                    // ...
                    load: function(loadOptions) {
                        let params = '?';
                        // Passing settings to the server

                        // Pass if the remoteOperations property is set to true 
                        [
                            "skip",
                            "take",
                            "filter",
                            "totalSummary",
                            "group",
                            "groupSummary"
                        ].forEach(function(i) {
                            if(i in loadOptions && isNotEmpty(loadOptions[i]))
                            { params += `${i}=${JSON.stringify(loadOptions[i])}&`; }
                        });
                        params = params.slice(0, -1);
                        return fetch(`http://mydomain.com/MyDataService${params}`)
                            .then(response => response.json())
                            .then((data) => {
                                return {
                                    data: data.data,
                                    summary: data.summary
                                };
                            })
                            .catch(() => { throw 'Data Loading Error'; });
                    }
                })
            },
        };
    },
};
</script>
React
App.js
import React from 'react';

import { PivotGrid } from 'devextreme-react/pivot-grid';
import CustomStore from "devextreme/data/custom_store";
import "whatwg-fetch";

function isNotEmpty(value) {
    return value !== undefined && value !== null && value !== "";
}

const dataSource = {
    remoteOperations: true,
    store: new CustomStore({
        // ...
        load: function (loadOptions) {
            let params = "?";
            // Passing settings to the server

            // Pass if the remoteOperations property is set to true
            [
                "skip",
                "take",
                "filter",
                "totalSummary",
                "group",
                "groupSummary"
            ].forEach(function (i) {
                if (i in loadOptions && isNotEmpty(loadOptions[i])) {
                    params += `${i}=${JSON.stringify(loadOptions[i])}&`;
                }
            });
            params = params.slice(0, -1);
            return fetch(`http://mydomain.com/MyDataService${params}`)
                .then(response => response.json())
                .then((data) => {
                    return {
                        data: data.data,
                        summary: data.summary
                    };
                })
                .catch(() => { throw 'Data Loading Error'; });
    }),
};

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

export default App;

Now, the PivotGrid sends several requests to load data. At first launch, it sends the request that contains the following settings.

JavaScript
{
    skip: 0,
    take: 20
}

This request is utilized to get the structure of your data, therefore, the server should return an array of data items that reflects the whole structure. Note that the array may contain only one item. The subsequent requests have a different structure and contain the following settings.

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

}

After the server applies settings to data, it should send back an object of the following structure.

JavaScript
{
    data: [{
        key: "Category 1",
        // Group summary
        summary: [30, 20],
        items: [{
            key: "Category 1_1",
            summary: [12, 5],
            items: [{
                key: "Category 1_1_1",
                summary: [5, 2],
                // This is a group of the deepest hierarchy level,
                // therefore, you need to return a null value
                items: null
            }, 
            // ...
            ]
        }, {
            key: "Category 1_2",
            summary: [18, 15],
            items: [ ... ]
        }]
    }, {
        key: "Category 2",
        summary: [100, 50],
        items: [ ... ]
    }, 
    // ...
    ],
    // Total summary
    summary: [1222, 856]
}