Lesson 2 - Bind a Pivot Grid to an OLAP Data Source

• Dec 17, 2020
• 3 minutes to read

This topic describes how to connect the PivotGrid extension to an OLAP data source - an Analysis Services server or a local cube file.

To bind the PivotGrid to an OLAP data source, follow the steps below.

• Step 1. Create an ASP.NET MVC Application
• Step 2-3. Add the PivotGrid Extension to the MVC Application
• Step 4. Create a Connection String
• Step 5-6. Change the View Code

Step 1. Create an ASP.NET MVC Application

1. To learn how to create an ASP.NET MVC Application, see steps 1-6 from Lesson 1 - Bind MVCxPivotGrid to Microsoft SQL Server Database File.

   Note

   You can add the PivotGrid extension manually. To learn more, see Integration into the Project.

Step 2-3. Add the PivotGrid Extension to the MVC Application

2. Add the PivotGrid extension to your project using the Insert DevExpress MVC Extension Wizard. For this, open a required View file (Views | Home | Index.cshtml in this topic), right-click the desired location to display the menu, and click Insert DevExpress MVC Extension…

   

3. In the invoked dialog, go to the Data tab, select PivotGrid and click Insert. The wizard automatically generates the View code for the extension and inserts it into the View at the cursor position.

Step 4. Create a Connection String

4. In the project, create the Helpers folder, right-click it and add a new PivotGridOLAPHelper.cs helper class that will provide access to the connection string.

   

   Specify an OLAP connection string in this class by creating a new OLAPConnectionString public static property.

   In this tutorial, the following string is used to connect to OLAP.

   "Data Source=https://demos.devexpress.com/Services/OLAP/msmdpump.dll;Initial Catalog=Adventure Works DW Standard Edition;Cube Name=Adventure Works;"

   The code snippet below demonstrates the created helper class.

   C#

   namespace DXWebApplication1.Helpers {
       public class PivotGridOLAPHelper {
           public static string OLAPConnectionString { get { return "Data Source=https://demos.devexpress.com/Services/OLAP/msmdpump.dll;Initial Catalog=Adventure Works DW Standard Edition;Cube Name=Adventure Works;"; } }
       }
   }
   

   VB.NET

   Namespace DXWebApplication1.Helpers
       Public Class PivotGridOLAPHelper
           Public Shared ReadOnly Property OLAPConnectionString() As String
               Get
                   Return "Data Source=https://demos.devexpress.com/Services/OLAP/msmdpump.dll;Initial Catalog=Adventure Works DW Standard Edition;Cube Name=Adventure Works;"
               End Get
           End Property
       End Class
   End Namespace
   

Step 5-6. Change the View Code

5. In the pivot grid’s generated View code (Views | Home | _PivotGridPartial.cshtml), use the PivotGridExtension.BindToOLAP method with the specified OLAP connection string from the PivotGridOLAPHelper class. This enables binding to an OLAP data source. After that, add and configure pivot grid fields.

   The table below illustrates the PivotGrid fields and their settings.

   PivotGridFieldBase.FieldName	PivotGridFieldBase.Area	PivotGridFieldBase.AreaIndex
   [Ship Date].[Calendar Year].[Calendar Year]	PivotArea.RowArea	0
   [Customer].[City].[City]	PivotArea.ColumnArea	0
   [Sales Territory].[Sales Territory].[Country]	PivotArea.FilterArea	0
   [Measures].[Order Count]	PivotArea.DataArea	0

   After these manipulations, your Partial View code (_PivotGridPartial.cshtml) will look as follows.

   C#

   @using DXWebApplication1.Helpers
   @Html.DevExpress().PivotGrid(settings => {
       settings.Name = "PivotGridOlap";
       settings.CallbackRouteValues = new { Controller = "Home", Action = "PivotGridOLAPPartial" };
   
       settings.Fields.Add(field => {
           field.Area = PivotArea.RowArea;
           field.AreaIndex = 0;
           field.FieldName = "[Ship Date].[Calendar Year].[Calendar Year]";
       });
       settings.Fields.Add(field => {
           field.Area = PivotArea.ColumnArea;
           field.AreaIndex = 0;
           field.FieldName = "[Customer].[City].[City]";
       });
       settings.Fields.Add(field => {
           field.Area = PivotArea.FilterArea;
           field.AreaIndex = 0;
           field.FieldName = "[Sales Territory].[Sales Territory].[Country]";
       });
       settings.Fields.Add(field => {
           field.Area = PivotArea.DataArea;
           field.AreaIndex = 1;
           field.FieldName = "[Measures].[Order Count]";
       });
   
   }).BindToOLAP(PivotGridOLAPHelper.OLAPConnectionString).GetHtml()
   

   VB.NET

   @Html.DevExpress().PivotGrid(Sub(settings)
       settings.Name = "PivotGrid"
       settings.CallbackRouteValues = New With {Key.Controller = "Home", Key.Action = "PivotGridPartial"}
   
           settings.Fields.Add(Sub(field)
           field.Area = PivotArea.RowArea;
           field.AreaIndex = 0
           field.FieldName = "[Ship Date].[Calendar Year].[Calendar Year]";
       End Sub)
           settings.Fields.Add(Sub(field)
           field.Area = PivotArea.ColumnArea;
           field.AreaIndex = 0;
           field.FieldName = "[Customer].[City].[City]";
       End Sub)
           settings.Fields.Add(Sub(field)
           field.Area = PivotArea.FilterArea;
           field.AreaIndex = 0;
           field.FieldName = "[Sales Territory].[Sales Territory].[Country]";
       End Sub)
           settings.Fields.Add(Sub(field)
           field.Area = PivotArea.DataArea;
           field.AreaIndex = 1;
           field.FieldName = "[Measures].[Order Count]";
       End Sub)
   End Sub).BindToOLAP(PivotGridOLAPHelper.OLAPConnectionString).GetHtml()
   

6. Run the project. As a result, you have a fully functional MVC PivotGrid bound to an OLAP cube.