How to: Bind a PivotGrid to an OLAP Cube with the OLE DB Data Provider

• Jul 09, 2019
• 2 minutes to read

MainWindow.xaml.vb

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

MainWindow.xaml.cs

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;
        }
    }
}

MainWindow.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>