Lesson 1 - Binding a Pivot Grid to an OLAP Cube

• May 22, 2020
• 3 minutes to read

A cube is a multidimensional structure defined by its measures and dimensions. You can think of a measure as a data field in PivotGridControl, while a dimension can be represented by a column field or a row field.

This tutorial will help you learn how to add a PivotGridControl to your Silverlight application, bind it to an OLAP source and map its fields to appropriate data fields. In this example, we will bind a pivot grid to an Adventure Works cube.

To display data from an OLAP cube, do the following.

• Steps 1-5. Create a New Project and Add a PivotGridControl
• Step 6. Specify a Pivot Grid Data Source
• Step 7. Create Pivot Grid Fields and Bind Them to Database Fields
• Result

Steps 1-5. Create a New Project and Add a PivotGridControl

1. Run MS Visual Studio.
2. Create a new Silverlight Application project.

3. Add PivotGridControl to your project. You can do this by dragging the PivotGridControl item from the DX.14.2: Data & Analytics toolbox tab.

   

4. Right-click the pivot grid and choose the Reset Layout | All option in the context menu. This will stretch the control to fill the whole window.

   

5. After this, your XAML may look like the following. If it does not, you can overwrite your code with:

   XAML

   
   <UserControl xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
                xmlns:dxpg="http://schemas.devexpress.com/winfx/2008/xaml/pivotgrid"
                xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
                xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
                xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
                x:Class="SilverlightApplication1.MainPage"
                d:DesignHeight="300" d:DesignWidth="400" 
                mc:Ignorable="d">
       <Grid x:Name="LayoutRoot" Background="White">
           <dxpg:PivotGridControl Name="pivotGridControl1" />
       </Grid>
   </UserControl>
   

   Note that you can add a PivotGridControl by overwriting your MainPage.xaml file with this code, without dragging PivotGridControl to the page. However, in this case, you need to manually add references to the following libraries.

   DevExpress.Data.v14.2, DevExpress.Xpf.Core.v14.2, DevExpress.PivotGrid.v14.2.Core, DevExpress.Xpf.PivotGrid.v14.2.

   

   NOTE

   Normally, when adding references to these assemblies, you should choose them from the Global Assembly Cache (GAC). However, if you prefer to copy them locally, or need to include them later into your product's installation, you can find copies in the following directory.

   C:\Program Files (x86)\DevExpress 14.2\Components\Bin\Silverlight

Step 6. Specify Pivot Grid Data Source

6. Assign a string that contains connection parameters (the connection string) to the PivotGridControl.OlapConnectionString property in XAML.

   XAML

   
   <dxpg:PivotGridControl x:Name="pivotGridControl1">
   <!-- ... -->
       <dxpg:PivotGridControl.OlapConnectionString>
           Data Source=http://demos.devexpress.com/Services/OLAP/msmdpump.dll;
           Initial Catalog=Adventure Works DW Standard Edition;
           Cube Name=Adventure Works;
       </dxpg:PivotGridControl.OlapConnectionString>
   <!-- ... -->
   </dxpg:PivotGridControl>
   

   Note that the connection string provides the following parameters.

   • Data Source - the path to the data pump (a service component that supplies data for the PivotGridControl - see Binding to an OLAP Server to learn more).
   • Initial Catalog - a data catalog that contains OLAP cubes.
   • Cube Name - the name of a cube.

Step 7. Create Pivot Grid Fields and Bind Them to Database Fields

7. Finally, you should create the required pivot grid fields and map them to the corresponding data fields to display bound data.

   To do this, open the Properties window and click the ellipsis button in the Fields row to edit the PivotGridControl.Fields collection.

   

   This opens the Field Collection Editor dialog that allows you to add fields to a PivotGridControl. To add a field, click the Add button. Use the Properties pane to adjust the properties of the created field.

   

   In this dialog, add three fields and set their properties to the following values.

   	Area	Caption	FieldName
   1	DataArea	Quantity	[Measures].[Quantity]
   2	ColumnArea	Category Name	[Categories].[Category Name].[Category Name]
   3	RowArea	Country	[Customers].[Country].[Country]

   Click OK to close the dialog.

Result

Run the project and see the result.