Better line charts in Dynamics CRM 2011

In part I of this series, I showed how to query Microsoft Dynamics CRM for aggregate data using FetchXML and then pass the results to Flot to generate a line chart. In this second part, I will expand on that to show how to query for and chart multi-series data. For my example today, I will be creating a chart that shows the number of contacts created by date and state (address, not statecode). In my CRM instance, I have created a set of contacts using characters from Cohen brothers movies. (Please, hold your "Big Lebowski" quotes.)

Changes to the previous approach

There are three main things we have to do differently here than before:

Update the FetchXML query to include an additional group-by field
Update the results parser to handle the additional group-by field
Send Flot an array of data series instead of just one

In addition to that, I have improved the original code in two more ways:

Fill in missing date values with 0 so the shape of the line better reflects reality
Shade weekend days in gray on the chart grid to help break up the weeks and highlight areas in which we might logically expect no activity to occur

Updated FetchXML

Changing the FetchXML query is easy. We just have to add the address1_stateorprovince field:

<fetch distinct='false' mapping='logical' aggregate='true'>  
<entity name='contact'>  
<attribute name='fullname' alias='plotValue' aggregate='count' />  
<attribute name='address1_stateorprovince' groupby='true' alias='seriesLabel' />  
<attribute name='createdon' groupby='true' dategrouping='day' alias='day' />  
<attribute name='createdon' groupby='true' dategrouping='month' alias='month' />  
<attribute name='createdon' groupby='true' dategrouping='year' alias='year' />  
</entity>  
</fetch>

The field we will use to create the data series is aliased as "seriesLabel" so we can have as generic a results parsing method as possible.

Updated results parser

The manner in which I wrote the getKeyValPairs function in part I won't return the seriesLabel field as part of the return object because only the numeric values come back in the a:FormattedValues element. The seriesLabel field is available via the a:Attributes element, so this required a minor change to my XML find logic. If you have downloaded my previous charting sample, you should be able to update the getKeyValPairs function in it with the newer version here without any issues.

function getKeyValPairs(entity, valuefield) {  
/// <summary>  
/// extracts an aggregate value and grouping components from a FetchXML response entity element and returns them in an array  
/// </summary>  
var outputArray = new Array();  
  
//first, get the aggregate value using its alias ("plotValue" in the original example)  
var pointValue = parseFloat(entity.find("a\\:KeyValuePairOfstringstring").filter(function() {  
return $(this).find('b\\:key').text() == valuefield;  
}).find("b\\:value").text().replace(/,/g,''));  
var groupingArray = [];  
  
//second, get the values for the group-by fields  
//unlike previous example, this uses the a:KeyValuePairOfstringanyType element   
//so we go down an extra "value" level when populating the value variable  
entity.find("a\\:KeyValuePairOfstringanyType").each(function() {  
return $(this).find('b\\:key').text() != valuefield;  
}).each(function() {  
var xmlElement = $(this);  
var key = xmlElement.find("b\\:key").text();  
var value = xmlElement.find("b\\:value").find("a\\:Value").text();  
var groupingText = '"' + key + '":"' + value + '"';  
groupingArray.push(groupingText);  
});  
var stringToEval = groupingArray.join();  
var dateFields = eval("({"+stringToEval+"})");  
outputArray.push(pointValue);  
outputArray.push(dateFields);  
  
return outputArray;  
}

Sending Flot the data

Flot expects data to be passed as an array of objects like this:

{   
label: "Series Label",   
data: [[x1,y1],[x2,y2]...   
}

In our example, we would have a separate object for each state's data. To build the array of data objects, we do the following:

Get a list of unique series labels (states)
Create a holding array for each series' data points
Assign each data point to its correct series in the holding array
Build the data array Flot expects from the holding array

As part of building the data array for Flot, we also do some date manipulation to fill in missing dates with 0 values.

Here are the functions that do all of this:

function processData(xml) {  
/// <summary>  
/// charts data returned in a FetchXML response using Flot  
/// </summary>  
  
alert("Charting started");  
  
//array to hold series labels  
var seriesList = [];  
  
//array to hold the series data before we get it ready for flot  
var pointSeries = [];  
  
//array to hold data to pass to flot  
var dataSeries = [];  
  
//array to hold all each unique date in our data set   
//this is so we can show dates with no data between the start and finish dates as 0  
var dateArray = [];  
  
//loop through xml and extract data from each "entity" element  
$("#chartdiv").append("Starting XML parsing . . . <br />");  
$(xml).find("a\\:Entity").each(function() {  
//pass the element to a function that extracts the aggregate value and the "group by" values  
//"plotValue" is the FetchXML alias for the data we want to display  
var pointData = getKeyValPairs($(this),"plotValue");   
  
//for each unique series label value  
if(seriesList.indexOf(pointData[1].seriesLabel) == -1) {  
//add label to array of labels  
seriesList.push(pointData[1].seriesLabel);  
  
//instantiate a new array to hold data for this series  
pointSeries.push(new Array());  
}  
  
//Flot requires datetimes to be passed as timestamp values  
var pointDate = new Date(pointData[1].year, pointData[1].month-1, pointData[1].day);  
var timestamp = pointDate.getTime();  
  
//for each unique date value  
if(dateArray.indexOf(timestamp) == -1) {  
//add date timestamp to array of dates  
dateArray.push(timestamp);  
}  
  
//get the index of the series to which this particular value corresponds  
var seriesNumber = seriesList.indexOf(pointData[1].seriesLabel);  
  
//add this particular data value to the correct array in the point series array  
pointSeries[seriesNumber].push([timestamp, pointData[0]]);  
  
});  
  
//sort the unique dates so we can find the min and max dates later  
dateArray.sort(function(a,b){return a-b;});  
  
//we have a function that fills in missing dates with 0 values  
//so to make sure all series have the same set of data, we will add a dummy date to both ends of the range with 0 values  
//after we have "filled out" any missing values in the series, we will remove the dummy dates  
//get milliseconds per day so we can just add/subtract the value of the "day" variable later  
var minute = 1000 * 60;  
var hour = minute * 60;  
var day = hour * 24;  
  
//for each series  
for(var i=0; i<seriesList.length; i++) {  
//the label we pass to Flot is the value from the series label array  
var seriesLabel = seriesList[i];  
  
//get a time series data variable from the correct array in the pointseries aray  
var tsData = pointSeries[i];  
  
//add a day with a 0 value to the beginning of the series  
tsData.push([dateArray[0]-day,0]);  
  
//add a day with a 0 value to the end of the series  
tsData.push([dateArray[dateArray.length-1]+day,0]);  
  
//sort the data points by date  
tsData.sort(function(a,b){return a[0]-b[0];});  
  
//fill in missing dates with 0-value points  
tsData = newDataArray(tsData);  
  
//remove the first "dummy" date  
tsData.shift();  
  
//remove the last "dummy" date  
tsData.pop();  
  
//create a new object to represent this series  
var seriesObj = new Object();  
  
//set a label property  
seriesObj.label = seriesLabel;  
  
//set a data property  
seriesObj.data = tsData;  
  
//add this object to the dataseries array that will be passed to Flot  
dataSeries.push(seriesObj);  
}  
  
//plot the data with some display options  
//see examples and API documentation at http://www.flotcharts.org/  
$.plot($("#chartdiv"),   
dataSeries, {   
series: {  
lines: { show: true },  
points: { show: true }  
},   
grid: {   
hoverable: true ,  
markings: weekendAreas //calls the weekendAreas function to shade weekend days in gray  
},  
xaxis: {   
tickLength: 5,  
mode: "time",  
timeformat: "%m/%d/%y"  
},  
yaxis: {  
tickDecimals: 0,  
min: 0  
}  
}  
);  
  
alert("Charting complete");  
}  
  
//this function is used to fill in the missing date values with 0 values  
//taken from http://stackoverflow.com/questions/14522667/how-to-configure-flot-to-draw-missing-time-series-on-y-axis-at-point-zero  
function newDataArray(data) {  
var startDay = data[0][0],  
newData = [data[0]];  
  
for (i = 1; i < data.length; i++) {  
var diff = dateDiff(data[i - 1][0], data[i][0]);  
var startDate = new Date(data[i - 1][0]);  
if (diff > 1) {  
for (j = 0; j < diff - 1; j++) {  
var fillDate = new Date(startDate).setDate(startDate.getDate() + (j + 1));  
newData.push([fillDate, 0]);  
}  
}  
newData.push(data[i]);  
}  
return newData;  
}  
  
  
/* helper function to find date differences*/  
function dateDiff(d1, d2) {  
return Math.floor((d2 - d1) / (1000 * 60 * 60 * 24));  
}

Format for the weekends

The final difference from part I is calling a custom function to format the weekends on the chart. This is done in this part of the processData function above:

grid: {   
hoverable: true ,  
markings: weekendAreas //calls the weekendAreas function to shade weekend days in gray  
},

The weekendAreas function:

//helper for returning the weekends in a period  
//taken from http://www.flotcharts.org/flot/examples/visitors/index.html  
function weekendAreas(axes) {  
var markings = [];  
var d = new Date(axes.xaxis.min);  
// go to the first Saturday  
d.setUTCDate(d.getUTCDate() - ((d.getUTCDay() + 1) % 7))  
d.setUTCSeconds(0);  
d.setUTCMinutes(0);  
d.setUTCHours(0);  
var i = d.getTime();  
do {  
// when we don't set yaxis, the rectangle automatically  
// extends to infinity upwards and downwards  
markings.push({ xaxis: { from: i, to: i + 2 * 24 * 60 * 60 * 1000 } });  
i += 7 * 24 * 60 * 60 * 1000;  
} while (i < axes.xaxis.max);  
  
return markings;  
}

Trying it out

Here is a complete web page with the code for you to download and try out in your own CRM instance chart_example2.htm (12.34 kb). Just upload it as you would any other web resource, publish and open.

When I run it in my CRM instance, this chart is generated.

As you can see, the chart gets a little busy, and it's hard to differentiate between the different series where they overlap, but there's a lot more you can do with Flot formatting options to make it look better. Also, I haven't covered everything Flot can do, so I encourage you to look at the Flot examples page to see some other possibilities like bar charts, stacked charts, panning/zooming, etc.