wpf-8015-controls-and-libraries-pivot-grid-binding-to-data-olap-data-source-binding-to-olap-data-sources.md
The PivotGridControl allows you to visualize data contained in a cube deployed on an OLAP server. In OLAP mode, the PivotGridControl delegates data management operations (such as summarization, grouping, etc.) to the server side. You can use OLAP binding mode to process large amounts of data. The following article describes recommendations related to OLAP data sources: Pivot Grid Performance - OLAP.
Refer to the following topic for information about requirements and limitations in OLAP mode: Requirements and Limitations.
Important
You cannot bind the Pivot Grid to data at design time in .NET 5+ projects. Refer to the “Bind Pivot Grid to an OLAP cube in Code” section for information on how to populate the Pivot Grid with data in code.
Refer to the following tutorial for information on how to bind the Pivot Grid to an OLAP cube at design time in .NET Framework WPF applications: Lesson 2 - Bind a Pivot Grid to an OLAP Cube.
AreaIndex can be set only after the field is added to the control’s field collection.Use the PivotGridControl.GetFieldList method to obtain a list of fields that are available in a bound data source, and the PivotGridControl.RetrieveFields method to create PivotGridField objects for all available fields.
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 following example demonstrates how to bind a PivotGridControl to an MS OLAP cube.
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
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>
See Also