Bind Pivot Grid Fields to Data Columns

• Jun 15, 2022
• 3 minutes to read

Pivot Grid uses the Binding API to bind Pivot Grid fields to data. Data binding sources can be columns in a data source, calculated expressions, or window calculations. Data binding to columns in the underlying data source is the most common way to obtain data and display it in the Pivot Grid control.

In Legacy and LegacyOptimized modes (before v19.2), Pivot Grid uses the PivotGridFieldBase.FieldName property to bind a data source column to a Pivot Grid field.

Important

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

Optimized and Server Modes

Follow the steps below to bind a Pivot Grid field to a data source column in code:

1. Create a DataSourceColumnBinding instance.
2. Set the DataSourceColumnBinding.ColumnName property to the name of a data source column.
3. Assign the DataSourceColumnBinding object to the PivotGridField.DataBinding property.

The following code snippet illustrates the Pivot Grid’s Product Sales field bound to the Extended Price source column:

MainWindow.xaml

<dxpg:PivotGridControl>
    <dxpg:PivotGridControl.Fields>
      <dxpg:PivotGridField
          Area="DataArea"
          AreaIndex="5"
          Caption="Product Sales"
          CellFormat="C"
          Name="fieldSales">
          <dxpg:PivotGridField.DataBinding>
              <dxpg:DataSourceColumnBinding ColumnName="Extended Price" />
          </dxpg:PivotGridField.DataBinding>
        </dxpg:PivotGridField>
      </dxpg:PivotGridControl.Fields>
</dxpg:PivotGridControl>

OLAP mode

Follow the steps below to bind a Pivot Grid’s field to a measure or dimension in code:

1. Create a DataSourceColumnBinding instance.

2. Specify the DataSourceColumnBinding.ColumnName property. ColumnName must specify the full name of the bound measure or dimension.

   For dimensions, the full name is composed of a dimension name, followed by a hierarchy name, followed by 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].[Customer Geography].[Country]”.

   For measures, the full name is composed of the “[Measures].” string followed by the measure name. Example: “[Measures].[Sales Amount]”.

3. Assign the DataSourceColumnBinding object to the PivotGridField.DataBinding property.

View Example: Pivot Grid for WPF - Bind a PivotGrid to an OLAP Cube

The following code snippet illustrates how to create the fieldMeasuresInternetSalesAmount field and bind it to the Internet Sales Amount measure:

MainWindow.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) {
           // ...
           PivotGridField fieldMeasuresInternetSalesAmount = 
                new PivotGridField("[Measures].[Internet Sales Amount]", FieldArea.DataArea);
            fieldMeasuresInternetSalesAmount.Caption = "Internet Sales Amount";
            pivotGridControl1.Fields.Add(fieldMeasuresInternetSalesAmount); 
         }
    }
}

MainViewModel.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)
           ' ...
           Dim fieldMeasuresInternetSalesAmount As New PivotGridField("[Measures].[Internet Sales Amount]", FieldArea.DataArea)
            fieldMeasuresInternetSalesAmount.Caption = "Internet Sales Amount"
            pivotGridControl1.Fields.Add(fieldMeasuresInternetSalesAmount)
         End Sub
    End Class
End Namespace