How to: Bind a PivotGridControl to an OLAP Cube Using the OLE DB Data Provider
- 4 minutes to read
The following example shows how to view data from an OLAP server (MS SQL Server 2008 R2 Analysis Services) in the PivotGridControl.
Prerequisites
In the example, the Pivot Grid Control is bound to an Adventure Works cube in the MS SQL Server 2008 R2 Analysis Services (SSAS). It’s assumed that you have the standard AdventureWorksDW (data warehouse) database installed and the Adventure Works cube was deployed to your instance of MS SQL Server 2008 R2. By default, this database and the cube are not installed.
Displaying Data in the Pivot Grid Control - Design-Time Example
The steps below show how to view data from the Adventure Works cube in the PivotGridControl.
- Create a new Windows Forms application project in Visual Studio.
- Place the PivotGridControl on the main form and select it in the Properties window.
- In the Properties window, focus the PivotGridControl.OLAPConnectionString property and click the ellipsis (…) button. This will invoke the Connection String Editor.
In the editor, specify the connection settings to the cube. First, specify a data provider to be used (MSOLAP). Specify the server name that runs the required instance of SSAS (type “localhost” if the local MS SQL Server 2008 R2 is used). Then, click the dropdown button in the Catalog Name edit box. This should list the names of all data catalogs that exist on the specified instance of SSAS. Select the “Adventure Works DW Standard Edition” item.
Click the dropdown button in the Cube Name edit box to open the list of available cubes in the specified catalog. Select the “Adventure Works” cube.
Click the OK button to submit the connection settings.
- Run the PivotGrid Designer by right-clicking on the control and selecting the Run Designer option. The Fields page will be opened.
- Ensure that the Field List pane that displays all available fields in the bound cube is visible. If the pane is hidden, click the Show Field List button at the top of the designer.
Locate the “[Measures].[Internet Sales Amount]” field in the Field List pane. This field will be represented by a Data Field in the PivotGridControl.
To create a PivotGridField object for this field, drag the “[Measures].[Internet Sales Amount]” item onto the PivotGrid Fields pane (or simply double-click it within the Field List pane).
To position the created field within the Data Header Area, set the field’s PivotGridFieldBase.Area property to the PivotArea.DataArea value in the Properties grid.
Locate the “[Customer].[Country].[Country]” field in the Field List pane. This field will be represented by a Row Field in the PivotGridControl.
Drag this field onto the PivotGrid Fields list and set the created PivotGridField’s PivotGridFieldBase.Area property to the PivotArea.RowArea value.
Locate the “[Date].[Fiscal Year].[Fiscal Year]” field in the Field List pane. This field will be represented by a Column Field in the PivotGridControl.
Drag this field onto the PivotGrid Fields list. The created PivotGridField’s PivotGridFieldBase.Area property will be already set to the PivotArea.ColumnArea value. Leave this value unchanged.
Close the designer. The Pivot Grid Control will display three fields in the Data, Column and Row areas respectively.
Run the project. The form will look like the image below, showing data retrieved from the Adventure Works cube.
Displaying Data in Pivot Grid Control - Runtime Example
The following code shows how to view data from the Adventure Works cube at runtime. It’s equivalent to the design-time steps from the previous section.
using DevExpress.XtraPivotGrid;
// Create a PivotGridControl.
Pivot Grid Control pivotControl = new PivotGridControl();
pivotControl.Dock = DockStyle.Fill;
this.Controls.Add(pivotControl);
// Specify the connection string
pivotControl.OLAPConnectionString = "Provider=msolap;Data Source=localhost;Initial Catalog=Adventure Works DW Standard Edition;Cube Name=Adventure Works;";
// Create fields.
PivotGridField fieldMeasuresInternetSalesAmount = new PivotGridField("[Measures].[Internet Sales Amount]", PivotArea.DataArea);
fieldMeasuresInternetSalesAmount.Caption = "Internet Sales Amount";
PivotGridField fieldCustomerCountryCountry = new PivotGridField("[Customer].[Country].[Country]", PivotArea.RowArea);
fieldCustomerCountryCountry.Caption = "Country";
PivotGridField fieldDateFiscalYearFiscalYear = new PivotGridField("[Date].[Fiscal Year].[Fiscal Year]", PivotArea.ColumnArea);
fieldDateFiscalYearFiscalYear.Caption = "Fiscal Year";
// Add fields to the PivotGridControl
pivotControl.Fields.AddRange(new PivotGridField[] { fieldMeasuresInternetSalesAmount, fieldCustomerCountryCountry,
fieldDateFiscalYearFiscalYear });