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/Bootstrap -->
<script src="gc_angular.min.js"></script>
<script src="gc_uibootstrap"></script>
<link rel="stylesheet" type="text/css" href="gc_bootstrap.min.css" />
<!-- Angular/Bootstrap -->
<!-- Wijmo -->
<link href="gc_wijmo.min.css" rel="stylesheet" />
<script src="gc_wijmo.min.js"></script>
<script src="gc_wijmo.input.min.js"></script>
<script src="gc_wijmo.grid.min.js"></script>
<script src="gc_wijmo.grid.filter.min.js"></script>
<script src="gc_wijmo.olap.min.js"></script>
<script src="gc_wijmo.grid.grouppanel.min.js"></script>
<script src="gc_wijmo.angular.min.js"></script>


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) {
resolve(data.value);
}
}
else {
var error = JSON.parse(this.response).error;
reject(error);
}
}
};
req.send(null);
});
}
});


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) {
alert(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 = [];
}
$scope.viewdefs.push(definiation);
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



<div class="col-md-3" style="">
<wj-pivot-panel items-source="data" control="thePanel" initialized="initPivotPanel(s,e)">
</wj-pivot-panel>
</div>
<div class="col-md-9">

<wj-pivot-grid control="pivotGrid"
items-source="thePanel"
show-selected-headers="All"
initialized="initPivotDataGrid(s,e)">
</wj-pivot-grid>
</div>
</div>


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.