Use CustomStore

Custom Store is a Store that allows you to connect a widget 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 widget.

Local Operations

By default, the widget 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

If you process data using a remote web service, enable remote operations in PivotGrid. They make the grid work faster with large amounts of data.

To enable remote operations, set the remoteOperations option of the PivotGridDataSource object to true. To use a CustomStore in PivotGrid for working with data, implement the load function specifying how data must be loaded from your web service.

JavaScript
var pivotGridDataSource = {
    remoteOperations: true,
    load: function (loadOptions) {
       // Implement your load function logic here
    }
}

Now, the pivot grid assumes that your data service supports filtering, grouping and summary calculation. The pivot grid sends settings for these operations to the server, where these settings should be applied to data, and waits for processed data to be sent back. The following code shows a pivot grid data source configured for this scenario.

JavaScript
var pivotGridDataSource = {
    remoteOperations: true,
    load: function (loadOptions) {
        // Getting settings for remote operations
        var filterOptions = loadOptions.filter ? JSON.stringify(loadOptions.filter) : "";
        var groupOptions = loadOptions.group ? JSON.stringify(loadOptions.group) : "";
        var totalSummaryOptions = loadOptions.totalSummary;
        var groupSummaryOptions = loadOptions.groupSummary;
        var d = $.Deferred();
        $.getJSON('http://mydomain.com/MyDataService', {
            // Sending settings to the server
            group: groupOptions,
            filter: filterOptions,
            totalSummary: totalSummaryOptions,
            groupSummary: groupSummaryOptions
        }).done(function (result) {
            // Managing the response
            d.resolve(result.data, { 
                summary: result.summaries 
            }); 
        });
        return d.promise();
    }
}

Settings for remote operations have the following structure.

  • loadOptions.filter

    If a single filter expression is to be passed to the server, the filter has the following structure.

    ['fieldName', 'operator', 'value']        

    Several filter expressions are combined into an array.

    [
        ['fieldName', 'operator', 'value'],
        "and", // "or"
        ['fieldName', 'operator', 'value'],
        // . . .
    ]

    For information on more sophisticated filter expressions, refer to the Filtering article.

    Note that for date fields, date components come as a part of the filter. For example:

    [
        ['dateField.year', '>', '2000'],
        "and",
        ['dateField.dayOfWeek', '=', '4']
    ]

    The following date components are supported: year, month (from 1 to 12), day, dayOfWeek (from 0 to 6).

  • loadOptions.group

    [
        // Number fields
        { selector: "fieldName1", groupInterval: 100 }
        // Date fields
        { selector: "fieldName2", groupInterval: "year" },
        { selector: "fieldName2", groupInterval: "month" },
        { selector: "fieldName2", groupInterval: "day" },
        // . . .
    ]

    Note that you cannot use the selector option along with remote operations, because the selector function is ignored on the server side. Instead of using selector, create a calculated data field on your web server, and then bind the pivot grid field directly to it.

    JavaScript
    var pivotGridDataSource = {
        remoteOperations: true,
        load: function() { . . . },
        fields: [
            // . . . 
            {
                dataField: "yourCalculatedField",
                area: "row"
            }
        ]
    }
  • loadOptions.groupSummary and loadOptions.totalSummary

    Note that these settings have the same structure, but they are sent as separate arrays.

    [   
        { selector: "fieldName1", summaryType: "sum" }, 
        { selector: "fieldName2", summaryType: "min" },
        // . . .
        { summaryType: "count" }
    ]

After the remote operation settings are applied to data on the server side, an object of the following structure should be returned back to the pivot grid.

JavaScript
{
    // . . .
    data: [{
        key: "Category 1",
        summary: [30, 20],
        items: [{
            key: "Category 1_1",
            summary: [12, 5],
            items: [
                // . . .
            ]
        }, {
            key: "Category 1_2",
            summary: [18, 15],
            items: [
                // . . .
            ]
        }]
    }, {
        key: "Category 2",
        summary: [100, 50],
        items: [ ... ]
    }, 
    // . . .
    ],
    summaries: [/* total summary values calculated for the entire dataset */]
}

Note that the items arrays located on the deepest hierarchy level, may have a null value.

For consuming data from ASP.NET and PHP servers, DevExtreme provides the following ready-to-use extensions that implement the CustomStore and server-side data processing for you.