Bind a Pivot Grid to an OLAP Cube
- 4 minutes to read
A cube is a multidimensional structure defined by its measures and dimensions. A cube’s measure can be bound to a data field in the PivotGridControl, while a dimension can be bound to a column, filter, or row field. This tutorial contains step-by-step instructions on how to bind the Pivot Grid to an existing OLAP (OnLine Analytical Processing) cube.
Create a New Project and Add a Pivot Grid Control
Run Microsoft Visual Studio and create a new Windows Forms App (.NET Framework) project.
Specify the project’s name and location in the New Project wizard.
Drag the PivotGridControl item from the DX.23.2: Data & Analytics toolbox group onto the form.
Note
The control’s PivotGridOptionsData.DataProcessingEngine property is set to Optimized when you drop the Pivot Grid from the toolbox. When the Pivot Grid uses the Optimized calculation engine, data is calculated on the client side, except data from the OLAP and server-mode data sources.
Open the Pivot Grid’s smart tag menu and click Dock in Parent Container to let the Pivot Grid fill the entire window’s client area.
Prepare a Data Source
Click the smart tag icon displayed in the top-right corner of the Pivot Grid to invoke the popup PivotGridControl Tasks window.
Select the Data Source Wizard item.
Select the OLAP Cube technology and click Next in the invoked wizard.
Select ADOMD.NET as the data provider and click Next.
Specify the Server Name as https://demos.devexpress.com/Services/OLAP/msmdpump.dll and select the MSOLAP provider. Then, click the Retrieve Schema button.
Make sure that the following parameters are specified:
Option | Value | Description |
Provider | MSOLAP | Identifies the data provider to be used. The “MSOLAP“ string identifies the latest version of the OLE DB provider. |
Server Name | https://demos.devexpress.com/Services/OLAP/msmdpump.dll | Specifies either the name of a server that runs an instance of Microsoft SQL Server Analysis Services (SSAS), the path to a cube file, or the path to a data pump. |
Catalog Name | Adventure Works DW Standard Edition | Specifies a data catalog that contains cubes. |
Cube Name | Adventure Works | Specifies the name of a cube that supplies OLAP data. |
The image below demonstrates the configured settings of the OLAP connection. Click Finish to close the wizard.
Create and Arrange Pivot Grid Fields
Create fields that correspond to specific measures and dimension levels of the cube in PivotGridControl.
Click the smart tag icon and then click Run Designer… in the popup window.
Use the Add Field button to add Pivot Grid fields and specify their properties in the invoked PivotGrid Designer.
“Country” Field
Create a Pivot Grid field and set its properties to the following values:
- DataBinding
- Value:
Data Source Column - ColumnName
- Value:
[Customer].[Country].[Country] - AreaIndex
- Value:
0 - Area
- Value:
RowArea
“City” Field
Create a Pivot Grid field and set its properties to the following values:
- DataBinding
- Value:
Data Source Column - ColumnName
- Value:
[Customer].[City].[City] - AreaIndex
- Value:
1 - Area
- Value:
RowArea
“Fiscal Year” Field
Create a Pivot Grid field and set its properties to the following values:
- DataBinding
- Value:
Data Source Column - ColumnName
- Value:
[Date].[Fiscal].[Fiscal Year] - AreaIndex
- Value:
0 - Area
- Value:
ColumnArea
“Fiscal Quarter” Field
Create a Pivot Grid field and set its properties to the following values:
- DataBinding
- Value:
Data Source Column - ColumnName
- Value:
[Date].[Fiscal].[Fiscal Quarter] - AreaIndex
- Value:
1 - Area
- Value:
ColumnArea
“Internet Sales Amount” Field
Create a Pivot Grid field and set its properties to the following values:
- DataBinding
- Value:
Data Source Column - ColumnName
- Value:
[Measures].[Internet Sales Amount] - AreaIndex
- Value:
0 - Area
- Value:
DataArea
The image below shows the result.
Close the designer. Pivot grid fields are now arranged as follows:
Result
Run the project to see the result.
