Lesson 2 - Bind a Pivot Grid to an OLAP Cube
- 3 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 ASPxPivotGrid, 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. We use the Adventure Works cube in this tutorial.
Create a New Project and Add ASPxPivotGrid
Run Microsoft Visual Studio.
Click Create a new project and select DevExpress v23.2 Web App Template Gallery.
Select Web Application and click Run Wizard.
In the DevExpress ASP.NET Project Wizard, select Empty on the Choose Layout page.
Click Create Project.
Right-click the project in the Solution Explorer and select Add | Web Form from the context menu. In the invoked dialog, leave the default item name and click OK.
Drag and drop the ASPxPivotGrid control from the DX.23.2 Data & Analytics toolbox tab onto the page.
Prepare a Data Source
Click the smart tag icon displayed at the top-right of the Pivot Grid to invoke the ASPxPivotGrid Tasks popup window.
In this window, click the ellipsis button in the Choose OLAP Data Source field.
This invokes the Connection String Editor window.
Specify the following parameters in the Connection String Editor:
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 provides OLAP data. |
Click OK to save the connection parameters.
Create and Arrange Pivot Grid Fields
In ASPxPivotGrid, create fields that are bound to specific measures and dimension levels of the cube.
Click the smart tag and then click the Designer… item. This invokes the ASPxPivotGrid Designer.
Click the Retrieve Fields button to retrieve fields to Pivot Grid areas. Select the following fields from the drop-down list:
- [Customer].[City].[City]
- [Customer].[Country].[Country]
- [Date].[Fiscal].[Fiscal Year]
- [Date].[Fiscal].[Fiscal Quarter]
- [Measures].[Internet Sales Amount]
Use drag-and-drop to move the fields between areas. You can also specify the field’s location and position between areas in the Property pane (for this, use the Area and AreaIndex properties).
Place the Country and City fields (in this order) into the Row Header Area, and the Date.Fiscal group into the Column Header Area.
Click OK.
Result
Run the project to see the result.
