Edit this page

PivotGrid Overview

The Kendo UI PivotGrid widget represents multidimensional data in a cross-tabular format. It allows the user to perform a complex analysis on the visualized data. Kendo UI PivotGrid uses the Online Analytical Processing (OLAP) approach to present the result of multidimensional queries in a comprehensive way. It also supports a drill-down functionality that represents the underlying data for calculated cells. The widget uses an instance of the kendo.data.PivotDataSource component as a data source. PivotDataSource communicates with an OLAP cube instance on HTTP using the XMLA protocol.

Prerequisites

Getting Started

Initialize the PivotGrid

The sections below demonstrate how to configure the Kendo UI PivotGrid widget to the "Adventure Works" cube hosted on http://demos.telerik.com.

Create the PivotGrid by defining an HTML <div> element.

Example
    <!-- Define  the HTML div that will hold the PivotGrid -->
    <div id="pivotgrid">
    </div>

The example below demonstrates how to further configure the Kendo UI PivotGrid widget.

Example
    <script>
        $(document).ready(function () {
            $("#pivotgrid").kendoPivotGrid({
                height: 200, //define the height of the widget
                dataSource: {
                    type: "xmla", //define the type
                    columns: [{ name: "[Date].[Calendar]" }], //specify a dimesion on columns
                    rows: [{ name: "[Product].[Category]" }], //specify a dimesion on rows
                    measures: ["[Measures].[Internet Sales Amount]"], //specify a measure to display
                    transport: {
                        connection: {
                            catalog: "Adventure Works DW 2008R2", //specify the name of the catalog
                            cube: "Adventure Works" //specify the name of the cube
                        },
                        read: {
                            url: "http://demos.telerik.com/olap/msmdpump.dll", //define the URL of the service
                            dataType: "text",
                            contentType: "text/xml",
                            type: "POST"
                        }
                    },
                    schema: {
                        type: "xmla" //specify the type of the schema
                    },
                }
            });
        });
    </script>

The code above results in the following PivotGrid widget:

Kendo UI PivotGrid

Settings

Filtering

The PivotGrid widget uses kendo.data.PivotDataSource to perform label filtration. That being said, it filters only by the members' caption value. The filter descriptor is similar to the filter option of kendo.data.DataSource. It contains:

  • field - the full path to the tuple member, e.g. [Date].[Calendar].[Calendar Year].&[2005].
  • operator - all operators that work with strings. Note that the widget treats field values as strings.
  • value - the filter value.

For a demo, see the runnable how-to example about filtering a dimension.

Important Filtering is supported both in OLAP and flat-data (client cube) binding scenarios.

Sorting

The widget supports sorting by the members' caption name. The structure of the sort descriptor is similar to the sort option of thekendo.data.DataSource. It contains:

  • field - the name of the dimension, e.g. [Date].[Calendar].
  • dir - the direction of the sorting.

All inner members of the sorted dimension will be sorted with the same sort dimension.

For a demo, see the runnable how-to example about sorting a dimension.

Important Sorting is supported only in OLAP binding scenarios. Client cube cannot be sorted for the time being.

Reference

Existing Instances

Refer to an existing PivotGrid instance via the jQuery.data(). Once a reference has been established, use the PivotGrid API to control its behavior.

The example below demonstrates how to access an existing PivotGrid instance.

Example
var pivotgrid = $("#pivotgrid").data("kendoPivotGrid");

Limitations

Bind to Large Flat Data

When the PivotGrid is bound to a flat data structure, it processes the data on the client (browser) creating a client cube representation (configuration). This means that the widget uses the browser's processing power to project the data, producing the required categorized data output. Although we do not restrict the maximum data amount bound to the widget, it certainly has limits, directly related to the browser capability to handle the loaded dataset.

The symptoms for an overloaded browser are:

  • The browser is extremely slowly loading or unresponsive for a long time.
  • The browser is crashing on load or on the dimensions/measures update.

If any of the above symptoms are observable, then you hit the browser processing limit.

Solution

Use a dedicated OLAP solution, like Microsoft's SSAS.

Important

The server solution should be able to communicate with the client accepting HTTP requests. It should support the XMLA 1.1 protocol.

See Also

Other articles on the Kendo UI PivotGrid:

For how-to examples on the Kendo UI PivotGrid widget, browse its How To documentation folder.