Pivot Grid for WPF - How to Connect a Pivot Grid to an OLAP Data Source

The Pivot Grid can display data from the the OLAP server (Microsoft Analysis Services). This example shows how to specify connection settings to the server and create fields that relate to specific measures and dimensions of the cube.

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

1. Specify connection settings to the server. You can do it in the Items Source Wizard. The following code shows the resulting XML:

   <!-- xmlns:dx="http://schemas.devexpress.com/winfx/2008/xaml/core" -->
    <Window.Resources>
        <dx:PivotOlapDataSource x:Key="PivotOlapDataSource" Cube="Adventure Works" 
                                Catalog="Adventure Works DW Standard Edition" ConnectionTimeout="60" 
                                LocaleIdentifier="1033" Password="{x:Null}" Provider="MSOLAP" 
                                QueryTimeout="30" Server="http://demos.devexpress.com/Services/OLAP/msmdpump.dll" 
                                UserId="{x:Null}">
        </dx:PivotOlapDataSource>
    </Window.Resources>

2. Bind the Pivot Grid to data. Set the PivotGridControl.OlapDataProvider to ADOMD and assign the connection string to the PivotGridControl.OlapConnectionString property.

   <!-- xmlns:dxpg="http://schemas.devexpress.com/winfx/2008/xaml/pivotgrid" -->
    <Grid>
        <dxpg:PivotGridControl Name="pivotGridControl1" RowTreeMinWidth="170"
                               OlapDataProvider="Adomd" 
                               OlapConnectionString="{Binding ConnectionString, Source={StaticResource PivotOlapDataSource}}" />
    </Grid>

3. Create fields for all the measures and dimension in the bound OLAP cube, and move these fields to the specified area to make them hidden. To do it, use the PivotGridControl.RetrieveFields method overload with the visible parameter and set the field's visibility to false. The RetrieveFields method generates DataSourceColumnBinding objects for each Pivot Grid field in OLAP, Server, and Optimized modes.

   public MainWindow() {
         InitializeComponent();
   
         // Retrieves fields.
         pivotGridControl1.RetrieveFields(FieldArea.ColumnArea, false);
   }

4. Place the created fields within corresponding Pivot Grid Control areas and set their PivotGridField.Visible property to true.

   public MainWindow() {
      // ...
   
      // 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;
   }

   Use the invoked Customization Form to manage the Pivot Grid control's layout.

Files to Review

• MainWindow.xaml.cs (VB: MainWindow.xaml.vb)

Documentation

• Pivot Grid Data Processing Modes
• Bind a Pivot Grid Control to an OLAP Data Source
• Bind Pivot Grid Fields to Calculated Expressions

Does this example address your development requirements/objectives?

(you will be redirected to DevExpress.com to submit your response)