How to: Bind a Pivot Grid to an OLAP Datasource

• Jul 09, 2019
• 3 minutes to read

If you have a cube on the OLAP server (Microsoft Analysis Services), you can view its data using the Pivot Grid. In this example, you will see how to specify connection settings to the server and create fields that represent specific measures and dimensions of the cube.

To bind the Pivot Grid control to an OLAP cube, follow the steps below.

• Set ADOMD as a data provider using the PivotGridControl.OlapDataProvider property.

• Specify connection settings to the server using the PivotGridControl.OlapConnectionString property. The connection string used in the example is shown below.

  Provider=MSOLAP;Data Source=http://demos.devexpress.com/Services/OLAP/msmdpump.dll;Initial catalog=Adventure Works DW Standard Edition;Cube name=Adventure Works;Query Timeout=100;

• Create fields for all the measures and dimension in the bound OLAP cube, and move these fields to the specified area, hiding them. To do this, use the PivotGridControl.RetrieveFields method overload and set the field’s visibility to false.
• Place some of the created fields within corresponding Pivot Grid Control areas and set the visibility of the fields to true, using the PivotGridField.Visible property.

Use the invoked Customization Form to specify the Pivot Grid control’s layout.

To learn more about OLAP Datasources, see OLAP Datasources.

View Example

MainWindow.xaml.vb

Imports System.Windows
Imports DevExpress.Xpf.PivotGrid

Namespace WpfOlapRetrieveFieldsExample
    ''' <summary>
    ''' Interaction logic for MainWindow.xaml
    ''' </summary>
    Partial Public Class MainWindow
        Inherits Window

        Public Sub New()
            InitializeComponent()

            ' Specifies the Olap connection settings.
            pivotGridControl1.OlapDataProvider = OlapDataProvider.Adomd
            pivotGridControl1.OlapConnectionString = "Provider=MSOLAP;" & ControlChars.CrLf & _
"                Data Source=http://demos.devexpress.com/Services/OLAP/msmdpump.dll; " & ControlChars.CrLf & _
"                Initial catalog=Adventure Works DW Standard Edition;" & ControlChars.CrLf & _
"                Cube name=Adventure Works;" & ControlChars.CrLf & _
"                Query Timeout=100;"

            ' Retrieves fields.
            pivotGridControl1.RetrieveFields(FieldArea.ColumnArea, False)

            ' Adds some fields from the Field List to the specified area to create a report.
            pivotGridControl1.Fields("[Customer].[Country].[Country]").Area = FieldArea.RowArea
            pivotGridControl1.Fields("[Customer].[Country].[Country]").Visible = True
            pivotGridControl1.Fields("[Customer].[City].[City]").Area = FieldArea.RowArea
            pivotGridControl1.Fields("[Customer].[City].[City]").Visible = True
            pivotGridControl1.Fields("[Date].[Fiscal].[Fiscal Year]").Area = FieldArea.ColumnArea
            pivotGridControl1.Fields("[Date].[Fiscal].[Fiscal Year]").Visible = True
            pivotGridControl1.Fields("[Measures].[Internet Sales Amount]").Visible = True

            ' Sets the Customization Forms style to Excel2007 with additional capabilities.
            pivotGridControl1.FieldListStyle = FieldListStyle.Excel2007

            ' Invokes the Field List.
            pivotGridControl1.ShowFieldList()
        End Sub
    End Class
End Namespace

MainWindow.xaml.cs

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

namespace WpfOlapRetrieveFieldsExample
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();

            // Specifies the Olap connection settings.
            pivotGridControl1.OlapDataProvider = OlapDataProvider.Adomd;
            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;
                Query Timeout=100;";

            // Retrieves fields.
            pivotGridControl1.RetrieveFields(FieldArea.ColumnArea, false);

            // Adds some fields from the Field List to the specified area to create a report.
            pivotGridControl1.Fields["[Customer].[Country].[Country]"].Area = FieldArea.RowArea;
            pivotGridControl1.Fields["[Customer].[Country].[Country]"].Visible = true;
            pivotGridControl1.Fields["[Customer].[City].[City]"].Area = FieldArea.RowArea;
            pivotGridControl1.Fields["[Customer].[City].[City]"].Visible = true;
            pivotGridControl1.Fields["[Date].[Fiscal].[Fiscal Year]"].Area = FieldArea.ColumnArea;
            pivotGridControl1.Fields["[Date].[Fiscal].[Fiscal Year]"].Visible = true;
            pivotGridControl1.Fields["[Measures].[Internet Sales Amount]"].Visible = true;

            // Sets the Customization Forms style to Excel2007 with additional capabilities.
            pivotGridControl1.FieldListStyle = FieldListStyle.Excel2007;

            // Invokes the Field List.
            pivotGridControl1.ShowFieldList();
        }
    }
}