JavaScript/jQuery PivotGrid - Data Binding

The PivotGrid UI component can present data obtained from different sources. You can declare an array of plain objects or get data from a remote server - PivotGrid will operate with any source quickly and seamlessly. In this guide, you will learn about different ways of providing and binding data to the PivotGrid UI component.

See Also

DevExtreme is supplied with a set of components called "data library" that helps you read data from different sources. A comprehensive overview of data library features is provided in the Data Layer topic. There are two main concepts, which the data library is built on: DataSource and Store.

DataSource is a stateful object that keeps sorting, grouping, filtering, etc., and applies them each time data is loaded. It also provides events intended to handle data and state changes. More information on the DataSource object can be found in the Reference section.

Store is a universal data access component supporting a number of methods for reading data. You can find the list of these methods in the What Are Stores topic. There are five predefined types of Stores used in PivotGrid.

• ArrayStore - provides access to an in-memory array
• LocalStore - provides access to the HTML5 web storage
• ODataStore - provides access to a remote OData service
• XmlaStore - provides access to a remote OLAP server
• CustomStore - allows you to implement your own data access logic

PivotGrid accepts a PivotGridDataSource instance or a PivotGridDataSource configuration object. The instance or the object must have the store field specified (except for a CustomStore, where all Store settings are specified directly in the object). This field can be set to an object configuring a Store of any type listed above. In further subtopics, you will see configuration examples for using a Store of each type.

You can use one of the two available approaches for providing data for PivotGrid.

• The first approach is processing the raw data on the client side by the PivotGrid UI component. This approach should be used when the amount of data is several million records and less, otherwise local data processing will cause significant delays. This approach is discussed in greater detail in the Using the Client-Side Processing section.

• The second approach is remote data processing on an OLAP server. This approach allows you to work with a large amount of data (several millions records and more) without delays. For more information on this approach, see the Using an OLAP section.

Client-side data processing is used for working with data whose amounts are close to one million records and which can be processed locally in an acceptable time frame. There are four Store types that support this approach. The specifics of using each Store type are detailed in the sections below.

This type of store is great when you need a simple data source with the data lifetime equaling the lifetime of your application. An ArrayStore is created from an array of plain objects. The code snippet below illustrates the minimal configuration of an ArrayStore that can be used to provide data for the PivotGrid UI component.

var pivotGridDataSource = {
    store: {
        type: 'array',
        data: [
            {   "CustomerID": "WELLI",
                "OrderDate": "2010/07/15",
                "ShipAddress": "Rua do Mercado, 12",
                "ShipCity": "Resende",
                "ShipRegion": "SP",
                "ShipCountry": "Brazil",
                "Freight": 13.97
            },{
                "CustomerID": "TOMSP",
                "OrderDate": "2010/07/05",
                "ShipAddress": "Luisenstr, 48",
                "ShipCity": "Munster",
                "ShipRegion": null,
                "ShipCountry": "Germany",
                "Freight": 11.61
            },{
                "CustomerID": "HANAR",
                "OrderDate": "2010/07/08",
                "ShipAddress": "Rua do Paco, 67",
                "ShipCity": "Rio de Janeiro",
                "ShipRegion": "RJ",
                "ShipCountry": "Brazil",
                "Freight": 65.83
            }
        ]
    }
};

After you have created a data source configuration object, you need to bind it to your grid. Refer to the Bind Data section to learn how to do this.

View Demo

This type of Store is helpful when you use an HTML5 Web Storage (also known as window.localStorage) for storing your data. To provide a minimal configuration for a LocalStore, specify its type and name properties as demonstrated below.

var pivotGridDataSource = {
    store: {
        type: 'local',
        name: 'myLocalStore'
    }
};

After you have created a data source configuration object, you need to bind it to your grid. Refer to the Bind Data section to learn how to do this.

OData is a universal open protocol for consuming data APIs. The DevExtreme data library provides a special type of Store to access OData web services, called ODataStore. The following code is sufficient to provide data for PivotGrid using an ODataStore.

var pivotGridDataSource = {
    store: {
        type: 'odata',
        url: 'http://url/to/the/source'
    }
};

An ODataStore has many more properties available for configuring. For the full list, refer to the ODataStore reference section.

After you have created a data source configuration object, you need to bind it to your grid. Refer to the Bind Data section to learn how to do this.

A 1-Click Solution for CRUD Web API Services with Role-based Access Control via EF Core

If you target .NET for your backend API, be sure to check out Web API Service and register your free copy today. The Solution Wizard scaffolds an OData v4 Web API Service (.NET 6+) with integrated authorization & CRUD operations powered by EF Core ORM. You can use OAuth2, JWT or custom authentication strategies alongside tools like Postman or Swagger (OpenAPI) for API testing. The built-in Web API Service also filters out secured server data based on permissions granted to users. Advanced/enterprise functions include audit trail, endpoints to download reports, file attachments, check validation, obtain localized captions, etc.

To use the free Solution Wizard (which creates the Web API Service), run the Universal Component Installer from the DevExpress Download Manager and use our predefined template in Visual Studio 2022+.

Read Tutorial | View Examples: JavaScript (DevExtreme) & JavaScript (Svelte) | Watch Videos

When you have a custom web service with its own data accessing logic, use a CustomStore to operate data. This type of Store requires the implementation of a function for each data access operation. To use a CustomStore in PivotGrid, implement the load function specifying how data must be loaded from your web service.

var pivotGridDataSource = {
    load: function (loadOptions) {
        var d = $.Deferred();
        $.getJSON('http://mydomain.com/MyDataService').done(function (data) {
            // process data here
            d.resolve(data); 
        }).fail(d.reject);
        return d.promise();
    }
}

A CustomStore has many more configuration properties than those described in this topic. For a complete list, refer to the CustomStore reference section.

After you have created a data source configuration object, bind it to your grid. Refer to the Bind Data section to learn how to do this.

DevExtreme is supplied with the XmlaStore Store type, which is used to interact with OLAP servers using an XMLA standard. If you intended to use OLAP technology for analyzing your data with the PivotGrid UI component, follow the steps below and you will learn how to configure the Microsoft Analysis Services and connect the PivotGrid UI component to it.

1. Install Microsoft Analysis Services. For details, refer to the How to: Install Analysis Services by Using Setup.aspx) MSDN article.

2. Install SQL Server Data Tools ― Business Intelligence for your version of Visual Studio (Visual Studio 2012 version).

3. Create a new project and define a Cube. For details, refer to the Multidimensional Modeling MSDN tutorial. If you would like to use sample data instead of your own, refer to the Install Sample Data and Projects MSDN article.

4. Configure HTTP access to the Analysis Services. For details, refer to the Configure HTTP Access to Analysis Services on Internet Information Services (IIS) 8.0 MSDN article.

5. Configure IIS to allow cross-domain access to the OLAP application created in step 4.

 5.1. Add the HTTP Response Headers. In the OLAP application, in the Home pane, double-click HTTP Response Headers. Add the following custom headers.

• Name: "Access-Control-Allow-Headers", Value: "Content-Type"
• Name: "Access-Control-Allow-Methods", Value: "POST"
• Name: "Access-Control-Allow-Origin" , Value: <the host from which you are going to access this server or "*" for all hosts>

 5.2. While configuring HTTP access, you added an "OLAP" script map to the OLAP application (Step 3, items 6-8 of the Configure HTTP Access to Analysis Services on Internet Information Services (IIS) 8.0 MSDN article). To make the cross-domain access work, make sure the "OPTIONSVerbHandler" is at the top of the Handler Mappings list. To access the list's order, click View Ordered List... from the Actions menu. If the "OPTIONSVerbHandler" is not at the top of the list, select the "OPTIONSVerbHandler" and use the Move Up command from the Actions menu to move it to the top. Also make sure the OLAP handler is located above other *.dll handlers (even the disabled ones).

6. Configure the PivotGrid.dataSource.store object for using your OLAP server.

View Demo

Regardless of the approach you've chosen to provide data, after implementing a data source, bind it to the PivotGrid UI component using the dataSource property as demonstrated below.

It is also recommended that you declare the fields array and describe data from the DataSource for the PivotGrid UI component there.

var pivotGridDataSource = {
    store: {
        //...
    },      
    fields: [{
        //...
    },
    //...
    ],
};

var dxPivotGridOptions = {
    dataSource: pivotGridDataSource
};