Visualizing Dynamics 365 Sales Data with ComponentOne DataConnectors
Microsoft Dynamics 365 is a popular enterprise business solution designed to manage business processes. Some of the core modules include sales, customer service, marketing, and finance operations.
In the ComponentOne 2020 v1 release, we have added an ADO.NET data connector for Dynamics 365 Sales. This enables connecting seamlessly to sales entities in Dynamics 365 with just a few lines of code.
The Dynamics 365 ADO.NET connector becomes very useful when used with an analytical UI control such as the ComponentOne OLAP/Pivot control. The UI control provides the analytical abilities for records from Dynamics.
Let’s take a look at an example of the sales module. It is one of the most common and most important modules used by businesses, as it helps businesses keep a record of accounts, contacts, invoice records, and the flow of information from lead generation to orders and sales.
Here’s a look at the invoice entity and its records from Dynamics 365:
If a sales manager would like to analyze these invoice records to get information regarding:
- Total sales for a year
- Tax collected by country
- Total invoices shipped by year
The easiest way would be to download all the records to an Excel sheet and then create a pivot table, based on the fields. However, this would be a considerable manual effort and the process would have to repeat for every record update.
If we could build an analytical sales dashboard to automate this process it would make the job of the sales manager easier.
The ComponentOne DataConnectors for Dynamics 365 and the OLAP/Pivot control is useful to create this dashboard quickly.
Sales Dashboard Features
Here is the breakdown of features for implementing and using these controls.
Data Connector for Dynamics 365
- Connecting to Microsoft Dynamics 365 API
- Querying the Invoice Entity for data
ComponentOne OLAP/Pivot Control
- Displaying the data on the OLAP control for analysis
- Saving and Loading views (Total Sales for a Year, Tax Collected by Country, etc.)
Now that we understand the architecture, let's create an MVC Core application to implement the sales dashboard, step by step.
Step 1: Adding NuGet Packages for ComponentOne DataConnectors & OLAP Control
You can download and install the ComponentOne DataConnectors (part of the Service Components module) and the ASP.NET MVC Edition locally, or add references to the following packages from the NuGet Gallery (nuget.org):
- C1.AdoNet.D365S
- C1.AspNetCore.Mvc
- C1.AspNetCore.Mvc.Olap
- C1.DataConnector
Step 2: Configuring the Connection to Microsoft Dynamics 365 Sales
In order to connect to the Dynamics 365 Sales instance, we need these parameters:
appsettings.json
{
"DynamicConnection": {
"UrlDynamics": "https://Contosso.crm8.dynamics/api/data/v9.1/",
"ClientID": "896f8e6f-7854-4248-9383-8257540737fa",
"CllentSecret": "pdc?@RbRZ9sTHMpVvK3R2860Jg:Jtj:Lh",
"Username": "admin",
"Password": "password",
"TokenEnpoint": "https://login.microsoftonline.com/eabb65e3-6625-4eb4-a534-5ca7b11098b6/oauth2/token",
"Resource": "https://Contosso.crm8.dynamics/"
}
}
Note: All connection string settings provided above are placeholder values. The values must be modified as per your actual Dynamics 365 environment in order to execute correctly.
Step 3: Connecting to Dynamics 365 using the ComponentOne DataConnectors
The command Text contains the query for getting the records from the Dynamics 365 Invoice entity:
IndexController.cs
private IEnumerable<InvoiceData> GetData()
{
var config = this.config.Value;
string extendProperties = "{\"resource\":\"" + config.Resource + "\"}";
var conectionStr = $@"Url={config.UrlDynamics};Use Etag=true;OAuthClientId={config.ClientID};OAuthClientSecret={config.CllentSecret};OAuthTokenEndpoint={config.TokenEnpoint};OAuthExtendProperties={extendProperties};Max Page Size = 100";
using (C1D365SConnection con = new C1D365SConnection(conectionStr))
{
//Open the connection to dynamic 365 server
con.Open();
var cmd = con.CreateCommand();
//query to select from dynamic 365 server
cmd.CommandText = "Select totalamount, billto_postalcode, billto_country, datedelivered, discountpercentage, totaltax, totaldiscountamount, name from invoices limit 1000";
var reader = cmd.ExecuteReader();
List<InvoiceData> result = new List<InvoiceData>();
CultureInfo provider = CultureInfo.InvariantCulture;
//Generate List<InvoiceData> from reader
while (reader.Read())
{
}); ;
}
return result;
}
}
Step 4: Parsing the Data Received from Dynamics 365 Inside GetData()
The data received from Dynamics 365 is parsed to a readable format, so that it can be converted to an IList to be later used as a DataSource for the pivot control:
IndexController.cs
using (C1D365SConnection con = new C1D365SConnection(conectionStr))
{
//Open the connection to dynamic 365 server
con.Open();
var cmd = con.CreateCommand();
//query to select from dynamic 365 server
cmd.CommandText = "Select totalamount, billto_postalcode, billto_country, datedelivered, discountpercentage, totaltax, totaldiscountamount, name from invoices limit 1000";
var reader = cmd.ExecuteReader();
List<InvoiceData> result = new List<InvoiceData>();
CultureInfo provider = CultureInfo.InvariantCulture;
//Generate List<InvoiceData> from reader
while (reader.Read())
{
//Put the default value if totalamount is null. Just use for demo
var totalamount = String.IsNullOrEmpty(reader[0].ToString()) ? 0 : Convert.ToDecimal(reader[0].ToString());
var billto_postalcode = reader[1].ToString();
var billto_country = reader[2].ToString();
var datedelivered = reader[3].ToString();
DateTime? deliveredDate = null;
if (!String.IsNullOrEmpty(datedelivered) && DateTime.TryParse(datedelivered, out DateTime convertedDate))
{
deliveredDate = convertedDate;
}
else
{
//Put the default value if datedelivered is null. Just use for demo
deliveredDate = new DateTime(2019, 12, 1);
}
//Put the default value if discountpercentage is null. Just use for demo
var discountpercentage = String.IsNullOrEmpty(reader[4].ToString()) ? 0 : Convert.ToDecimal(reader[4].ToString());
//Put the default value if totaltax is null. Just use for demo
var totaltax = String.IsNullOrEmpty(reader[5].ToString()) ? 0 : Convert.ToDecimal(reader[5].ToString());
//Put the default value if totaldiscountamount is null. Just use for demo
var totaldiscountamount = String.IsNullOrEmpty(reader[6].ToString()) ? 0 : Convert.ToDecimal(reader[6].ToString());
var name = reader[7].ToString();
//Add InvoiceData model from the reader
result.Add(new InvoiceData
{
Totalamount = totalamount,
Billtopostalcode = billto_postalcode,
//Put the default value if billto_country is null. And Standardize the billto_country. Just use for demo
Billtocountry = String.IsNullOrEmpty(billto_country) ? StandardizeName("United States") : StandardizeName(billto_country),
Discountpercentage = discountpercentage,
Totaltax = totaltax,
Totaldiscountamount = totaldiscountamount,
//Standardize the Name. Just use for demo
Name = StandardizeName(name),
Datedelivered = deliveredDate.Value.ToString(CultureInfo.InvariantCulture),
Year = deliveredDate.Value.Year.ToString(),
Month = deliveredDate.Value.ToString("MMMM"),
}); ;
}
return result;
Step 5: Return the Invoice as a Model for the View Page
IndexController.cs
public ActionResult Index()
{
IEnumerable Data = GetData().ToList();
OlapModel.ControlId = "indexPanel";
ViewBag.DemoOptions = OlapModel;
return View(Data);
}
Step 6: Binding Invoice Data to ComponentOne OLAP
The invoice data received from the Model is bound the C1 MVC OLAP Control i.e c1-pivot-engine using the source-collection property of the c1-items-source control:
Index.cshtml
@model IEnumerable<InvoiceData>
@{
ClientSettingsModel optionsModel = ViewBag.DemoOptions;
}
<c1-pivot-engine id="indexEngine" show-row-totals="Subtotals" show-column-totals="Subtotals" totals-before-data="false">
<c1-items-source source-collection="Model"></c1-items-source>
<c1-pivot-field-collection>
<c1-pivot-field header="Bill To Country" binding="Billtocountry"></c1-pivot-field>
<c1-pivot-field header="Year" binding="Year"></c1-pivot-field>
<c1-pivot-field header="Total Amount" binding="Totalamount"></c1-pivot-field>
<c1-pivot-field header="Bill To Postal Code" binding="Billtopostalcode"></c1-pivot-field>
<c1-pivot-field header="Discount Percentage" binding="Discountpercentage"></c1-pivot-field>
<c1-pivot-field header="Total Tax" binding="Totaltax"></c1-pivot-field>
<c1-pivot-field header="Total Discount Amount" binding="Totaldiscountamount"></c1-pivot-field>
<c1-pivot-field header="Date Delivered" binding="Datedelivered"></c1-pivot-field>
<c1-pivot-field header="Name" binding="Name"></c1-pivot-field>
<c1-pivot-field header="Month" binding="Month"></c1-pivot-field>
</c1-pivot-field-collection>
<c1-view-field-collection c1-property="RowFields" items="Bill To Country"></c1-view-field-collection>
<c1-view-field-collection c1-property="ColumnFields" items="Year"></c1-view-field-collection>
<c1-view-field-collection c1-property="ValueFields" items="Total Amount" ca></c1-view-field-collection>
</c1-pivot-engine>
<div class="row">
<div class="col-sm-4 col-md-4">
<c1-pivot-panel id="@(optionsModel.ControlId)" items-source-id="indexEngine"></c1-pivot-panel>
</div>
<div class="col-sm-8 col-md-8">
<c1-pivot-grid id="indexGrid" items-source-id="indexEngine" outline-mode="false" show-value-field-headers="false"></c1-pivot-grid>
<p>
<span style="vertical-align:top; color:black;">
ShowValueFieldHeaders <input id="ShowValueFieldHeaders" type="checkbox" onchange="toggleShowValueFieldHeaders(event)" />
</span>
</p>
</div>
</div>
Step 7: Creating Dynamics Views on the Dashboard
Our sales dashboard has taken shape. The sales manager can add pivot fields to row, columns, and values fields using simple drag-drop operations to create dynamic views based on his requirements.
Loading and Saving Existing Views
In order to save or load an existing views to local storage, the 'View' definition of the OLAP control can be used:
@section Scripts{
<script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.5.0/jszip.min.js"></script>
<script type="text/javascript">
function saveView() {
var ng = c1.getService('indexEngine');
if (ng && ng.isViewDefined) {
localStorage.viewDefinition = ng.viewDefinition;
}
}
function loadView() {
var ng = c1.getService('indexEngine');
if (ng && localStorage.viewDefinition) {
ng.viewDefinition = localStorage.viewDefinition;
var cmbRowTotals = wijmo.Control.getControl('#RowTotals');
if (cmbRowTotals) {
cmbRowTotals.selectedValue = ng.showRowTotals;
}
var cmbColTotals = wijmo.Control.getControl('#ColTotals');
if (cmbColTotals) {
cmbColTotals.selectedValue = ng.showColumnTotals;
}
var chkShowZeros = document.getElementById('ColTotals');
if (chkShowZeros) {
chkShowZeros.checked = ng.showZeros;
}
}
}
Exporting OLAP Views to Excel
The MVC Olap/Pivot control extends the FlexGrid control, so you can export it to any of the formats supported by the extension modules provided with the FlexGrid. The list of the supported formats includes .xlsx, .csv, and .pdf.
For example, code below creates an Excel file with two sheets: the current view and a transposed version of the current view.
@section Scripts{
<script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.5.0/jszip.min.js"></script>
<script type="text/javascript">
function excelExport() {
var pivotGrid = wijmo.Control.getControl('#indexGrid');
// create book with current view
var book = wijmo.grid.xlsx.FlexGridXlsxConverter.save(pivotGrid, {
includeColumnHeaders: true,
includeRowHeaders: true
});
book.sheets[0].name = 'Main View';
addTitleCell(book.sheets[0], getViewTitle(pivotGrid.engine));
// add sheet with transposed view
transposeView(pivotGrid.engine);
var transposed = wijmo.grid.xlsx.FlexGridXlsxConverter.save(pivotGrid, {
includeColumnHeaders: true,
includeRowHeaders: true
});
transposed.sheets[0].name = 'Transposed View';
addTitleCell(transposed.sheets[0], getViewTitle(pivotGrid.engine));
book.sheets.push(transposed.sheets[0]);
transposeView(pivotGrid.engine);
// save the book
book.save('wijmo.olap.xlsx');
}
// build a title for the current view
function getViewTitle(ng) {
var title = '';
for (var i = 0; i < ng.valueFields.length; i++) {
if (i > 0) title += ', ';
title += ng.valueFields[i].header;
}
title += ' by ';
if (ng.rowFields.length) {
for (var i = 0; i < ng.rowFields.length; i++) {
if (i > 0) title += ', ';
title += ng.rowFields[i].header;
}
}
if (ng.rowFields.length && ng.columnFields.length) {
title += ' and by ';
}
if (ng.columnFields.length) {
for (var i = 0; i < ng.columnFields.length; i++) {
if (i > 0) title += ', ';
title += ng.columnFields[i].header;
}
}
return title;
}
function transposeView(ng) {
ng.deferUpdate(function () {
// save row/col fields
var rows = [],
cols = [];
for (var r = 0; r < ng.rowFields.length; r++) {
rows.push(ng.rowFields[r].header);
}
for (var c = 0; c < ng.columnFields.length; c++) {
cols.push(ng.columnFields[c].header);
}
// clear row/col fields
ng.rowFields.clear();
ng.columnFields.clear();
// restore row/col fields in transposed order
for (var r = 0; r < rows.length; r++) {
ng.columnFields.push(rows[r]);
}
for (var c = 0; c < cols.length; c++) {
ng.rowFields.push(cols[c]);
}
});
}
// adds a title cell into an xlsx sheet
function addTitleCell(sheet, title) {
// create cell
var cell = new wijmo.xlsx.WorkbookCell();
cell.value = title;
cell.style = new wijmo.xlsx.WorkbookStyle();
cell.style.font = new wijmo.xlsx.WorkbookFont();
cell.style.font.bold = true;
// create row to hold the cell
var row = new wijmo.xlsx.WorkbookRow();
row.cells[0] = cell;
// and add the new row to the sheet
sheet.rows.splice(0, 0, row);
}
// toggle outline mode
function toggleOulineMode(e) {
var pivotGrid = wijmo.Control.getControl('#indexGrid');
pivotGrid.outlineMode = e.target.checked;
}
// toggle ShowValueFieldHeaders
function toggleShowValueFieldHeaders(e) {
var pivotGrid = wijmo.Control.getControl('#indexGrid');
pivotGrid.showValueFieldHeaders = e.target.checked;
}
</script>
}
Dynamics 365 Pivot Demo
You can see a live demo of the ComponentOne DataConnectors for Dynamics 365 and the MVC OLAP control below.
Abhishek Dutta