Getting Started with JQuery PivotGauge widget

Creating a simple application with pivot gauge and OLAP data sources (client mode)

This section covers the information required to populate a simple pivot gauge with the OLAP data completely on the client-side.

Scripts and CSS references

Create an HTML page and add scripts and style sheets that are required to render a pivot gauge widget which are highlighted below in an appropriate order:

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

Initialize pivot gauge

Place a “div” tag in the HTML page which acts as a container for the pivot gauge widget. Then, initialize the widget by using the “ejPivotGauge” method.

  • html
  • <!DOCTYPE html>
    <html>
        <head>
            <title>PivotGauge - Getting Started</title>
            <link href="http://cdn.syncfusion.com/18.3.0.35/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/18.3.0.35/js/web/ej.web.all.min.js" type="text/javascript"></script>
        </head>
        <body>
            <!--Create a tag which acts as a container for ejPivotGauge widget.-->
            <div id="PivotGauge1"></div>
            <script type="text/javascript">
                $(function () {
                    //Set properties and initialize ejPivotGauge widget.
                    $("#PivotGauge1").ejPivotGauge();
                });
            </script>
        </body>
    </html>

    Populate pivot gauge with data source

    Initialize the OLAP data source for the pivot gauge widget by using the dataSource property.

  • html
  • <html>
    
        //....
        <body>
            <div id="PivotGauge1"></div>
            <script type="text/javascript">
                $(function() {
                    $("#PivotGauge1").ejPivotGauge({
                        dataSource: {
                            data: "http://bi.syncfusion.com/olap/msmdpump.dll",
                            catalog: "Adventure Works DW 2008 SE",
                            cube: "Adventure Works",
                            rows: [
                                {
                                    fieldName: "[Date].[Fiscal]"
                                },
                            ],
                            columns: [
                                {
                                    fieldName: "[Customer].[Customer Geography]"
                                }
                            ],
                            values: [
                                {
                                    measures: [
                                        {
                                            fieldName: "[Measures].[Internet Sales Amount]"
                                        },
                                        {
                                            fieldName: "[Measures].[Internet Revenue Status]"
                                        },
                                        {
                                            fieldName: "[Measures].[Internet Revenue Trend]"
                                        },
                                        {
                                            fieldName: "[Measures].[Internet Revenue Goal]"
                                        },
                                    ],
                                    axis: ej.PivotGauge.AxisName.Columns
                                }
                            ]
                        },
                        scales: [
                            {
                                showRanges: true,
                                radius: 150, showScaleBar: true, size: 1,
                                border: {
                                    width: 0.5
                                },
                                showIndicators: true, showLabels: true,
                                pointers: [
                                    {
                                        showBackNeedle: true,
                                        backNeedleLength: 20,
                                        length: 120,
                                        width: 7
                                    },
                                    {
                                        type: "marker",
                                        markerType: "diamond",
                                        distanceFromScale: 5,
                                        placement: "center",
                                        backgroundColor: "#29A4D9",
                                        length: 25,
                                        width: 15
                                    }
                                ],
                                ticks: [
                                    {
                                        type: "major",
                                        distanceFromScale: 2,
                                        height: 16,
                                        width: 1, color: "#8c8c8c"
                                    },
                                    {
                                        type: "minor",
                                        height: 6,
                                        width: 1,
                                        distanceFromScale: 2,
                                        color: "#8c8c8c"
                                    }
                                ],
                                labels: [
                                    {
                                        color: "#8c8c8c"
                                    }
                                ],
                                ranges: [
                                    {
                                        distanceFromScale: -5,
                                        backgroundColor: "#fc0606",
                                        border: { color: "#fc0606" }
                                    },
                                    {
                                        distanceFromScale: -5
                                    }
                                ],
                                customLabels: [
                                    {
                                        position: { x: 180, y: 290 },
                                        font: { size: "10px", fontFamily: "Segoe UI", fontStyle: "Normal" }, color: "#666666"
                                    }, 
                                    {
                                        position: { x: 180, y: 320 },
                                        font: { size: "10px", fontFamily: "Segoe UI", fontStyle: "Normal" }, color: "#666666"
                                    }, 
                                    {
                                        position: { x: 180, y: 150 },
                                        font: { size: "12px", fontFamily: "Segoe UI", fontStyle: "Normal" }, color: "#666666"
                                    }
                                ]
                            }
                        ]
                    });
                });
            </script>
        </body>
    </html>

    The above code will generate a series of gauges for all countries as shown below:

    ClientMode

    The following table will explain the OLAP datasource properties at client-side in detail:

    Properties Description

    cube

    Contains the respective cube name as string type in the OLAP database.

    sourceInfo

    To set the data source name to fetch the data.

    providerName

    Set the provider name for pivot gauge to identify whether the provider is SSAS or Mondrian.

    data

    Provides the raw data source for the pivot gauge.

    catalog

    In connection with an OLAP database, this property contains the database name as string to fetch the data from the given connection string.

    columns

    Lists out the items to bind in columns section.
    Properties Description

    fieldName

    Allows the user to bind the item by using its unique name as field name.

    rows

    Lists out the items to bind in rows section.
    Properties Description

    fieldName

    Allows the user to bind the item by using its unique name as field name.

    values

    Lists out the items supports calculation in pivot gauge.
    Properties Description

    axis

    Allows to set the axis name to place the measures items.

    measures

    This holds the list of unique names of measures to bind them from the OLAP cube. </td> </tr>
    Property Description

    fieldName

    Allows the user to bind the measure from OLAP datasource by using its unique name as field name.

    Creating a simple application with pivot gauge and OLAP data source (server mode)

    This section covers the information required to create a simple pivot gauge that is bound to the OLAP data source from the server-side.

    NOTE

    This section is illustrated by creating a simple web application through the Visual Studio IDE since the pivot gauge in the server mode requires .NET dependency. The web application contain an HTML page and a service that will transfer the data to the server-side, process it, and return it to the client-side for control rendering. The service utilized for communication can be WCF or WebAPI based on user requirement. Here, both are illustrated for user convenience.

    Project initialization

    Create a new ASP.NET Empty Web Application by using the Visual Studio IDE and name the project “PivotGaugeDemo”.

    Next, you should add an HTML page. To add an HTML page in your web application, right-click the project in the solution explorer and select Add > New Item. In the Add New Item window, select HTML Page and name it “GettingStarted.html” page, and then click Add.

    Now, you can set “GettingStarted.html” as start-up page. To do so, right-click the “GettingStarted.html” page and select “Set As Start Page”.

    Scripts and CSS initialization

    The scripts and style sheets that are required to render a pivot gauge widget in an HTML page are highlighted below in an appropriate order:

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

    The scripts and style sheets listed above can be found in any of the following locations:

    Local disk: Click here to know more about script and style sheets installed on the local machine.

    CDN link: Click here to know more about script and style sheets available in online.

    NuGet package: Click here to know more about script and style sheets available in the NuGet package.

    Control initialization

    To initialize a pivot gauge widget, first you can define a “div” tag with an appropriate “id” attribute which acts as a container for the pivot gauge widget. Then, you can initialize the widget by using the ejPivotGauge method.

  • html
  • <!DOCTYPE html>
    <html xmlns="http://www.w3.org/1999/xhtml">
        <head>
            <title>PivotGauge - Getting Started</title>
            <link href="http://cdn.syncfusion.com/18.3.0.35/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/18.3.0.35/js/web/ej.web.all.min.js" type="text/javascript"></script>
        </head>
    
        <body>
            <!--Create a tag which acts as a container for ejPivotGauge widget.-->
            <div id="PivotGauge1"> </div>
            <script type="text/javascript">
                //Set properties and initialize ejPivotGauge widget.
                $(function() {
                    $("#PivotGauge1").ejPivotGauge({
                        url: "/Olap",
                        scales: [
                            {
                                showRanges: true,
                                radius: 150, showScaleBar: true, size: 1,
                                border: {
                                    width: 0.5
                                },
                                showIndicators: true, showLabels: true,
                                pointers: [
                                    {
                                        showBackNeedle: true,
                                        backNeedleLength: 20,
                                        length: 120,
                                        width: 7
                                    },
                                    {
                                        type: "marker",
                                        markerType: "diamond",
                                        distanceFromScale: 5,
                                        placement: "center",
                                        backgroundColor: "#29A4D9",
                                        length: 25,
                                        width: 15
                                    }
                                ],
                                ticks: [
                                    {
                                        type: "major",
                                        distanceFromScale: 2,
                                        height: 16,
                                        width: 1, color: "#8c8c8c"
                                    },
                                    {
                                        type: "minor",
                                        height: 6,
                                        width: 1,
                                        distanceFromScale: 2,
                                        color: "#8c8c8c"
                                    }
                                ],
                                labels: [
                                    {
                                        color: "#8c8c8c"
                                    }
                                ],
                                ranges: [
                                    {
                                        distanceFromScale: -5,
                                        backgroundColor: "#fc0606",
                                        border: { color: "#fc0606" }
                                    },
                                    {
                                        distanceFromScale: -5
                                    }
                                ],
                                customLabels: [
                                    {
                                        position: { x: 180, y: 290 },
                                        font: { size: "10px", fontFamily: "Segoe UI", fontStyle: "Normal" }, color: "#666666"
                                    }, 
                                    {
                                        position: { x: 180, y: 320 },
                                        font: { size: "10px", fontFamily: "Segoe UI", fontStyle: "Normal" }, color: "#666666"
                                    }, 
                                    {
                                        position: { x: 180, y: 150 },
                                        font: { size: "12px", fontFamily: "Segoe UI", fontStyle: "Normal" }, color: "#666666"
                                    }
                                ]
                            }
                        ]
                    });
                });
            </script>
        </body>
    </html>

    The url property in the pivot gauge widget points the service endpoint, where the data is processed and fetched in the form of JSON. The services used for the pivot gauge widget as endpoint are WCF and WebAPI.

    NOTE

    The above “GettingStarted.html” contains WebAPI url, which is “/Olap”. If you are using the WCF service, then the url will look like “/OlapService.svc”.

    WebAPI

    Adding a WebAPI controller

    To add a WebAPI controller in your existing web application, right-click the project in the solution explorer and select Add > New Item. In the Add New Item window, select WebAPI Controller Class and name it “OlapController.cs,” and then click Add.

    The WebAPI controller is added to your application, which, in-turn, comprises the following file. The utilization of this file will be explained in the following sections:

    • OlapController.cs

    NOTE

    While adding the WebAPI controller class, add the mandatory suffix “Controller”. For example, in the demo, the controller is named “OlapController”.

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

  • c#
  • namespace PivotGaugeDemo
    {
        public class OlapController : ApiController
        {
        
        }
    }

    List of dependency libraries

    Next, you can add the below-mentioned dependency libraries to your web application. These libraries can be found in the GAC (Global Assembly Cache).

    To add them to your web application, right-click References in the solution explorer and select Add Reference. In the Reference Manager dialog, under Assemblies > Extension, the following Syncfusion libraries are found.

    NOTE

    If you have installed any version of SQL Server Analysis Service (SSAS) or Microsoft ADOMD.NET utility, then the location of Microsoft.AnalysisServices.AdomdClient library is [system drive:\Program Files (x86)\Microsoft.NET\ADOMD.NET]. If you have installed any version of Essential Studio, then the location of Syncfusion libraries is [system drive:\Program Files (x86)\Syncfusion\Essential Studio\18.3.0.35\Assemblies].

    • Microsoft.AnalysisServices.AdomdClient
    • Syncfusion.Compression.Base
    • Syncfusion.Linq.Base
    • Syncfusion.Olap.Base
    • Syncfusion.PivotAnalysis.Base
    • Syncfusion.EJ
    • Syncfusion.EJ.Export
    • Syncfusion.EJ.Pivot

    List of namespaces

    Following are the list of namespaces to be added on top of the main class in the OlapController.cs file:

  • c#
  • using Syncfusion.Olap.Manager;
    using Syncfusion.Olap.Reports;
    using Syncfusion.JavaScript;
    
    namespace PivotGaugeDemo
    {
        public class OlapController : ApiController
        {
            
        }
    }

    Data source initialization

    Now, the connection string to connect the OLAP cube and the pivot gauge instances is created immediately in the main class of the OlapController.cs file.

  • c#
  • namespace PivotGaugeDemo
    {
        public class OlapController : ApiController
        {
            string connectionString = "Data Source=http://bi.syncfusion.com/olap/msmdpump.dll; Initial Catalog=Adventure Works DW 2008 SE;";
            PivotGauge pivotGauge = new PivotGauge();
            //Other codes
        }
    }

    Service methods in WebAPI controller

    Now, you can define the service methods in the OlapController class, find in the OlapController.cs file which was created while adding the WebAPI controller class to your web application.

  • c#
  • namespace PivotGaugeDemo
    {
        public class OlapController : ApiController
        {
            string connectionString = "Data Source=http://bi.syncfusion.com/olap/msmdpump.dll; Initial Catalog=Adventure Works DW 2008 SE;";
            PivotGauge pivotGauge = new PivotGauge();
            
            [System.Web.Http.ActionName("InitializeGauge")]
            [System.Web.Http.HttpPost]
            public Dictionary<string, object> InitializeGauge(Dictionary<string, object> jsonResult)
            {
                OlapDataManager DataManager = new OlapDataManager(connectionString);
                DataManager.SetCurrentReport(CreateOlapReport());
                return pivotGauge.GetJsonData(jsonResult["action"].ToString(), DataManager);
            }
    
            private OlapReport CreateOlapReport()
            {
                OlapReport olapReport = new OlapReport();
                olapReport.Name = "Default Report";
                olapReport.CurrentCubeName = "Adventure Works";
    
                DimensionElement dimensionElementColumn = new DimensionElement();
                //Specifying the Name for the Dimension Element
                dimensionElementColumn.Name = "Customer";
                dimensionElementColumn.AddLevel("Customer Geography", "Country");
    
                MeasureElements measureElementColumn = new MeasureElements();
                //Specifying the Name for the Measure Element
                measureElementColumn.Elements.Add(new MeasureElement { Name = "Customer Count" });
    
                DimensionElement dimensionElementRow = new DimensionElement();
                //Specifying the Dimension Name
                dimensionElementRow.Name = "Date";
                dimensionElementRow.AddLevel("Fiscal", "Fiscal Year");
    
                ///Adding Row Members
                olapReport.SeriesElements.Add(dimensionElementRow);
                ///Adding Column Members
                olapReport.CategoricalElements.Add(dimensionElementColumn);
                ///Adding Measure Element
                olapReport.CategoricalElements.Add(measureElementColumn);
                return olapReport;
            }
        }
    }

    Configure routing in global application class

    To add a Global.asax in your existing web application, right-click the project in the solution explorer and select Add > New Item. In the Add New Item window, select Global Application Class and name it “Global.asax”, and then click Add.

    After adding the Global.asax file, immediately add the namespace “using System.Web.Http;”, and then configure the routing as shown in the following code example:

  • c#
  • public class Global : System.Web.HttpApplication
    {
        protected void Application_Start(object sender, EventArgs e)
        {
            GlobalConfiguration.Configuration.Routes.MapHttpRoute(
                name: "DefaultApi",
                routeTemplate: "{controller}/{action}/{id}",
                defaults: new { id = RouteParameter.Optional });
            AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", true);
        }
    }

    Now, the pivot gauge will be rendered with the provided data as shown below:

    ServerMode

    WCF

    This section demonstrates the utilization of the WCF service as endpoint binding the OLAP data source to a simple pivot gauge. For more details on this topic, click here.