Skip to main content

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.

View Example: Bind a Pivot Grid to an OLAP Cube at Design Time

Create a New Project and Add ASPxPivotGrid

Run Microsoft Visual Studio.

Click Create a new project and select DevExpress v22.2 Web App Template Gallery.

Create a new project in Visual Studio

Select Web Application and click Run Wizard.

Create a new Web Application in DevExpress Template Gallery

In the DevExpress ASP.NET Project Wizard, select Empty on the Choose Layout page.

The Project Wizard

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.

Add WebForm to the project

Drag and drop the ASPxPivotGrid control from the DX.22.2 Data & Analytics toolbox tab onto the page.

Drop the Pivot Grid from the Toolbox

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.

GettingStarted_L1_SmartTag

In this window, click the ellipsis button in the Choose OLAP Data Source field.

GettingStarted_L2_OlapDataSource

This invokes the Connection String Editor window.

GettingStarted_L2_ConnectionStringEditor_Empty

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.

GettingStarted_L2_ConnectionStringEditor_Filled

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.

GettingStarted_L2_Fields

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]

GettingStarted_L2_FieldName

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.

GettingStarted_L2_Fields_Arrange

Click OK.

Result

Run the project to see the result.

GettingStarted_L2_Result

See Also