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. The widget 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 PivotGrid 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

• Get Acquainted with the PivotGrid Fundamentals
• Set Up an OLAP Cube, or
• Use the Kendo UI OLAP Service—http://demos.telerik.com/olap/msmdpump.dll (the URL does not open directly in the browser).

Getting Started

Initialize the PivotGrid

The following sections demonstrate how to configure the PivotGrid to the Adventure Works cube that is hosted on http://demos.telerik.com.

To create the PivotGrid, define an HTML <div> element.

Example

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

The following example demonstrates how to further configure the 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 previous code results in the following PivotGrid widget.

Figure 1: The Kendo UI PivotGrid widget

Settings

Filtering

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

• field—The full path to the tuple member. For example, [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, refer to the runnable how-to example on filtering a dimension.

Important

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

Sorting

The widget supports sorting by the caption name of the members. The structure of the sort descriptor is similar to the sort option of the kendo.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, refer to the runnable how-to example on sorting a dimension.

Important

Sorting is supported only in OLAP binding scenarios. Currently, you cannot sort the flat data (client cube).

Reference

Existing Instances

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

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

Example

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

Known Limitations

Binding to Large Flat Data

When the PivotGrid is bound to a flat-data structure, it processes the data on the client (browser) and creates a client cube representation (configuration). This means that the widget uses the processing power of the browser to project the data and produces the required categorized data output. Although the PivotGrid does not restrict the maximum data amount bound to itself, the data has limits that are 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 you observe any of these symptoms, this means you have hit the processing limit of the browser.

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:

• PivotConfigurator Overview
• Exporting
• Fundamentals
• OLAP Cube Setup
• Frequently Asked Questions
• Overview of the ASP.NET MVC HtmlHelper Extension
• Overview of the JSP Tag
• Overview of the PHP Class
• PivotGrid JavaScript API Reference

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