Integrate ASPxPivotGrid with the Chart Control

Dec 24, 2024
9 minutes to read

Tip

You can use DevExpress BI Dashboard to build data visualization and analysis UIs that include Charts, Grids, Maps, Pivot Grids, Cards, Range Selectors, and other elements. This cross-platform product is available as part of the DevExpress Universal Subscription.

Review the following help topic to learn the basics: Get Started with the DevExpress Dashboard.

The Pivot Grid presents multidimensional data in a cross-tabular form, allowing end-users to perform various data shaping operations. They can customize the report to reshape data and create a layout that fits their specific data analysis needs.

ASPxPivotGrid allows end-users to go further and visualize data displayed in the Pivot Grid using the Chart Control.

Watch Video: DevExpress ASP.NET Pivot Grid: Integration with Chart Control (YouTube)

Binding Overview

The Chart control retrieves data from the Pivot Grid control in the following manner:

The series data member values are generated based on Pivot Grid control columns;
The arguments data member values are generated based on Pivot Grid control rows;
The values data member contains summary values.

Series and argument data members are created based on column/row headers (e.g., ‘1994’, ‘Produce Total’, ‘Grand Total’). If a column/row is identified by multiple fields, the corresponding data member value is constructed from the respective field values connected by the ‘|’ sign (e.g., ‘1995 | January’, ‘Beverages | Chai’).

Implementation

To set an ASPxPivotGrid as the WebChartControl data source, do one of the following:

At design time, click the WebChartControl smart tag and select the ASPxPivotGrid instance from the Choose Data Source drop-down list:
Assign the ASPxPivotGrid.ID property value to the WebChartControl ASPxDataWebControlBase.DataSourceID property.

By default, the WebChartControl displays all the data of the ASPxPivotGrid control. Use the PivotGridOptionsChartDataSourceBase.DataProvideMode property to control whether or not the ASPxPivotGrid should only pass values to the WebChartControl from the lowest aggregation level.

Use the PivotGridOptionsChartDataSourceBase.ProvideDataByColumns property to toggle the default behavior in order to create a series data member based on rows, and an argument data member based on columns.

To update WebChartControl according to the changes made to ASPxPivotGrid, you can use the following approaches:

Set the ASPxPivotGrid ASPxPivotGrid.EnableCallBacks property to false. Both controls will be updated simultaneously when ASPxPivotGrid data is changed.
Call the ASPxClientWebChartControl.PerformCallback method from the ASPxClientPivotGrid.EndCallback event handler.

It is also possible to refresh the attached WebChartControl only on certain positions. Use the ASPxPivotGrid.JSProperties collection that allows you to pass additional information client side. For example, you can handle the ASPxPivotGrid.GridLayout event to update the attached chart only if a pivot grid layout has been changed, or update the chart after data is refreshed.

Binding and Layout Options

When the ASPxPivotGrid.ID property is assigned to the ASPxDataWebControlBase.DataSourceID property, the chart bindings and layout are auto-adjusted. If necessary, you can manually specify WebChartControl binding settings that define how to create a diagram from supplied data. There are two modes of providing binding settings to ChartControl: automatic and manual. You can specify which one to use via the PivotGridDataSourceOptions.AutoBindingSettingsEnabled property.

Automatic ModeIn this mode (the PivotGridDataSourceOptions.AutoBindingSettingsEnabled property is set to true), binding settings are automatically adjusted. In this instance, WebChartControl uses data source series, argument and value data members to create series, arguments and values, respectively. You can manually adjust automatically applied binding settings by handling the WebChartControl.CustomizeAutoBindingSettings event. This event is raised when automatic settings are about to be applied to the WebChartControl.Manual ModeIn this mode (the PivotGridDataSourceOptions.AutoBindingSettingsEnabled property is set to false), specify which data member should be used to create series via the ChartControl.SeriesDataMember property. Then use the SeriesBase.ArgumentDataMember and SeriesBase.ValueDataMembers properties of the ChartControl.SeriesTemplate object to specify which data members should be used to create arguments and values, respectively. Use the ‘Series’, ‘Arguments’ and ‘Values’ strings to refer to corresponding data members stored in the data source.

When binding settings are applied, you can manually specify layout settings that define how to display the diagram. As with binding settings, you can let WebChartControl adjust these settings automatically (in this instance, you can manually customize them by handling various events) or specify them manually. Use the PivotGridDataSourceOptions.AutoLayoutSettingsEnabled property to specify whether or not layout settings are automatically adjusted.

For a list of binding and layout settings, see the Pivot Charting topic in the Chart Control documentation.

Data Types Specifics

If a Pivot Grid contains multiple data fields with different data types, cell values are converted to Decimal (if possible). Use the PivotGridOptionsChartDataSourceBase.ProvideCellValuesAsType property to override this behavior by explicitly specifying a data type to which cell values should be converted.

WebChartControl supports only Numeric and DateTime series point values (see Series Scale Types to learn more). To convert cell values into an appropriate type in a custom manner, handle the ASPxPivotGrid.CustomChartDataSourceData event.

Member Table

ASPxPivotGrid provides multiple options, enabling you to specify how its data is interpreted by WebChartControl. These options are represented by a PivotGridWebOptionsChartDataSource class instance, and accessed via the ASPxPivotGrid.OptionsChartDataSource property.

PivotGridOptionsChartDataSourceBase.ProvideDataByColumnsGets or sets whether series in a chart control are created for PivotGrid columns or rows.PivotGridOptionsChartDataSourceBase.DataProvideModeGets or sets what data the pivot grid should pass to a bound chart control.PivotGridOptionsChartDataSourceBase.ProvideColumnCustomTotalsGets or sets whether column custom totals should be passed to a chart control.PivotGridOptionsChartDataSourceBase.ProvideColumnGrandTotalsGets or sets whether column grand totals are passed to a chart control.PivotGridOptionsChartDataSourceBase.ProvideRowCustomTotalsGets or sets whether row custom totals should be displayed in a chart control.PivotGridOptionsChartDataSourceBase.ProvideRowGrandTotalsGets or sets whether row grand totals are passed to a chart control.PivotGridOptionsChartDataSourceBase.ProvideRowTotalsGets or sets whether row totals are passed to a chart control.PivotGridOptionsChartDataSourceBase.ProvideEmptyCellsGets or sets whether empty cells should be processed by the Chart control.PivotGridOptionsChartDataSourceBase.FieldValuesProvideModeGets or sets how field values are passed to the chart control.PivotGridOptionsChartDataSourceBase.ProvideCellValuesAsTypeGets or sets the type to which cell values are converted, before they are exported to the Chart Control.PivotGridOptionsChartDataSourceBase.ProvideColumnFieldValuesAsTypeGets or sets the type to which the column field values are converted before they are exported to the chart control.PivotGridOptionsChartDataSourceBase.ProvideRowFieldValuesAsTypeGets or sets the type to which the row field values are converted, before they are exported to the chart control.PivotGridOptionsChartDataSourceBase.MaxAllowedPointCountInSeriesGets or sets the maximum number of points allowed in a series.PivotGridOptionsChartDataSourceBase.MaxAllowedSeriesCountGets or sets the maximum number of series allowed in the chart data source.

Note

Note that PivotGridOptionsChartDataSourceBase.Provide…Totals properties are in effect when the PivotGridOptionsChartDataSourceBase.DataProvideMode is set to the PivotChartDataProvideMode.UseCustomSettings.

Example

The following example shows how to bind a Chart control to a Pivot Grid to visualize data.

In this example, after an ASPxPivotGrid and WebChartControl are created, the pivot grid’s ID is assigned to the WebChartControl.DataSourceID property. By default, the PivotGridDataSourceOptions.AutoBindingSettingsEnabled property is set to true, so that binding settings are automatically adjusted, and no further customization is needed. Once the WebChartControl is bound to the ASPxPivotGrid, pivot grid data is immediately displayed in the Chart.

The sample web page contains the ‘Transpose Data Source’ check box that defines the pivot grid’s PivotGridOptionsChartDataSourceBase.ProvideDataByColumns property value. If the check box is checked, the property is set to false, and series are created based on pivot grid rows (instead of columns, which is the default behavior).

Default.aspx (C#)
Default.aspx.cs
Default.aspx.vb
Default.aspx (VB.NET)

aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs"
    Inherits="ASPxPivotGrid_ChartIntegration._Default" %>

<%@ Register Assembly="DevExpress.XtraCharts.v14.2, Version=14.2.2.0,
    Culture=neutral, PublicKeyToken=b88d1754d700e49a"
    Namespace="DevExpress.XtraCharts" TagPrefix="cc1" %>

<%@ Register Assembly="DevExpress.Web.v14.2, Version=14.2.2.0,
    Culture=neutral, PublicKeyToken=b88d1754d700e49a"
    Namespace="DevExpress.Web" TagPrefix="dxe" %>

<%@ Register Assembly="DevExpress.XtraCharts.v14.2.Web, Version=14.2.2.0,
    Culture=neutral, PublicKeyToken=b88d1754d700e49a"
    Namespace="DevExpress.XtraCharts.Web" TagPrefix="dxchartsui" %>

<%@ Register Assembly="DevExpress.Web.ASPxPivotGrid.v14.2, Version=14.2.2.0,
    Culture=neutral, PublicKeyToken=b88d1754d700e49a"
    Namespace="DevExpress.Web.ASPxPivotGrid"
    TagPrefix="dx" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>

    <form id="form1" runat="server">
    <div>
        <dxe:ASPxCheckBox ID="ASPxCheckBox1" runat="server" Text="Transpose Data Source"
        OnCheckedChanged="ASPxCheckBox1_CheckedChanged"
        AutoPostBack="True">
        </dxe:ASPxCheckBox>
        <dx:ASPxPivotGrid ID="ASPxPivotGrid1" runat="server" DataSourceID="AccessDataSource1"
        OptionsView-ShowColumnGrandTotals="False" OptionsView-ShowRowGrandTotals="False">
            <Fields>
                <dx:PivotGridField ID="fieldShippedYear" Area="ColumnArea" AreaIndex="0"
                Caption="Order Date" FieldName="OrderDate" GroupInterval="DateYear">
                </dx:PivotGridField>
                <dx:PivotGridField ID="fieldQuantity" Area="DataArea" AreaIndex="0"
                Caption="Quantity" FieldName="Quantity">
                </dx:PivotGridField>
                <dx:PivotGridField ID="fieldSalesperson" Area="RowArea" AreaIndex="0"
                Caption="Sales Person" FieldName="Sales_Person">
                </dx:PivotGridField>
            </Fields>
        </dx:ASPxPivotGrid>
        <asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/nwind.mdb"
        SelectCommand="SELECT [OrderDate], [Quantity], [Sales Person] AS Sales_Person FROM [SalesPerson]">
        </asp:AccessDataSource>
        <dxchartsui:WebChartControl ID="WebChartControl1" runat="server" Width="700" Height="400">
        </dxchartsui:WebChartControl>
    </div>
    </form>
</body>
</html>

csharp

using System;
using System.Data;
using System.Configuration;
using System.Web.UI;

namespace ASPxPivotGrid_ChartIntegration {
    public partial class _Default : Page {
        protected override void OnInit(EventArgs e) {
            base.OnInit(e);

            // Specifies that Series data member values are created based on columns.
            ASPxPivotGrid1.OptionsChartDataSource.ProvideDataByColumns = true;

            // Specifies that data binding settings are automatically adjusted 
            // while retrieving data from the pivot grid.
            WebChartControl1.PivotGridDataSourceOptions.AutoBindingSettingsEnabled = true;

            // Sets the pivot grid as the chart's data source.
            WebChartControl1.DataSourceID = "ASPxPivotGrid1";
        }
        protected void ASPxCheckBox1_CheckedChanged(object sender, EventArgs e) {
            ASPxPivotGrid1.OptionsChartDataSource.ProvideDataByColumns = !ASPxCheckBox1.Checked;
        }
    }
}

Imports Microsoft.VisualBasic
Imports System
Imports System.Data
Imports System.Configuration
Imports System.Web.UI

Namespace ASPxPivotGrid_ChartIntegration
    Partial Public Class _Default
        Inherits Page
        Protected Overrides Sub OnInit(ByVal e As EventArgs)
            MyBase.OnInit(e)

            ' Specifies that Series data member values are created based on columns.
            ASPxPivotGrid1.OptionsChartDataSource.ProvideDataByColumns = True

            ' Specifies that data binding settings are automatically adjusted 
            ' while retrieving data from the pivot grid.
            WebChartControl1.PivotGridDataSourceOptions.AutoBindingSettingsEnabled = True

            ' Sets the pivot grid as the chart's data source.
            WebChartControl1.DataSourceID = "ASPxPivotGrid1"
        End Sub
        Protected Sub ASPxCheckBox1_CheckedChanged(ByVal sender As Object, ByVal e As EventArgs)
            ASPxPivotGrid1.OptionsChartDataSource.ProvideDataByColumns = Not ASPxCheckBox1.Checked
        End Sub
    End Class
End Namespace

aspx

<%@ Page Language="vb" AutoEventWireup="true" CodeBehind="Default.aspx.vb"
    Inherits="ASPxPivotGrid_ChartIntegration._Default" %>

<%@ Register Assembly="DevExpress.XtraCharts.v14.2, Version=14.2.2.0,
    Culture=neutral, PublicKeyToken=b88d1754d700e49a"
    Namespace="DevExpress.XtraCharts" TagPrefix="cc1" %>

<%@ Register Assembly="DevExpress.Web.v14.2, Version=14.2.2.0,
    Culture=neutral, PublicKeyToken=b88d1754d700e49a"
    Namespace="DevExpress.Web" TagPrefix="dxe" %>

<%@ Register Assembly="DevExpress.XtraCharts.v14.2.Web, Version=14.2.2.0,
    Culture=neutral, PublicKeyToken=b88d1754d700e49a"
    Namespace="DevExpress.XtraCharts.Web" TagPrefix="dxchartsui" %>

<%@ Register Assembly="DevExpress.Web.ASPxPivotGrid.v14.2, Version=14.2.2.0,
    Culture=neutral, PublicKeyToken=b88d1754d700e49a"
    Namespace="DevExpress.Web.ASPxPivotGrid"
    TagPrefix="dx" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>

    <form id="form1" runat="server">
    <div>
        <dxe:ASPxCheckBox ID="ASPxCheckBox1" runat="server" Text="Transpose Data Source"
        OnCheckedChanged="ASPxCheckBox1_CheckedChanged"
        AutoPostBack="True">
        </dxe:ASPxCheckBox>
        <dx:ASPxPivotGrid ID="ASPxPivotGrid1" runat="server" DataSourceID="AccessDataSource1"
        OptionsView-ShowColumnGrandTotals="False" OptionsView-ShowRowGrandTotals="False">
            <Fields>
                <dx:PivotGridField ID="fieldShippedYear" Area="ColumnArea" AreaIndex="0"
                Caption="Order Date" FieldName="OrderDate" GroupInterval="DateYear">
                </dx:PivotGridField>
                <dx:PivotGridField ID="fieldQuantity" Area="DataArea" AreaIndex="0"
                Caption="Quantity" FieldName="Quantity">
                </dx:PivotGridField>
                <dx:PivotGridField ID="fieldSalesperson" Area="RowArea" AreaIndex="0"
                Caption="Sales Person" FieldName="Sales_Person">
                </dx:PivotGridField>
            </Fields>
        </dx:ASPxPivotGrid>
        <asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/nwind.mdb"
        SelectCommand="SELECT [OrderDate], [Quantity], [Sales Person] AS Sales_Person FROM [SalesPerson]">
        </asp:AccessDataSource>
        <dxchartsui:WebChartControl ID="WebChartControl1" runat="server" Width="700" Height="400">
        </dxchartsui:WebChartControl>
    </div>
    </form>
</body>
</html>