Lesson 2 - Bind a Pivot Grid to an OLAP Cube (.NET)

Jul 22, 2025
8 minutes to read

An OLAP (OnLine Analytical Processing) cube is a multidimensional structure defined by its measures and dimensions. You can bind a cube’s measures to Pivot Grid fields in the data area. A cube’s dimensions can supply data to fields in the column area, row area, or filter area. This tutorial contains step-by-step instructions on how to bind the Pivot Grid to an existing OLAP cube in code. This example uses the Adventure Works cube.

View Example

Prerequisites

Create a New Project and Add the Pivot Grid Control

Run Microsoft Visual Studio.
Select WPF Application for .NET and click Next.
The next step of the New Project wizard allows you to configure project settings and set the project’s target .NET version. Select the target framework version — .NET 6.0.
Drag the PivotGridControl item from the DX.25.2: Data & Analytics toolbox tab to add the control to your project.
Right-click the Pivot Grid and choose the Layout → Reset All option in the context menu. This option stretches the control to fill the entire window.
After this, your XAML may look like the following:

Connect the Pivot Grid to an OLAP Cube

Important

You cannot bind the Pivot Grid to data at design time in .NET 5+ projects.

Specify connection settings to the server in the PivotGridControl.OlapConnectionString property. A sample connection string is shown below.

Provider=msolap;Data Source=http://demos.devexpress.com/Services/OLAP/msmdpump.dll;Initial Catalog=Adventure Works DW;Cube Name=Adventure Works;Query Timeout=100;

Note that a valid connection string should contain the following parameters: Provider, Data Source, Initial Catalog, and Cube Name.

MainWindow.xaml.cs
MainWindow.xaml.vb

using DevExpress.Xpf.PivotGrid;
using System.Windows;

namespace HowToBindOLAP {
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window {
        public MainWindow() {
            InitializeComponent();
        }
        private void Window_Loaded(object sender, RoutedEventArgs e) {
            pivotGridControl1.OlapConnectionString = "Provider=msolap;" +
                "Data Source=http://demos.devexpress.com/Services/OLAP/msmdpump.dll;" +
                "Initial Catalog=Adventure Works DW Standard Edition;" +
                "Cube Name=Adventure Works;";
                // ...
        }
    }
}

Imports System.Windows
Imports DevExpress.Xpf.PivotGrid

Namespace HowToBindOLAP
    ''' <summary>
    ''' Interaction logic for MainWindow.xaml
    ''' </summary>
    Partial Public Class MainWindow
        Inherits Window

        Public Sub New()
            InitializeComponent()
        End Sub

        Private Sub Window_Loaded(ByVal sender As Object, ByVal e As RoutedEventArgs)
            pivotGridControl1.OlapConnectionString = "Provider=msolap;" & "Data Source=http://demos.devexpress.com/Services/OLAP/msmdpump.dll;" & "Initial Catalog=Adventure Works DW Standard Edition;" & "Cube Name=Adventure Works;"
            ' ...
        End Sub
        ' ...
    End Class
End Namespace

Create Pivot Grid Fields

The code below creates several fields, binds them to the data source, and specifies their position within the control. Follow the steps below:

Create a PivotGridField object and add it to the PivotGridControl.Fields collection.
Use the Area property to specify the field’s area. You can use AreaIndex to specify the order of fields in Pivot Grid areas if there is more than one field in an area. AreaIndex can be set only after the field is added to the control’s field collection.

MainWindow.xaml.cs
MainWindow.xaml.vb

using DevExpress.Xpf.PivotGrid;
using System.Windows;

namespace HowToBindOLAP {
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window {
        public MainWindow() {
            InitializeComponent();
        }
        private void Window_Loaded(object sender, RoutedEventArgs e) {
        // ...
            pivotGridControl1.BeginUpdate();
            // ...
            // Create Pivot Grid fields.
            PivotGridField fieldMeasuresInternetSalesAmount =
                new PivotGridField();
            fieldMeasuresInternetSalesAmount.Caption = "Internet Sales Amount";
            fieldMeasuresInternetSalesAmount.Area = FieldArea.DataArea;
            pivotGridControl1.Fields.Add(fieldMeasuresInternetSalesAmount);

            PivotGridField fieldSales = new PivotGridField();
            fieldSales.Caption = "Cleared Amount";
            fieldSales.Area = FieldArea.DataArea;
            fieldSales.CellFormat = "c";
            pivotGridControl1.Fields.Add(fieldSales);
            // ...
            pivotGridControl1.EndUpdate();
        }
        // ...
    }
}

Imports System.Windows
Imports DevExpress.Xpf.PivotGrid

Namespace HowToBindOLAP
    ''' <summary>
    ''' Interaction logic for MainWindow.xaml
    ''' </summary>
    Partial Public Class MainWindow
        Inherits Window

        Public Sub New()
            InitializeComponent()
        End Sub

        Private Sub Window_Loaded(ByVal sender As Object, ByVal e As RoutedEventArgs)
        ' ...
            pivotGridControl1.BeginUpdate()
            ' Create Pivot Grid fields.
            Dim fieldMeasuresInternetSalesAmount As New PivotGridField()
            fieldMeasuresInternetSalesAmount.Caption = "Internet Sales Amount"
            fieldMeasuresInternetSalesAmount.Area = FieldArea.DataArea
            pivotGridControl1.Fields.Add(fieldMeasuresInternetSalesAmount)

            Dim fieldSales As New PivotGridField()
            fieldSales.Caption = "Cleared Amount"
            fieldSales.Area = FieldArea.DataArea
            fieldSales.CellFormat = "c"
            pivotGridControl1.Fields.Add(fieldSales)
            ' ...
            pivotGridControl1.EndUpdate()
        End Sub
        ' ...
    End Class
End Namespace

Populate Pivot Grid Fields with Data

The sections below describe how to bind Pivot Grid fields to OLAP’s measures and dimensions, or MDX expressions.

Bind a Field to a Measure or Dimension

Follow the steps below to bind a Pivot Grid field to a measure or dimension.

Create a DataSourceColumnBinding instance.
Specify the DataSourceColumnBinding.ColumnName property. ColumnName must specify the full name of the bound measure or dimension.
Assign the DataSourceColumnBinding object to the PivotGridField.DataBinding property.

The code below shows how to bind the created fieldMeasuresInternetSalesAmount field to data.

MainWindow.xaml.cs
MainWindow.xaml.vb

using DevExpress.Xpf.PivotGrid;
using System.Windows;

namespace HowToBindOLAP {
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window {
        public MainWindow() {
            InitializeComponent();
        }
        private void Window_Loaded(object sender, RoutedEventArgs e) {
        // ...
            pivotGridControl1.BeginUpdate();
            // ...
            // Populate fields with data.
            fieldMeasuresInternetSalesAmount.DataBinding =
                new DataSourceColumnBinding("[Measures].[Internet Sales Amount]");
                // ...
            pivotGridControl1.EndUpdate();
        }
        // ...
    }
}

Imports System.Windows
Imports DevExpress.Xpf.PivotGrid

Namespace HowToBindOLAP
    ''' <summary>
    ''' Interaction logic for MainWindow.xaml
    ''' </summary>
    Partial Public Class MainWindow
        Inherits Window

        Public Sub New()
            InitializeComponent()
        End Sub

        Private Sub Window_Loaded(ByVal sender As Object, ByVal e As RoutedEventArgs)
        ' ...
            pivotGridControl1.BeginUpdate()
            ' ...
            fieldMeasuresInternetSalesAmount.DataBinding = New DataSourceColumnBinding("[Measures].[Internet Sales Amount]")
            ' ...
            pivotGridControl1.EndUpdate()
        End Sub
        ' ...
    End Class
End Namespace

Bind a Field to an MDX Expression

Follow the steps below to bind a Pivot Grid field to an MDX expression.

Create an OlapExpressionBinding instance and pass the expression in its constructor as a parameter.
Assign the OlapExpressionBinding object to the PivotGridField.DataBinding property.

The code below shows how to bind the created fieldSales field to the MDX expression.

MainWindow.xaml.cs
MainWindow.xaml.vb

csharp

using DevExpress.Xpf.PivotGrid;
using System.Windows;

namespace HowToBindOLAP {
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window {
        public MainWindow() {
            InitializeComponent();
        }
        private void Window_Loaded(object sender, RoutedEventArgs e) {
        // ...
            pivotGridControl1.BeginUpdate();
            // ...
            fieldSales.DataBinding =
                new OlapExpressionBinding("[Measures].[Internet Sales Amount] * 0.87");
                // ...
            pivotGridControl1.EndUpdate();
        }
        // ...
    }
}

Imports System.Windows
Imports DevExpress.Xpf.PivotGrid

Namespace HowToBindOLAP
    ''' <summary>
    ''' Interaction logic for MainWindow.xaml
    ''' </summary>
    Partial Public Class MainWindow
        Inherits Window

        Public Sub New()
            InitializeComponent()
        End Sub

        Private Sub Window_Loaded(ByVal sender As Object, ByVal e As RoutedEventArgs)
        ' ...
            pivotGridControl1.BeginUpdate()
            ' ...
            fieldSales.DataBinding = New OlapExpressionBinding("[Measures].[Internet Sales Amount] * 0.87")
            ' ...
            pivotGridControl1.EndUpdate()
        End Sub
        ' ...
    End Class
End Namespace

Result

The resulting code appears as follows:

MainWindow.xaml.cs
MainWindow.xaml.vb

using DevExpress.Xpf.PivotGrid;
using System.Windows;

namespace HowToBindOLAP {
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window {
        public MainWindow() {
            InitializeComponent();
        }
        private void Window_Loaded(object sender, RoutedEventArgs e) {
            pivotGridControl1.OlapConnectionString = "Provider=msolap;" +
                "Data Source=http://demos.devexpress.com/Services/OLAP/msmdpump.dll;" +
                "Initial Catalog=Adventure Works DW Standard Edition;" +
                "Cube Name=Adventure Works;";
            pivotGridControl1.BeginUpdate();

            // Create Pivot Grid fields.
            PivotGridField fieldMeasuresInternetSalesAmount =
                new PivotGridField();
            fieldMeasuresInternetSalesAmount.Caption = "Internet Sales Amount";
            fieldMeasuresInternetSalesAmount.Area = FieldArea.DataArea;
            pivotGridControl1.Fields.Add(fieldMeasuresInternetSalesAmount);

            PivotGridField fieldSales = new PivotGridField();
            fieldSales.Caption = "Cleared Amount";
            fieldSales.Area = FieldArea.DataArea;
            fieldSales.CellFormat = "c";
            pivotGridControl1.Fields.Add(fieldSales);

            // Populate fields with data.
            fieldMeasuresInternetSalesAmount.DataBinding =
                new DataSourceColumnBinding("[Measures].[Internet Sales Amount]");

            fieldSales.DataBinding =
                new OlapExpressionBinding("[Measures].[Internet Sales Amount] * 0.87");

            AddField("Country", FieldArea.RowArea, "[Customer].[Country].[Country]", 0);
            AddField("Fiscal Year", FieldArea.ColumnArea, "[Date].[Fiscal Year].[Fiscal Year]", 0);

            pivotGridControl1.EndUpdate();
        }
        // Add fields to the Pivot Grid and bind them to data.
        private PivotGridField AddField(string caption, FieldArea area, string fieldName, int index) {
            PivotGridField field = pivotGridControl1.Fields.Add();
            field.Caption = caption;
            field.Area = area;
            if (fieldName != string.Empty)
                field.DataBinding = new DataSourceColumnBinding(fieldName);
            field.AreaIndex = index;
            return field;
        }
    }
}

Imports System.Windows
Imports DevExpress.Xpf.PivotGrid

Namespace HowToBindOLAP
    ''' <summary>
    ''' Interaction logic for MainWindow.xaml
    ''' </summary>
    Partial Public Class MainWindow
        Inherits Window

        Public Sub New()
            InitializeComponent()
        End Sub

        Private Sub Window_Loaded(ByVal sender As Object, ByVal e As RoutedEventArgs)
            pivotGridControl1.OlapConnectionString = "Provider=msolap;" & "Data Source=http://demos.devexpress.com/Services/OLAP/msmdpump.dll;" & "Initial Catalog=Adventure Works DW Standard Edition;" & "Cube Name=Adventure Works;"
            pivotGridControl1.BeginUpdate()
            ' Create Pivot Grid fields.
            Dim fieldMeasuresInternetSalesAmount As New PivotGridField()
            fieldMeasuresInternetSalesAmount.Caption = "Internet Sales Amount"
            fieldMeasuresInternetSalesAmount.Area = FieldArea.DataArea
            pivotGridControl1.Fields.Add(fieldMeasuresInternetSalesAmount)

            Dim fieldSales As New PivotGridField()
            fieldSales.Caption = "Cleared Amount"
            fieldSales.Area = FieldArea.DataArea
            fieldSales.CellFormat = "c"
            pivotGridControl1.Fields.Add(fieldSales)

            ' Populate fields with data.
            fieldMeasuresInternetSalesAmount.DataBinding = New DataSourceColumnBinding("[Measures].[Internet Sales Amount]")

            fieldSales.DataBinding = New OlapExpressionBinding("[Measures].[Internet Sales Amount] * 0.87")

            AddField("Country", FieldArea.RowArea, "[Customer].[Country].[Country]", 0)
            AddField("Fiscal Year", FieldArea.ColumnArea, "[Date].[Fiscal Year].[Fiscal Year]", 0)

            pivotGridControl1.EndUpdate()
        End Sub
        Private Function AddField(ByVal caption As String, ByVal area As FieldArea, ByVal fieldName As String, ByVal index As Integer) As PivotGridField
            Dim field As PivotGridField = pivotGridControl1.Fields.Add()
            field.Caption = caption
            field.Area = area
            If fieldName <> String.Empty Then
                field.DataBinding = New DataSourceColumnBinding(fieldName)
            End If
            field.AreaIndex = index
            Return field
        End Function
    End Class
End Namespace

xaml

<Window
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:local="clr-namespace:HowToBindOLAP"
        xmlns:dxpg="http://schemas.devexpress.com/winfx/2008/xaml/pivotgrid" x:Class="HowToBindOLAP.MainWindow"
        mc:Ignorable="d"
        Title="MainWindow" Height="450" Width="800" Loaded="Window_Loaded">
    <Grid>

        <dxpg:PivotGridControl Name="pivotGridControl1"/>

    </Grid>
</Window>

Run the project and see the result.