Pivoting data in Microsoft Dynamics 365 using Wijmo OLAP

The numerous entities and records in the back-end that generally compose Dynamics 365 applications can make it difficult for business analysts to analyze their data on a day-to-day basis, gather useful information, and then make recommendations to business stakeholders. Although reporting tools can help, they lack the business intelligence required to extrapolate meaningful information to empower the stakeholders to make timely decisions. A developer could devise mechanisms to help business analysts or introduce built-in reports for the business stakeholders to view the right data at the right time, but creating such reports could be a daunting task. User requirements might change from time to time based on business trends. They might also need different views of the same data, leading to the creation of several reports, with some only being used once. By devising a generic system, like a dashboard, developers could make it possible for users to create views themselves and find the data analysts they need. This is where the Wijmo 5 OLAP control comes in. In our previous post, we learned with how we can easily extend Microsoft Dynamics 365 using Wijmo 5 Controls — FlexGrid and FlexChart — to create applications customized to your end user's needs and how we can analyze data from CRM-specific entities. We also talked about how we could use Wijmo 5’s OLAP control to analyze and drilldown. Here, we'll put our words into action and showcase the analytical capabilities of the Wijmo 5 OLAP control. olap-with-dynamics-1 We'll learn how to:

  1. Fetch Data from an invoice entity
  2. Pivot data on Invoice and create views
  3. Save and load views

Let's Get Started

First, we'll create an invoice dashboard using OLAP and FlexGrid. Dashboard Features and Components: There will be two tabs to display/analyze data and a side panel to list the defined views: Tab 1: For pivoting and analyzing data: This will be used to pivot data from the invoices. Users can pivot data however they need to. Here, we'll use the PivotGrid control. Tab 2: For tabular display of raw data: Displays raw data from the invoices entity. Here, we'll use the FlexGrid control. Save Button: For users to save their custom views. Side Panel: For listing all of the saved views. Even though creating the dashboard involves the same steps we elaborated on in our previous post, but we'll briefly summarize the steps here.

Step 1: Creating the HTML page

Our HTML page needs these Wijmo components: OLAP Grid, OLAP Panel, and FlexGrid. We'll use the Bootstrap Angular UI framework to create our tabs.

Step 2: Add JavaScript code to perform the read operation on the invoice entity and plug data from Dynamics 365 to OLAP

Here, we'll use Dynamics 365 Web API to fetch records from the invoice entity. We've selected specific fields from the invoice entity that we'll use to pivot data on it. Once the data is extracted from Invoices, we'll bind it to FlexGrid to display the raw data. We'll use this raw data to pivot data using the OLAP control. We've created a single file which contains both HTML & the JavaScript controller. We used a different approach than in our last post to save some loading time.

gc_olapview.html – Code Snippets

The code snippets given below only highlight the core functionality needed to create the application. The entire code for gc_olapview.html can be found in the project link below.

Wijmo 5 & Angular Dependencies

Angular App Reference & Wijmo Dependency Injection

var app = angular.module('app', ['wj', 'ui.bootstrap']);  

Data Service to Fetch Data from Invoice Entity

        app.service('DataService', function ($q) {  
            this.retrieveRecords = function () {  
                return $q(function (resolve, reject) {  
                    var clientURL = Xrm.Page.context.getClientUrl();  
                    var req = new XMLHttpRequest()  
                    var query = "/api/data/v8.0/invoices?$select=totalamount,billto\_postalcode,billto\_country,datedelivered,discountpercentage,totaltax,totaldiscountamount,name&$top=1000";  
                    req.open("GET", encodeURI(clientURL + query), true);  
                    req.setRequestHeader("Accept", "application/json");  
                    req.setRequestHeader("Content-Type", "application/json;charset=utf-8");  
                    req.setRequestHeader("OData-MaxVersion", "4.0");  
                    req.setRequestHeader("OData-Version", "4.0");  
                    req.onreadystatechange = function () {  
                        if (this.readyState == 4) {  
                            req.onreadystatechange = null;  
                            if (this.status == 200) {  
                                var data = JSON.parse(this.response);  
                                if (data && data.value) {  
                            else {  
                                var error = JSON.parse(this.response).error;  

Code for Controller

        app.controller('appCtrl', function ($scope, $timeout, $uibModal, $log, $q, DataService) {  
            $scope.pivotgrid = [];  
            $scope.flxgrid = [];  
            $scope.viewdefs = [];  
            $scope.thePanel = null;  
            // Parse the Invoice Shipped date and get Month and Year  
            $scope.AddTimeInformation = function (data) {  
                data.forEach(function (item, index) {  
                    var dateofinvoice = new Date(item.datedelivered);  
                    data[index].datedelivered = dateofinvoice  
                    data[index].Year = dateofinvoice.getFullYear().toString();  
                    data[index].Month = monthNames[dateofinvoice.getMonth()];  
                return data;  

Load Invoices Data from Dynamics 365

            $scope.GetData = function () {  
                var promise = DataService.retrieveRecords();  
                promise.then(function (response) {  
                    response = $scope.AddTimeInformation(response)  
                    $scope.data = new wijmo.collections.CollectionView(response);  
                    $scope.data.trackChanges = true;  
                }, function (reason) {  

Save View to Local Storage

       $scope.SaveView = function () {  
                $scope.viewCounter = $scope.viewCounter + 1  
                var definiation =  
                    "def": $scope.thePanel.engine.viewDefinition,  
                    "name": "CustomView" + $scope.viewCounter  
                if ($scope.viewdefs == null) {  
                    $scope.viewdefs = [];  
                localStorage.setItem('viewdefs', JSON.stringify($scope.viewdefs));  

Load View from Local Storage

            $scope.LoadViews = function () {  
                var retrievedObject = localStorage.getItem('viewdefs');  

HTML Markup

Pivot Panel & Pivot Grid


Step 3: Deploying Code & Dependencies to CRM

Please refer to Step 3 of our previous post: we need to perform the exact steps that we outlined there, but with a few minor changes.

  1. Upload Wijmo.Olap.min.js as a web resource.
  2. Upload Angular UI Bootstrap JS(gc_uibootstrap.js) as a web resource. It's required for rendering the Tab control in Angular applications.
  3. Upload gc_olapview.html as Web Resource. It contains Script and HTML for rendering OLAP and the FlexGrid control.

Step 4: Entry Point

Take note of the URL field for gc_OlapView.html: this serves as the entry point for the invoice pivot dashboard. In our sample, the entry point is: https://dtdustest.crm.dynamics.com//WebResources/gc\_OlapView

Step 5: Open the Invoice Pivot Dashboard

Invoices Pivot Dashboard

Raw Data- The Raw Data Tab displays the data that’s been fetched from the invoices entity. This data is populated into FlexGrid. raw-data Additionally, we can perform grouping, filtering and sorting operations on the raw data. raw-data-2 Pivoting Data on Invoices This is where all the action takes place. The Pivot Grid Tab consists of a Pivot panel and a Pivot Grid. Pivot Panel This panel displays all fields from the invoices entity (the raw data). Users can drag and drop fields into columns, rows, and values sections to create views based on the data set. Additionally, users can also use filters and sort on these fields. If you're familiar with an OLAP Engine, you have a sense of how easy it is to pivot data. Pivot Grid The Pivot Grid displays the pivoted data based on the fields we added to the Pivot panel. We can also perform sort operation on the columns and view the underlying data for a pivot point. pivot-grid-1 pivot-grid-2 Adding Custom Views You can preserve the pivoted data to view it again by saving these views. In our shared example, we used a bit of code to save views created by the users. This is because we're storing them to local storage. But in real applications, we can save the views to custom CRM entities against specific users and load them when the Pivot Grid is loaded. adding-custom-views For more information about Wijmo 5 controls, check out our Sample Explorer and Docs. You can also download a free trial of Wijmo 5.

Project Files

Source Code Project: WijmoSampleSolution_1_0_0_0 This project contains all the source files for the invoice pivot dashboard. Dynamics 365 Solution: OlapWithDynamics365 We also created a MSCRM solution which can be directly imported into your CRM Instance and you can see the pivot dashboard in action on your end.


GrapeCity Developer Tools
comments powered by Disqus