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

• Apr 15, 2018
• 2 minutes to read

The following example demonstrates how to bind a PivotGridControl to an MS OLAP cube.

For this, it is required to set the PivotGridControl.OlapConnectionString property value, and add the necessary fields to the PivotGridControl.Fields collection.

Note

A complete sample project is available at https://github.com/DevExpress-Examples/how-to-bind-a-pivotgrid-to-an-olap-cube-e2048.

MainWindow.xaml

<Window xmlns:dxpg="http://schemas.devexpress.com/winfx/2008/xaml/pivotgrid" 
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        x:Class="HowToBindOLAP.MainWindow"
        Title="MainWindow" Loaded="Window_Loaded"
        Height="350" Width="525">
    <Grid>
        <dxpg:PivotGridControl HorizontalAlignment="Left" VerticalAlignment="Top" 
                               Name="pivotGridControl1" />
    </Grid>
</Window>

MainWindow.xaml.vb

Imports System.Windows
Imports DevExpress.Xpf.PivotGrid

Namespace HowToBindOLAP
    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 fields.
            Dim fieldMeasuresInternetSalesAmount As New  _
                PivotGridField("[Measures].[Internet Sales Amount]", FieldArea.DataArea)
            fieldMeasuresInternetSalesAmount.Caption = "Internet Sales Amount"

            Dim fieldCustomerCountryCountry As New  _
                PivotGridField("[Customer].[Country].[Country]", FieldArea.RowArea)
            fieldCustomerCountryCountry.Caption = "Country"

            Dim fieldDateFiscalYearFiscalYear As New  _
                PivotGridField("[Date].[Fiscal Year].[Fiscal Year]", FieldArea.ColumnArea)
            fieldDateFiscalYearFiscalYear.Caption = "Fiscal Year"

            ' Add fields to the PivotGridControl
            pivotGridControl1.Fields.AddRange(fieldMeasuresInternetSalesAmount, _
                                        fieldCustomerCountryCountry, fieldDateFiscalYearFiscalYear)
            pivotGridControl1.EndUpdate()
        End Sub
    End Class
End Namespace

MainWindow.xaml.cs

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

namespace HowToBindOLAP {
    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 fields.
            PivotGridField fieldMeasuresInternetSalesAmount = 
                new PivotGridField("[Measures].[Internet Sales Amount]", FieldArea.DataArea);
            fieldMeasuresInternetSalesAmount.Caption = "Internet Sales Amount";

            PivotGridField fieldCustomerCountryCountry = 
                new PivotGridField("[Customer].[Country].[Country]", FieldArea.RowArea);
            fieldCustomerCountryCountry.Caption = "Country";

            PivotGridField fieldDateFiscalYearFiscalYear = 
                new PivotGridField("[Date].[Fiscal Year].[Fiscal Year]", FieldArea.ColumnArea);
            fieldDateFiscalYearFiscalYear.Caption = "Fiscal Year";

            // Add fields to the PivotGridControl
            pivotGridControl1.Fields.AddRange(fieldMeasuresInternetSalesAmount, 
                fieldCustomerCountryCountry, fieldDateFiscalYearFiscalYear);

            pivotGridControl1.EndUpdate();
        }
    }
}