Dashboards and KPI`s are those tools that every manager wants from a Business software like CRM, ERP and etc.
In a company that I develop Dynamics CRM for their based on their requirements, I was asked to create a charts for the marketing dashboard. Default chart tool in Dynamics CRM helps you easily create chat using wizard but it has drawbacks, and one the main drawbacks is that filtering the data to be displayed in chart is not as easy as creating the chart.
So after lots of search and try, I selected Highcharts to use in dashboard. In the following, I explain a sample, that shows a pie chart by lead source.
Note: All the codes here work for Dynamics CRM 2011, but it also works for 2013.
Here is the code:
<html lang="en"><head>
<link id="sharedaddy-css" href="new_pikaday.css" rel="stylesheet" type="text/css" media="all">
<script src="ClientGlobalContext.js.aspx" type="text/javascript"></script>
<script src="new_jquery1.6.min.js" type="text/javascript"></script>
<script src="new_sdk.rest.js" type="text/javascript"></script>
<script src="new_json2.js" type="text/javascript"></script>
<script src="new_sdk.metadata.js" type="text/javascript"></script>
<script src="new_highcharts.js" type="text/javascript"></script>
<script src="new_moment.js" type="text/javascript"></script>
<script src="new_pikaday.js" type="text/javascript"></script>
<script src="new_custom_reports_common.js" type="text/javascript"></script>
<script type="text/javascript">
var teamusers;
function LeadSourceDataClass()
{
this.Name = '';
this.Items = [];
}
function LeadSourceDataClassItem(title, code)
{
this.Title = title;
this.Code = code;
this.Data = 0;
}
LeadSourceDataClass.prototype.reSet = function(){
this.Items = [];
};
LeadSourceDataClass.prototype.AddToItem = function(code){
$.each(this.Items, function(index, item)
{
if(item.Code == code)
{
item.Data += 1;
return false;
}
})
};
var dataObj = new LeadSourceDataClass();
function generateChart()
{
dataObj.reSet();
var filter = getFilter();
var options = "$select=LeadId,LeadSourceCode&$filter=" + filter + "&$top=" + Helper.XRM.CustomReports.MaxRecords;
SDK.Metadata.RetrieveAttribute("Lead", "LeadSourceCode", "00000000-0000-0000-0000-000000000000", true, function (result) {
for (var i = 0; i < result.OptionSet.Options.length; i++) {
var text = result.OptionSet.Options[i].Label.LocalizedLabels[0].Label;
var value = result.OptionSet.Options[i].Value;
dataObj.Items.push(new LeadSourceDataClassItem(text, value));
}
SDK.REST.retrieveMultipleRecords("Lead", options, retrieveLeads, function (error) { console.log(error); }, retrieveLeadsCompleted);
}, function (error) { console.log(error); })
}
function retrieveLeads(data)
{
$.each(data,function(index, item)
{
if(item.LeadSourceCode)
if(item.LeadSourceCode.Value)
dataObj.AddToItem(item.LeadSourceCode.Value);
})
}
function retrieveLeadsCompleted()
{
var data = [];
$.each(dataObj.Items, function(index, item)
{
data.push([item.Title, item.Data]);
});
var series = [
{
type: 'pie',
name: '',//name,
data: data
}
];
Helper.XRM.CustomReports.createPieChart('#chartcontainer', 'Leads by Source (Pie)', series, chartClickEvent, chartTooltipFunction);
}
function chartTooltipFunction()
{
return this.key + ': ' + this.y;
}
function chartClickEvent(event)
{
}
function getFilter()
{
var filter = "";
if($('#fromPicker').val())
filter += " and CreatedOn ge datetime'" + $('#fromPicker').val() + "T00:00:00Z'";
if($('#toPicker').val())
filter += " and CreatedOn le datetime'" + $('#toPicker').val() + "T23:59:59Z'";
if($('#usersSelect').val())
filter += " and CreatedBy/Id eq guid'" + $('#usersSelect').val() + "'";
if (filter.substr(0, 5) == " and ")
filter = filter.substr(5);
return filter;
}
$(document).ready(function(){
Helper.XRM.CustomReports.createDatePicker('fromPicker');
Helper.XRM.CustomReports.createDatePicker('toPicker');
$('#generateBtn').click(generateChart)
});
</script>
</head>
<body>
<div id="contentwrapper">
<div id="content" style="height: 62%;">
<div id="chartcontainer"></div>
</div>
<div id="filters">
<a id="viewRecords" href="http://google.com" target="_blank">View Records</a>
<hr>
<table class="filtersTbl">
<thead>
<tr>
<th style="width: 25%;"></th>
<th style="width: 25%;"></th>
<th style="width: 25%;"></th>
<th style="width: 25%;"></th>
</tr>
</thead>
<tbody>
<tr>
<td class="tableLabel">From</td>
<td><input id="fromPicker" type="text"></td>
<td class="tableLabel">To</td>
<td><input id="toPicker" type="text"></td>
</tr>
<tr>
<td></td>
<td><input name="generateBtn" id="generateBtn" type="button" value="Generate"></td>
</tr>
</tbody>
</table>
</div>
</div>
</body></html>
So let’s explain each part separately.
You see that there are bunch of scripts and a CSS file in the first. You can add web resources to Dynamics CRM using Customization section of main settings, so I skip the part.
I use pickaday library to make it easier for user to select the date range from a calendar. To interact with Dynamics API, I use these libraries : ClientGlobalContext.js.aspx, sdk.rest.js, json2.js, sdk.metadata.js. And the most important one, is Highchart js lib that I included in my file.
In the body of the file, I have some html elements that help user fill in the filtering data such as Creation Date range.
But the main part is in script I wrote.
LeadSourceDataClass, LeadSourceDataClassItem are helper objects to hold data I retrieve from Dynamics API. LeadSourceDataClassItem contains the title of Lead Source and its Numerical Code, as in Dynamics, Option Set field types, have a title and a corresponding numerical code.
In $(document).ready method, I setup some code and configuration. The helper method XRM.CustomReports.createDatePicker … initialize and setup a field to be Calendar type. $(‘#generateBtn’).click(generateChart) Bind the click event to “generateBtn’” element.
In generateChart method, first we call Dynamics API to retrieve Lead metadata, which in this case we only wants list of defined items of Lead Source options set. We pass each record in the form of LeadSourceDataClassItem, and save it to dataObj. After reading all options ends, we pass a method named to be called. This is the method:
SDK.REST.retrieveMultipleRecords(“Lead”, options, retrieveLeads, function (error) { console.log(error); }, retrieveLeadsCompleted)
It first method accept the Entity you want to query, second accepts the filtering for the query, third is the success call back for each loop, and the last one is called when all the loops are finished being read.
But what I mean by each loop, in above? Using Dynamics CRM API, you cannot read all items of requested Entity, but you have to read it 1000 by 1000.
In retrieveLeads method, I read each lead and if it has value for Lead source, It is being added to list. The method AddToItem, check to which Lead Source it belongs and add it to corresponding list.
I missed to explain how options variable, which we use t filter data initiated. We use a helper method called getFilter, that read Selected Date vales from date pickers and creates the filtering string according to OData standards.
In retrieveLeadsCompleted mehod, we reduce our data to be show in Pie chart. By reduce I mean, reading each Lead Source title, and sum of its data, in this case count of items. Finally I use a helper method to show charts by using Highcharts API.
Here is the helper js file:
var Helper = Helper || {};
Helper.XRM = Helper.XRM || {};
Helper.XRM.CustomReports = Helper.XRM.CustomReports || {};
Helper.XRM.CustomReports.MaxRecords = 100000;
//OptionSet Filter
Helper.XRM.CustomReports.createOptionSetSelect = function (EntityLogicalName, AttributeLogicalName, itemToAppend, addEmptyValue){
SDK.Metadata.RetrieveAttribute(EntityLogicalName, AttributeLogicalName, "00000000-0000-0000-0000-000000000000", true, function (result) {
if (addEmptyValue)
itemToAppend.append('
<option value="null">' + '(Empty)' + '</option>');
for (var i = 0; i < result.OptionSet.Options.length; i++) {
var text = result.OptionSet.Options[i].Label.LocalizedLabels[0].Label;
var value = result.OptionSet.Options[i].Value;
itemToAppend.append('
<option value=' + value + '>' + text + '</option>');
}
},
function (error) { console.log(error); }
);
}
//End OptionSet Filter
//Create Pie Chart
Helper.XRM.CustomReports.createPieChart = function (chartcontainers, title, series, clickevent, tooltipfunction)
{
$(chartcontainers).highcharts({
chart: {
type: 'pie',
},
credits: {
enabled: false,
},
exporting: {
enabled: false,
},
plotOptions: {
pie: {
allowPointSelect: true,
cursor: 'pointer',
dataLabels: {
enabled: true,
format: '<b>{point.name}</b>: {point.percentage:.1f} %',
style: {
color: (Highcharts.theme && Highcharts.theme.contrastTextColor) || 'black',
},
},
point: {
events: {
click: clickevent,
},
},
},
},
series: series,
title: {
text: title,
},
tooltip: {
formatter: tooltipfunction,
},
});
}
//End Create Pie Chart
//Create A Date Picker
Helper.XRM.CustomReports.createDatePicker = function (controlname)
{
new Pikaday({
field: document.getElementById(controlname),
firstDay: 1,
format: 'YYYY-MM-DD',
minDate: new Date('2000-01-01'),
maxDate: new Date('2020-12-31'),
yearRange: [2000, 2020]
});
}
//End Create A Date Picker
//Date Calculation
Helper.XRM.CustomReports.getDefaultStartDate = function()
{
var firstOfThisMonth = moment();
firstOfThisMonth.set('month', moment().month());
firstOfThisMonth.set('date', 1);
return firstOfThisMonth.subtract(6, 'months').format("YYYY-MM-DD");
}
Helper.XRM.CustomReports.getDefaultEndDate = function()
{
var firstOfThisMonth = moment();
firstOfThisMonth.set('month', moment().month());
firstOfThisMonth.set('date', 1);
return firstOfThisMonth.subtract(1, 'days').format("YYYY-MM-DD");
}
//End Date Calculation
This is the final look of chart in a dashboard:
Best Regards
Babak Fakhriloo 