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

  • 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

  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

  1. 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...

    Lesson5_InsertDevexpressExtension

  2. 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

  1. 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.

    mvc-pivot-olap-create-helper

    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.

    
    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;"; } }
        }
    }
    

Step 5-6. Change the View Code

  1. 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.

    
    @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()
    
  2. Run the project. As a result, you have a fully functional MVC PivotGrid bound to an OLAP cube.

    mvc-pivot-olap-result