Lesson 2 - Bind a Pivot Grid to an OLAP Cube (.NET)
- 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.
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.23.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:
<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" DataProcessingEngine="Optimized"/> </Grid> </Window>
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.
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;";
// ...
}
}
}
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.
AreaIndexcan be set only after the field is added to the control’s field collection.
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();
}
// ...
}
}
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.
ColumnNamemust specify the full name of the bound measure or dimension.For dimensions, the full name is composed of a dimension name, a hierarchy name, and the name of a level(s). All names should be wrapped within square brackets and separated from one another with the dot symbol. Example:
[Customer].[Country].[Country].For measures, the full name is composed of the “[Measures].” string followed by the measure name. Example:
[Measures].[Internet Sales Amount].Assign the
DataSourceColumnBindingobject to the PivotGridField.DataBinding property.
The code below shows how to bind the created fieldMeasuresInternetSalesAmount field to data.
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();
}
// ...
}
}
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
OlapExpressionBindingobject to the PivotGridField.DataBinding property.
The code below shows how to bind the created fieldSales field to the MDX expression.
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();
}
// ...
}
}
Result
The resulting code appears as follows:
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;
}
}
}
<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.
