How to: Visualize Pivot Grid Data in the Chart Control

• Jul 09, 2019
• 2 minutes to read

This example demonstrates how to bind a ChartControl to a PivotGridControl. For this, assign the PivotGridControl.ChartDataSource property value to the ChartControl.DataSource property.

The Transpose Data Source checkbox is bound to the PivotGridControl.ChartProvideDataByColumns property. If the checkbox is checked, the property is set to false, and chart series are based on pivot grid rows instead of columns.

View Example: Visualize PivotGrid Data with the ChartControl

MainWindow.xaml

<dx:ThemedWindow
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:dx="http://schemas.devexpress.com/winfx/2008/xaml/core"
    xmlns:dxpg="http://schemas.devexpress.com/winfx/2008/xaml/pivotgrid" 
    xmlns:dxc="http://schemas.devexpress.com/winfx/2008/xaml/charts" 
    x:Class="WpfPivotChart.MainWindow" Loaded="Window_Loaded"
    Title="PivotGrid and Bound ChartControl" Height="800" Width="1000">
    <Grid>
        <Grid>
            <Grid.ColumnDefinitions>
                <ColumnDefinition Width="300"/>
                <ColumnDefinition/>
            </Grid.ColumnDefinitions>
            <Grid.RowDefinitions>
                <RowDefinition Height="Auto"/>
                <RowDefinition/>
            </Grid.RowDefinitions>
            <CheckBox x:Name="cbTransposeDataSource" Content="Transpose Data Source" 
              Margin="5,10,5,10">
                <CheckBox.IsChecked>
                    <Binding ElementName="pivotGridControl1" Path="ChartProvideDataByColumns"
                     Mode="TwoWay" />
                </CheckBox.IsChecked>
            </CheckBox>
            <dxpg:PivotGridControl ShowRowGrandTotals="False" ShowColumnGrandTotals="False"
                           Grid.Column="0" Grid.Row="1" x:Name="pivotGridControl1"
                           ShowFilterHeaders="False" ChartSelectionOnly="False"
                           ChartFieldValuesProvideMode="DisplayText"
                           ChartProvideDataByColumns="True">
                <dxpg:PivotGridControl.Fields>
                    <dxpg:PivotGridField Name="fieldYear" FieldName="OrderDate" Area="ColumnArea"
                                 Caption="Year" GroupInterval="DateYear" />
                    <dxpg:PivotGridField Name="fieldSalesPerson" FieldName="ContactName"
                                 Area="RowArea" Caption="Sales Person" />
                    <dxpg:PivotGridField Name="fieldQuantity" FieldName="Quantity" Area="DataArea" />
                </dxpg:PivotGridControl.Fields>
            </dxpg:PivotGridControl>
            <dxc:ChartControl DataSource="{Binding ElementName=pivotGridControl1, Path=ChartDataSource}"
                      x:Name="chartControl1" Grid.Column="1" Grid.Row="1">
                <dxc:ChartControl.Diagram>
                    <dxc:XYDiagram2D SeriesDataMember="Series">
                        <dxc:XYDiagram2D.SeriesTemplate>
                            <dxc:BarSideBySideSeries2D ArgumentDataMember="Arguments" 
                                               ValueDataMember="Values"/>
                        </dxc:XYDiagram2D.SeriesTemplate>
                    </dxc:XYDiagram2D>
                </dxc:ChartControl.Diagram>
            </dxc:ChartControl>
        </Grid>
    </Grid>
</dx:ThemedWindow>

MainWindow.xaml.cs

using System.Data;
using System.Windows;
using DevExpress.Xpf.Core;
using Microsoft.Data.Sqlite;

namespace WpfPivotChart {
    public partial class MainWindow : ThemedWindow {
        public MainWindow() {
            InitializeComponent();
        }
        private void Window_Loaded(object sender,RoutedEventArgs e) {
            // Create a data source.
            DataTable dt;

            using (SqliteConnection connection = new SqliteConnection("Data Source=nwind.db")) {
                connection.Open();
                SqliteCommand cmd = (SqliteCommand)SqliteFactory.Instance.CreateCommand();
                cmd.CommandText = @"SELECT * FROM Orders
                                    INNER JOIN Customers ON Customers.CustomerID = Orders.CustomerID
                                    INNER JOIN OrderDetails ON OrderDetails.OrderID = Orders.OrderID";
                cmd.Connection = connection;
                using (SqliteDataReader dr = cmd.ExecuteReader()) {
                    do {
                        dt = new DataTable();
                        dt.BeginLoadData();
                        dt.Load(dr);
                        dt.EndLoadData();

                    } while (!dr.IsClosed && dr.NextResult());
                }
            }

            // Assign the data source to the PivotGrid control.
            pivotGridControl1.DataSource = dt;
            pivotGridControl1.BestFit();
        }
    }
}

MainWindow.xaml.vb

Imports System.Data
Imports System.Text
Imports DevExpress.Xpf.Core
Imports DevExpress.Xpf.PivotGrid
Imports Microsoft.Data.Sqlite

Partial Public Class MainWindow
    Inherits ThemedWindow
    Public Sub New()
        InitializeComponent()
    End Sub
    Private Sub Window_Loaded(ByVal sender As Object, ByVal e As RoutedEventArgs)
        ' Create a data source.
        Dim dt As DataTable
        Using connection As New SqliteConnection("Data Source=nwind.db;")
            connection.Open()
            Dim cmd As SqliteCommand = CType(SqliteFactory.Instance.CreateCommand(), SqliteCommand)
            cmd.CommandText = "SELECT * FROM Orders
                                    INNER JOIN Customers ON Customers.CustomerID = Orders.CustomerID
                                    INNER JOIN OrderDetails ON OrderDetails.OrderID = Orders.OrderID"
            cmd.Connection = connection
            Using dr As SqliteDataReader = cmd.ExecuteReader()
                Do
                    dt = New DataTable()
                    dt.BeginLoadData()
                    dt.Load(dr)
                    dt.EndLoadData()
                Loop While Not dr.IsClosed AndAlso dr.NextResult()
            End Using
        End Using

        ' Assign the data source to the PivotGrid control.
        pivotGridControl1.DataSource = dt
    End Sub
End Class