Getting Started

13 Jun 202324 minutes to read

Important
Starting with v16.2.0.x, if you refer to Syncfusion assemblies from trial setup or from the NuGet feed, include a license key in your projects. Refer to this link to learn about registering Syncfusion license key in your ASP.NET Core application to use our components.

Creating a simple application with PivotGrid and Relational datasource (Client Mode)

This section covers the information that you need to know to populate a simple PivotGrid with Relational data completely on the client-side.

Project Initialization

Create a new ASP.NET MVC Web Application using Visual Studio IDE and name the project as “PivotGridDemo”.

Select the View engine as ‘Razor’ and Project template as ‘Internet Application’ and finally click OK button to create an application.

Now add the following dependency libraries as references into your MVC Web Application. In order to add them to your application, right-click on References in Solution Explorer and select Add Reference. Now in the Reference Manager dialog, under Assemblies > Extension, the following Syncfusion libraries will be found.

  • Syncfusion.EJ
  • Syncfusion.EJ.Export
  • Syncfusion.EJ.Pivot
  • Syncfusion.EJ.MVC

The version of Syncfusion libraries based on .NET Framework and MVC version are classified below. For example, version is illustrated as,

MVC Version MVC Version of Syncfusion assembly Base Version of Syncfusion assembly System.Web.Mvc System.Web.WebPages
MVC3 24.2300.3 24.2350.3 3.0 1.0
MVC4 24.2400.3 24.2400.3 4.0 2.0
MVC5 24.2500.3 24.2450.3 5.0 3.0

Register the referred assemblies in “Web.config” files available inside Views folder and also at the root of the application.

  • XAML
  • <compilation debug="true" targetFramework="4.0">
        <assemblies>
            ……
            ……
            <add assembly="Syncfusion.EJ, Version= 24.2400.3, Culture=neutral, PublicKeyToken=3d67ed1f87d44c89" />
            <add assembly="Syncfusion.EJ.Pivot, Version= 24.2400.3, Culture=neutral, PublicKeyToken=3d67ed1f87d44c89" />
            <add assembly="Syncfusion.EJ.Exprot, Version= 24.2400.3, Culture=neutral, PublicKeyToken=3d67ed1f87d44c89" />
            <add assembly="Syncfusion.EJ.Mvc, Version= 24.2400.3, Culture=neutral, PublicKeyToken=3d67ed1f87d44c89" />
        </assemblies>
    </compilation>

    Register the required namespaces in “Web.config” files available inside Views folder and also at the root of the application

  • XAML
  • <namespaces>
        ……
        ……
        <add namespace="Syncfusion.MVC.EJ" />
        <add namespace="Syncfusion.JavaScript" />
    </namespaces>

    NOTE

    Registering assemblies and namespaces earlier helps to include the control in view page with the help of intellisense.

    Set the UnobtrusiveJavaScriptEnabled property to false under appSettings tag in “Web.config” file at the root folder.

  • XAML
  • <configuration>
        ……
        ……
        <appSettings>
            ……
            ……
            <add key="UnobtrusiveJavaScriptEnabled" value="false" />
        </appSettings>
    </configuration>

    Scripts and CSS References

    The scripts and style sheets that are mandatorily required to render PivotGrid control in a MVC Web Application are mentioned in an appropriate order below:

    1. ej.web.all.min.css
    2. jQuery-3.0.0.min.js
    3. ej.web.all.min.js

    Scripts and style sheets are referred under the head tag in _Layout.cshtml file which is found inside Views > Shared folder.

  • HTML
  • <head>
        <link href="http://cdn.syncfusion.com/24.2.3/js/web/flat-azure/ej.web.all.min.css" rel="stylesheet" type="text/css" />
        <script src="http://cdn.syncfusion.com/js/assets/external/jquery-3.0.0.min.js" type="text/javascript"></script>
        <script src="http://cdn.syncfusion.com/24.2.3/js/web/ej.web.all.min.js" type="text/javascript"></script>
    
    </head>

    The script manager is initialized immediately after the RenderBody() function call in _Layout.cshtml file in-order to generate control related scripts.

  • CSHTML
  • <body>
        ……
        ……
        @RenderBody()
        @(Html.EJ().ScriptManager())
    </body>

    Initialize PivotGrid

    Before initializing, empty the contents of “Index.cshtml” file under Views > Home folder and add the following codes.

  • HTML
  • @using Syncfusion.JavaScript;
    
    <div>
        @Html.EJ().Pivot().PivotGrid("PivotGrid1")
    </div>

    Populate PivotGrid With Data

    Let us now see how to populate the PivotGrid control using a sample JSON data as shown below.

  • HTML
  • @Html.EJ().Pivot().PivotGrid("PivotGrid1").ClientSideEvents(clientSideEvents => clientSideEvents.Load("onLoad"))
    
    <script type="text/javascript">
    function onLoad(args) {
        args.model.dataSource.data = [
                        { Amount: 100, Country: "Canada", Date: "FY 2005", Product: "Bike", Quantity: 2, State: "Alberta" },
                        { Amount: 200, Country: "Canada", Date: "FY 2006", Product: "Van", Quantity: 3, State: "British Columbia" },
                        { Amount: 300, Country: "Canada", Date: "FY 2007", Product: "Car", Quantity: 4, State: "Brunswick" },
                        { Amount: 150, Country: "Canada", Date: "FY 2008", Product: "Bike", Quantity: 3, State: "Manitoba" },
                        { Amount: 200, Country: "Canada", Date: "FY 2006", Product: "Car", Quantity: 4, State: "Ontario" },
                        { Amount: 100, Country: "Canada", Date: "FY 2007", Product: "Van", Quantity: 1, State: "Quebec" },
                        { Amount: 200, Country: "France", Date: "FY 2005", Product: "Bike", Quantity: 2, State: "Charente-Maritime" },
                        { Amount: 250, Country: "France", Date: "FY 2006", Product: "Van", Quantity: 4, State: "Essonne" },
                        { Amount: 300, Country: "France", Date: "FY 2007", Product: "Car", Quantity: 3, State: "Garonne (Haute)" },
                        { Amount: 150, Country: "France", Date: "FY 2008", Product: "Van", Quantity: 2, State: "Gers" },
                        { Amount: 200, Country: "Germany", Date: "FY 2006", Product: "Van", Quantity: 3, State: "Bayern" },
                        { Amount: 250, Country: "Germany", Date: "FY 2007", Product: "Car", Quantity: 3, State: "Brandenburg" },
                        { Amount: 150, Country: "Germany", Date: "FY 2008", Product: "Car", Quantity: 4, State: "Hamburg" },
                        { Amount: 200, Country: "Germany", Date: "FY 2008", Product: "Bike", Quantity: 4, State: "Hessen" },
                        { Amount: 150, Country: "Germany", Date: "FY 2007", Product: "Van", Quantity: 3, State: "Nordrhein-Westfalen" },
                        { Amount: 100, Country: "Germany", Date: "FY 2005", Product: "Bike", Quantity: 2, State: "Saarland" },
                        { Amount: 150, Country: "United Kingdom", Date: "FY 2008", Product: "Bike", Quantity: 5, State: "England" },
                        { Amount: 250, Country: "United States", Date: "FY 2007", Product: "Car", Quantity: 4, State: "Alabama" },
                        { Amount: 200, Country: "United States", Date: "FY 2005", Product: "Van", Quantity: 4, State: "California" },
                        { Amount: 100, Country: "United States", Date: "FY 2006", Product: "Bike", Quantity: 2, State: "Colorado" },
                        { Amount: 150, Country: "United States", Date: "FY 2008", Product: "Car", Quantity: 3, State: "New Mexico" },
                        { Amount: 200, Country: "United States", Date: "FY 2005", Product: "Bike", Quantity: 4, State: "New York" },
                        { Amount: 250, Country: "United States", Date: "FY 2008", Product: "Car", Quantity: 3, State: "North Carolina" },
                        { Amount: 300, Country: "United States", Date: "FY 2007", Product: "Van", Quantity: 4, State: "South Carolina" }
        ];
    }
    </script>

    The JSON data is set to the “data” property present inside the “DataSource” object. “DataSource” object allows us to set both datasource as well as the fields that needs to be displayed in the row, column, value and filter section of the PivotGrid control.

  • HTML
  • @Html.EJ().Pivot().PivotGrid("PivotGrid1").ClientSideEvents(clientSideEvents => clientSideEvents.Load("onLoad")).DataSource(dataSource => dataSource.Rows(rows => { rows.FieldName("Country").FieldCaption("Country").Add();}).Columns(columns => { columns.FieldName("Product").FieldCaption("Product").Add(); }).Values(values => { values.FieldName("Amount").Add();}))

    The above code will generate a simple PivotGrid with “Country” field in Row, “Product” field in Column, and “Amount” field in Value section.

    Pivot Grid control in ASP NET MVC

    Apply Sorting

    You can sort a field either to ascending or descending order using the “sortOrder” property. Sorting is applicable only for Row and Column fields. By default, fields are arranged in ascending order.

  • HTML
  • @Html.EJ().Pivot().PivotGrid("PivotGrid1").ClientSideEvents(clientSideEvents => clientSideEvents.Load("onLoad")).DataSource(dataSource => dataSource.Rows(rows => {rows.FieldName("Country").FieldCaption("Country").SortOrder(SortOrder.Ascending).Add();}).Columns(columns => { columns.FieldName("Product").FieldCaption("Product").Add(); }).Values(values => { values.FieldName("Amount").Add();}))

    Sorting in ASP NET MVC pivot grid control

    Sort Row/Column by Date

    You can sort a field either in ascending or descending order according for date type by using the SortOrder property. Sorting is applicable only for Row and Column fields. By default, fields are arranged in ascending order.

    NOTE

    To apply sorting by date, you need to specify the Format and FormatString in the field.

  • CSHTML
  • @Html.EJ().Pivot().PivotGrid("PivotGrid1").ClientSideEvents(clientSideEvents => clientSideEvents.Load("onLoad")).DataSource(dataSource => dataSource.Rows(rows => {rows.FieldName("Date").FieldCaption("Date").Format("date").FormatString("dd-MM-yyyy").SortOrder(SortOrder.Descending).Add();}).Columns(columns => { columns.FieldName("Day").FieldCaption("Day").Format("date").FormatString("ddd").SortOrder(SortOrder.Ascending).Add(); }).Values(values => { values.FieldName("Amount").Add();}))
    
    <script type="text/javascript">
    function onLoad(args) {
        args.model.dataSource.data = [
                { Amount: 100, Date: "5-1-2017", Day: "Wednesday" },
                { Amount: 200, Date: "1-2-2017", Day: "Sunday" },
                { Amount: 300, Date: "1-1-2018", Day: "Thursday" },
                { Amount: 150, Date: "5-1-2018", Day: "Wednesday" },
                { Amount: 200, Date: "1-2-2017", Day: "Thursday" },
                { Amount: 100, Date: "1-1-2018", Day: "Sunday" },
                { Amount: 200, Date: "5-1-2017", Day: "Wednesday" },
                { Amount: 250, Date: "1-2-2017", Day: "Sunday" }
                //....
            ];
    }
    </script>

    Sorting by date in ASP NET MVC pivot grid control

    Apply Filtering

    Filtering option allows you to specify a set of values that either need to be displayed or hidden. Also filtering option is applicable only for Row, Column and Filter areas.

    “FilterItems” object allow us to apply filtering to the fields using the following properties:

    • FilterType - indicates whether the values should be included or excluded.
    • Values - specify an array of values that needs to be included or excluded within the particular field.
  • HTML
  • @Html.EJ().Pivot().PivotGrid("PivotGrid1").ClientSideEvents(clientSideEvents => clientSideEvents.Load("onLoad")).DataSource(dataSource => dataSource.Rows(rows => { rows.FieldName("Country").FieldCaption("Country").FilterItems(filter => { filter.FilterType(PivotFilterType.Exclude).Values(value => { value.Add("United Kingdom"); }); }).Add(); }).Columns(columns => { columns.FieldName("Product").FieldCaption("Product").Add(); }).Values(values => { values.FieldName("Amount").Add(); }))

    Filtering in ASP NET MVC pivot grid control

    Apply Summary Types

    Allows us to specify the required summary type that PivotGrid should use in its summary cells. “Sum” is the default summary type. Following are the summary types that are supported:

    • Sum
    • Average
    • Count
    • Min
    • Max
  • HTML
  • @Html.EJ().Pivot().PivotGrid("PivotGrid1").ClientSideEvents(clientSideEvents => clientSideEvents.Load("onLoad")).DataSource(dataSource => dataSource.Rows(rows => { rows.FieldName("Country").FieldCaption("Country").Add(); }).Columns(columns => { columns.FieldName("Product").FieldCaption("Product").Add(); }).Values(values => { values.FieldName("Amount").SummaryType(PivotSummaryType.Average).Add(); values.FieldName("Quantity").FieldCaption("Quantity").SummaryType(PivotSummaryType.TotalSum).Add(); }))

    Summary Types in ASP NET MVC pivot grid control

    Creating a simple application with PivotGrid and Relational datasource (Server Mode)

    This section covers the information required to create a simple PivotGrid bound to Relational datasource.

    NOTE

    ASP.NET MVC Web Application will contain a service that transfers data to server-side, processes and returns back to client-side for control rendering and re-rendering. The service utilized for communication could be either WCF or WebAPI based on user requirement.

    Project Initialization

    Create a new ASP.NET MVC Web Application using Visual Studio IDE and name the project as “PivotGridDemo”.

    Select the View engine as ‘Razor’ and Project template as ‘Internet Application’ and finally click OK button to create an application.

    Now add the following dependency libraries as references into your MVC Web Application. In order to add them to your application, right-click on References in Solution Explorer and select Add Reference. Now in the Reference Manager dialog, under Assemblies > Extension, the following Syncfusion libraries will be found.

    NOTE

    If you have installed any version of Essential Studio, then the location of Syncfusion libraries is [system drive:\Program Files (x86)\Syncfusion\Essential Studio\24.2.3\Assemblies].

    • Syncfusion.Compression.Base
    • Syncfusion.Linq.Base
    • Syncfusion.Olap.Base
    • Syncfusion.PivotAnalysis.Base
    • System.Data.SqlServerCe (Version: 4.0.0.0)
    • Syncfusion.XlsIO.Base
    • Syncfusion.Pdf.Base
    • Syncfusion.DocIO.Base
    • Syncfusion.EJ
    • Syncfusion.EJ.Export
    • Syncfusion.EJ.Pivot
    • Syncfusion.EJ.MVC

    The version of Syncfusion libraries based on .NET Framework and MVC version are classified below. For example, version is illustrated as,

    MVC Version MVC Version of Syncfusion assembly Base Version of Syncfusion assembly System.Web.Mvc System.Web.WebPages
    MVC3 24.2300.3 24.2350.3 3.0 1.0
    MVC4 24.2400.3 24.2400.3 4.0 2.0
    MVC5 24.2500.3 24.2450.3 5.0 3.0

    Register the referenced assemblies in “Web.config” files available inside Views folder and also at the root of the application.

  • XAML
  • <compilation debug="true" targetFramework="4.5">
        <assemblies>
            ……
            ……
            <add assembly="Syncfusion.EJ, Version= 24.2400.3, Culture=neutral, PublicKeyToken=3d67ed1f87d44c89" />
            <add assembly="Syncfusion.EJ.Pivot, Version= 24.2400.3, Culture=neutral, PublicKeyToken=3d67ed1f87d44c89" />
            <add assembly="Syncfusion.EJ.Export, Version= 24.2400.3, Culture=neutral, PublicKeyToken=3d67ed1f87d44c89" />
            <add assembly="Syncfusion.EJ.Mvc, Version= 24.2400.3, Culture=neutral, PublicKeyToken=3d67ed1f87d44c89" />
            <add assembly="Syncfusion.Linq.Base, Version= 24.2400.3, Culture=neutral, PublicKeyToken=3d67ed1f87d44c89" />
            <add assembly="Syncfusion.Olap.Base, Version= 24.2400.3, Culture=neutral, PublicKeyToken=3d67ed1f87d44c89" />
            <add assembly="Syncfusion.Pdf.Base, Version= 24.2400.3, Culture=neutral, PublicKeyToken=3d67ed1f87d44c89" />
            <add assembly="Syncfusion.XlsIO.Base, Version= 24.2400.3, Culture=neutral, PublicKeyToken=3d67ed1f87d44c89" />
            <add assembly="Syncfusion.DocIO.Base, Version= 24.2400.3, Culture=neutral, PublicKeyToken=3d67ed1f87d44c89" />
        </assemblies>
    </compilation>

    Register the required namespaces in “Web.config” files available inside Views folder and also at the root of the application

  • XAML
  • <namespaces>
        ……
        ……
        <add namespace="Syncfusion.MVC.EJ" />
        <add namespace="Syncfusion.JavaScript" />
    </namespaces>

    NOTE

    Registering assemblies and namespaces earlier helps to include the control in view page with the help of intellisense.

    Set the UnobtrusiveJavaScriptEnabled property to false under appSettings tag in “Web.config” file at the root folder.

  • XAML
  • <configuration>
        ……
        ……
        <appSettings>
            ……
            ……
            <add key="UnobtrusiveJavaScriptEnabled" value="false" />
    </appSettings>
    
    </configuration>

    Scripts and CSS Initialization

    The scripts and style sheets that are mandatorily required to render PivotGrid control in a MVC Web Application are mentioned in an appropriate order below:

    1. ej.web.all.min.css
    2. jQuery-3.0.0.min.js
    3. ej.web.all.min.js

    Click here here to know more about scripts and style sheets available online (CDN Link).

    Scripts and style sheets are referred under the <head> tag in _Layout.cshtml file which is found inside Views > Shared folder.

  • HTML
  • <head>
        <link href="http://cdn.syncfusion.com/24.2.3/js/web/flat-azure/ej.web.all.min.css" rel="stylesheet" type="text/css" />
        <script src="http://cdn.syncfusion.com/js/assets/external/jquery-3.0.0.min.js" type="text/javascript"></script>
        <script src="http://cdn.syncfusion.com/24.2.3/js/web/ej.web.all.min.js" type="text/javascript"></script>
    </head>

    The script manager is initialized immediately after the RenderBody() function call in _Layout.cshtml file in-order to generate control related scripts.

  • HTML
  • <body>
        ……
        ……
        @RenderBody()
        @(Html.EJ().ScriptManager())
    
    </body>

    Control Initialization

    Before initializing, empty the contents of Index.cshtml file under Views > Home folder and add the following codes. Register the namespaces at the top of the page and then add the control.

  • HTML
  • @using Syncfusion.JavaScript;
    
    <div>
        @Html.EJ().Pivot().PivotGrid("PivotGrid1").Url(Url.Content("/Relational"))
    </div>

    The “Url” property in PivotGrid control points the service endpoint, where data are processed and fetched in the form of JSON. The services used in PivotGrid control as endpoint are WCF and WebAPI.

    NOTE

    The above “Index.cshtml” contains WebAPI URL, which is “/Relational”. If WCF service is used as endpoint, the URL would look like “/RelationalService.svc”.

    WebAPI

    Adding a WebAPI Controller

    To add a WebAPI controller in an existing MVC Web Application, right-click on the project in Solution Explorer and select Add > New Item. In the Add New Item window, select WebAPI Controller Class and name it as “RelationalController.cs”, click Add.

    Now, WebAPI controller is added to the application successfully with the file “RelationalController.cs”.

    NOTE

    While adding WebAPI Controller Class, name it with the suffix ‘Controller’ which is mandatory. For example, in this demo the controller is named as “RelationalController”.

    Next, remove all the existing methods such as “Get”, “Post”, “Put” and “Delete” present inside RelationalController.cs file.

  • C#
  • namespace PivotGridDemo
    {
        public class RelationalController: ApiController
        {
    
        }
    }

    Adding the List of Namespaces

    The following are the list of namespaces to be added on top of the main class inside RelationalController.cs file.

  • C#
  • using Syncfusion.JavaScript;
    using Syncfusion.PivotAnalysis.Base;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlServerCe;
    using System.Linq;
    using System.Text;
    using System.Web;
    using System.Web.Http;
    using System.Web.Script.Serialization;
    using OLAPUTILS = Syncfusion.JavaScript.Olap;
    
    namespace PivotGridDemo
    {
        public class RelationalController : ApiController
        {
    
        }
    }

    Datasource Initialization

    Now, the connection string to connect OLAP Cube, PivotGrid and JavaScriptSerializer instances are created immediately inside the main class in RelationalController.cs file.

  • C#
  • namespace PivotGridDemo {
        …………
        …………
        internal class ProductSales {
            public string Product {
                get;
                set;
            }
    
            public string Date {
                get;
                set;
            }
    
            public string Country {
                get;
                set;
            }
    
            public string State {
                get;
                set;
            }
    
            public int Quantity {
                get;
                set;
            }
    
            public double Amount {
                get;
                set;
            }
    
            public static ProductSalesCollection GetSalesData() {
                /// Geography
                string[] countries = new string[] {
                    "Australia",
                    "Canada",
                    "France",
                    "Germany",
                    "United Kingdom",
                    "United States"
                };
                string[] ausStates = new string[] {
                    "New South Wales",
                    "Queensland",
                    "South Australia",
                    "Tasmania",
                    "Victoria"
                };
                string[] canadaStates = new string[] {
                    "Alberta",
                    "British Columbia",
                    "Brunswick",
                    "Manitoba",
                    "Ontario",
                    "Quebec"
                };
                string[] franceStates = new string[] {
                    "Charente-Maritime",
                    "Essonne",
                    "Garonne (Haute)",
                    "Gers",
                };
                string[] germanyStates = new string[] {
                    "Bayern",
                    "Brandenburg",
                    "Hamburg",
                    "Hessen",
                    "Nordrhein-Westfalen",
                    "Saarland"
                };
                string[] ukStates = new string[] {
                    "England"
                };
                string[] ussStates = new string[] {
                    "New York",
                    "North Carolina",
                    "Alabama",
                    "California",
                    "Colorado",
                    "New Mexico",
                    "South Carolina"
                };
    
                /// Time
                string[] dates = new string[] {
                    "FY 2005",
                    "FY 2006",
                    "FY 2007",
                    "FY 2008",
                    "FY 2009"
                };
    
                /// Products
                string[] products = new string[] {
                    "Bike",
                    "Van",
                    "Car"
                };
                Random r = new Random(123345345);
    
                int numberOfRecords = 2000;
                ProductSalesCollection listOfProductSales = new ProductSalesCollection();
                for (int i = 0; i < numberOfRecords; i++) {
                    ProductSales sales = new ProductSales();
                    sales.Country = countries[r.Next(1, countries.GetLength(0))];
                    sales.Quantity = r.Next(1, 12);
                    /// 1 percent discount for 1 quantity
                    double discount = (30000 * sales.Quantity) * (double.Parse(sales.Quantity.ToString()) / 100);
                    sales.Amount = (30000 * sales.Quantity) - discount;
                    sales.Date = dates[r.Next(r.Next(dates.GetLength(0) + 1))];
                    sales.Product = products[r.Next(r.Next(products.GetLength(0) + 1))];
                    switch (sales.Product) {
                        case "Car":
                            {
                                sales.Date = "FY 2005";
                                break;
                            }
                    }
                    switch (sales.Country) {
                        case "Australia":
                            {
                                sales.State = ausStates[r.Next(ausStates.GetLength(0))];
                                break;
                            }
                        case "Canada":
                            {
                                sales.State = canadaStates[r.Next(canadaStates.GetLength(0))];
                                break;
                            }
                        case "France":
                            {
                                sales.State = franceStates[r.Next(franceStates.GetLength(0))];
                                break;
                            }
                        case "Germany":
                            {
                                sales.State = germanyStates[r.Next(germanyStates.GetLength(0))];
                                break;
                            }
                        case "United Kingdom":
                            {
                                sales.State = ukStates[r.Next(ukStates.GetLength(0))];
                                break;
                            }
                        case "United States":
                            {
                                sales.State = ussStates[r.Next(ussStates.GetLength(0))];
                                break;
                            }
                    }
                    listOfProductSales.Add(sales);
                }
    
                return listOfProductSales;
            }
    
            public override string ToString() {
                return string.Format("{0}-{1}-{2}", this.Country, this.State, this.Product);
            }
    
            public class ProductSalesCollection: List < ProductSales > {}
        }
    }

    Service methods in WebAPI Controller

    Define the service methods inside RelationalController class, found inside RelationalController.cs file, created while adding WebAPI Controller Class to the Application.

  • C#
  • namespace PivotGridDemo
    {
        public class RelationalController: ApiController
        {
            PivotGrid htmlHelper = new PivotGrid();
            JavaScriptSerializer serializer = new JavaScriptSerializer();
            Dictionary<string, object> dict = new Dictionary<string, object>();
            static int cultureIDInfoval = 1033;
            string connectionString = "Data Source=https://bi.syncfusion.com/olap/msmdpump.dll; Initial Catalog=Adventure Works DW 2008 SE;";
            string conStringforDB = ""; //Enter appropriate connection string to connect database for saving and loading operation of reports
    
            [System.Web.Http.ActionName("InitializeGrid")]
            [System.Web.Http.HttpPost]
            public Dictionary<string, object> InitializeGrid(Dictionary<string, object> jsonResult)
            {
                htmlHelper.PivotReport = BindDefaultData();
                dict = htmlHelper.GetJsonData(jsonResult["action"].ToString(), ProductSales.GetSalesData());
                return dict;
            }
    
            [System.Web.Http.ActionName("FetchMembers")]
            [System.Web.Http.HttpPost]
            public Dictionary<string, object> FetchMembers(Dictionary<string, object> jsonResult)
            {
                htmlHelper.PopulateData(jsonResult["currentReport"].ToString());
                dict = htmlHelper.GetJsonData(jsonResult["action"].ToString(), ProductSales.GetSalesData(), jsonResult["headerTag"].ToString(), jsonResult["sortedHeaders"].ToString());
                return dict;
            }
    
            [System.Web.Http.ActionName("Filtering")]
            [System.Web.Http.HttpPost]
            public Dictionary<string, object> Filtering(Dictionary<string, object> jsonResult)
            {
                htmlHelper.PopulateData(jsonResult["currentReport"].ToString());
                dict = htmlHelper.GetJsonData(jsonResult["action"].ToString(), ProductSales.GetSalesData(), jsonResult["filterParams"].ToString(), jsonResult["sortedHeaders"].ToString());
                return dict;
            }
    
            [System.Web.Http.ActionName("NodeStateModified")]
            [System.Web.Http.HttpPost]
            public Dictionary<string, object> NodeStateModified(Dictionary<string, object> jsonResult)
            {
                htmlHelper.PopulateData(jsonResult["currentReport"].ToString());
                dict = htmlHelper.GetJsonData(jsonResult["action"].ToString(), ProductSales.GetSalesData(), jsonResult["headerTag"].ToString(), jsonResult["dropAxis"].ToString(), jsonResult["filterParams"].ToString(), jsonResult["sortedHeaders"].ToString());
                return dict;
            }
    
            [System.Web.Http.ActionName("NodeDropped")]
            [System.Web.Http.HttpPost]
            public Dictionary<string, object> NodeDropped(Dictionary<string, object> jsonResult)
            {
                htmlHelper.PopulateData(jsonResult["currentReport"].ToString());
                dict = htmlHelper.GetJsonData(jsonResult["action"].ToString(), ProductSales.GetSalesData(), jsonResult["dropAxis"].ToString(), jsonResult["headerTag"].ToString(), jsonResult.ContainsKey("filterParams") ? jsonResult["filterParams"].ToString() : null, jsonResult["sortedHeaders"].ToString());
                return dict;
            }
    
            [System.Web.Http.ActionName("Sorting")]
            [System.Web.Http.HttpPost]
            public Dictionary<string, object> Sorting(Dictionary<string, object> jsonResult)
            {
                htmlHelper.PopulateData(jsonResult["currentReport"].ToString());
                dict = htmlHelper.GetJsonData(jsonResult["action"].ToString(), ProductSales.GetSalesData(), jsonResult["sortedHeaders"].ToString());
                return dict;
            }
    
            [System.Web.Http.ActionName("CalculatedField")]
            [System.Web.Http.HttpPost]
            public Dictionary<string, object> CalculatedField(Dictionary<string, object> jsonResult)
            {
                htmlHelper.PopulateData(jsonResult["currentReport"].ToString());
                dict = htmlHelper.GetJsonData(jsonResult["action"].ToString(), ProductSales.GetSalesData(), null, jsonResult["headerTag"].ToString());
                return dict;
            }
    
            [System.Web.Http.ActionName("Export")]
            [System.Web.Http.HttpPost]
            public void Export()
            {
                string args = HttpContext.Current.Request.Form.GetValues(0)[0];
                Dictionary<string, string> gridParams = serializer.Deserialize<Dictionary<string, string>>(args);
                htmlHelper.PopulateData(gridParams["currentReport"]);
                string fileName = "Sample";
                htmlHelper.ExportPivotGrid(ProductSales.GetSalesData(), args, fileName, System.Web.HttpContext.Current.Response);
            }
    
            [System.Web.Http.ActionName("SaveReport")]
            [System.Web.Http.HttpPost]
            public Dictionary<string, object> SaveReport(Dictionary<string, object> jsonResult)
            {
                string mode = jsonResult["operationalMode"].ToString();
                bool isDuplicate = true;
                SqlCeConnection con = new SqlCeConnection() { ConnectionString = conStringforDB };
                con.Open();
                SqlCeCommand cmd1 = null;
                foreach (DataRow row in GetDataTable().Rows)
                {
                    if ((row.ItemArray[0] as string).Equals(jsonResult["reportName"].ToString()))
                    {
                        isDuplicate = false;
                        cmd1 = new SqlCeCommand("update ReportsTable set Report=@Reports where ReportName like @ReportName", con);
                    }
                }
                if (isDuplicate)
                {
                    cmd1 = new SqlCeCommand("insert into ReportsTable Values(@ReportName,@Reports)", con);
                }
                cmd1.Parameters.Add("@ReportName", jsonResult["reportName"].ToString());
                if (mode == "serverMode")
                    cmd1.Parameters.Add("@Reports", OLAPUTILS.Utils.GetReportStream(jsonResult["clientReports"].ToString()).ToArray());
                else if (mode == "clientMode")
                    cmd1.Parameters.Add("@Reports", Encoding.UTF8.GetBytes(jsonResult["clientReports"].ToString()).ToArray());
                cmd1.ExecuteNonQuery();
                con.Close();
                return null;
            }
    
            [System.Web.Http.ActionName("LoadReportFromDB")]
            [System.Web.Http.HttpPost]
            public Dictionary<string, object> LoadReportFromDB(Dictionary<string, object> jsonResult)
            {
                byte[] reportString = new byte[2 * 1024];
                PivotReport report = new PivotReport();
                var reports = "";
                string mode = jsonResult["operationalMode"].ToString();
                Dictionary<string, object> dictionary = new Dictionary<string, object>();
                if (mode == "serverMode" && jsonResult.ContainsKey("clientReports"))
                {
                    reports = jsonResult["clientReports"].ToString();
                }
                else
                {
                    foreach (DataRow row in GetDataTable().Rows)
                    {
                        if ((row.ItemArray[0] as string).Equals(jsonResult["reportName"].ToString()))
                        {
                            if (mode == "clientMode")
                            {
                                reportString = (row.ItemArray[1] as byte[]);
                                dictionary.Add("report", Encoding.UTF8.GetString(reportString));
                                break;
                            }
                            else if (mode == "serverMode")
                            {
                                reports = OLAPUTILS.Utils.CompressData(row.ItemArray[1] as byte[]);
                                break;
                            }
                        }
                    }
                }
                if (reports != "")
                {
                    report = htmlHelper.DeserializedReports(reports);
                    htmlHelper.PivotReport = report;
                    dictionary = htmlHelper.GetJsonData("loadOperation", ProductSales.GetSalesData(), "Load Report", jsonResult["reportName"].ToString());
                }
                return dictionary;
            }
    
    
            private DataTable GetDataTable()
            {
                SqlCeConnection con = new SqlCeConnection() { ConnectionString = conStringforDB };
                con.Open();
                DataSet dSet = new DataSet();
                new SqlCeDataAdapter("Select * from ReportsTable", con).Fill(dSet);
                con.Close();
                return dSet.Tables[0];
            }
    
    
            [System.Web.Http.ActionName("DeferUpdate")]
            [System.Web.Http.HttpPost]
            public Dictionary<string, object> DeferUpdate(Dictionary<string, object> jsonResult)
            {
                htmlHelper.PopulateData(jsonResult["currentReport"].ToString());
                dict = htmlHelper.GetJsonData(jsonResult["action"].ToString(), ProductSales.GetSalesData(), null, null, null, jsonResult["sortedHeaders"].ToString(), jsonResult["filterParams"].ToString());
                return dict;
            }
    
            [System.Web.Http.ActionName("CellEditing")]
            [System.Web.Http.HttpPost]
            public Dictionary<string, object> CellEditing(Dictionary<string, object> jsonResult)
            {
                htmlHelper.PopulateData(jsonResult["currentReport"].ToString());
                dict = htmlHelper.GetJsonData(jsonResult["action"].ToString(), ProductSales.GetSalesData(), jsonResult["index"].ToString(), jsonResult["summaryValues"].ToString(), jsonResult["valueHeaders"].ToString());
                return dict;
            }
    
            private PivotReport BindDefaultData()
            {
                PivotReport pivotSetting = new PivotReport();
                pivotSetting.PivotRows.Add(new PivotItem { FieldMappingName = "Product", FieldHeader = "Product", TotalHeader = "Total" });
                pivotSetting.PivotColumns.Add(new PivotItem { FieldMappingName = "Country", FieldHeader = "Country", TotalHeader = "Total" });
                pivotSetting.PivotCalculations.Add(new PivotComputationInfo { CalculationName = "Amount", Description = "Amount", FieldHeader = "Amount", FieldName = "Amount", Format = "C", SummaryType = Syncfusion.PivotAnalysis.Base.SummaryType.DoubleTotalSum });
                return pivotSetting;
            }
        }
            .....
            ..... // Initialize the datasource
            .....
    }

    Configure routing in WebAPIConfig Class

    Open the “WebAPIConfig.cs” file found in App_Start folder. Then routing could be configured as shown in the following code example.

  • C#
  • public static class WebApiConfig
    {
        public static void Register(HttpConfiguration config)
        {
            config.Routes.MapHttpRoute(
                name: "DefaultApi",
                routeTemplate: "{controller}/{action}/{id}",
                defaults: new { id = RouteParameter.Optional }
            );
        }
    }

    Now, PivotGrid will be rendered with Sales Amount over a set of products across different customer geographic locations.

    Pivot grid control in ASP NET MVC relational server mode

    WCF

    This section demonstrates the utilization of WCF service as endpoint binding Relational datasource to a simple PivotGrid. For more details on this topic, click here.