data visualizations - Alexander Development

Better line charts in Dynamics CRM 2011 - part II

Lucas Alexander — Sun, 27 Jan 2013 00:00:00 GMT

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:

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) {  
///   
/// extracts an aggregate value and grouping components from a FetchXML response entity element and returns them in an array  
///   
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) {  
///   
/// charts data returned in a FetchXML response using Flot  
///   
  
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 . . . 
");  
$(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 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.

Better line charts in Dynamics CRM 2011 - part I

Lucas Alexander — Thu, 24 Jan 2013 00:00:00 GMT

Earlier this week I posted an entry about using FetchXML and JQuery in a Dynamics CRM 2011 web resource. The reason I first started looking at those two together was that I wanted to see if I could generate better looking line charts than are available out of the box (spoiler alert: I did). In this post I will show how to combine FetchXML and JQuery with the Flot plotting library to produce line charts from CRM data aggregations.

In my career, one of the data visualizations I have encountered the most is to plot some value over unit time. For example, we can try to measure an agent's productivity by counting the number of phone calls per day. Because of the way Dynamics CRM stores data, if we restrict our queries to its database instead of a data warehouse, generally we can report on actions taken per unit time, but we can't report on how things change over time. We can say Agent X created 100 phone call records last week, but we can't tell how many overdue phone call records Agent X had in the queue yesterday. In my example here, I will show how to plot number of contacts created per day.

Top of the page stuff

Charting with Flot requires jQuery, Flot (obviously) and Excanvas in Internet Explorer. Here is how I pull those libraries in from CDNs, but if your CRM instance is accessed over a LAN, you should host them locally.

Also, Flot doesn't work so well in Internet Explorer quirks mode, so make sure you set the page to display in standards mode by declaring an HTML doctype like so in the first line:

Writing the query

As I explained previously, if you want to query aggregate data from inside JavaScript, you have to use FetchXML. While researching this topic, the examples I found weren't necessarily the clearest, but MSDN has a good overview here.

Once you get the hang of the FetchXML syntax, if you can write your query in SQL group-by syntax, it shouldn't be too difficult to translate it to FetchXML. Here is the query we will use to count contacts by their createdon date:

We have a single data field aliased as plotValue that is equivalent to SQL's count(fullname) as plotValue, and then there are three groupby fields that are based on the createdon field. They have each been given an alias that corresponds to the specific datepart.

Executing the query

To execute the FetchXML and retrieve the results, we will use the same basic technique I showed in my previous post.

Get FetchXML query from a textarea field.
Build a SOAP message.
Send the SOAP message to CRM and register a callback function to process the data.

To do this, we will use the same function executeFetchCommand, buildFetchRequest and sendQuery methods from my previous post. We will be using a different processData method, though the name will stay the same.

Charting the data

Once the data is returned by CRM, we can generate the chart. To do that, we will do the following:

Loop through XML to extract count for each date.
Convert the count and date values to an array of X and Y values for Flot to plot.
Call the Flot plotting method.
Bind an event to our chart to display tooltip messages that show the X and Y values when the user mouses over a specific point.

First, our new processData callback function looks like this:

function processData(xml) {  
///   
/// charts data returned in a FetchXML response using Flot  
///   
  
alert("Charting started");  
  
//create a label for the chart points  
var yLabel = "Contacts created";  
  
//create an array to hold the plot data  
var series1 = [];  
  
//loop through xml and extract data from each "entity" element  
$("#chartdiv").append("Starting XML parsing . . . 
");  
$(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");   
  
//Flot requires datetimes to be passed as timestamp values  
var dateString = (pointData[1].year + "-" + pointData[1].month + "-" + pointData[1].day).replace(/,/g,'');  
var timestamp = new Date(pointData[1].year.replace(/,/g,''), parseInt(pointData[1].month)-1, pointData[1].day).getTime();  
  
//add x (timestamp) and y (aggregate data value) values to the data series array  
series1.push([timestamp, pointData[0]]);  
});  
  
//sort series array by date - Flot plots in order, so line can look wrong if we don't do this  
series1.sort(function(a,b){return a[0]-b[0];});  
  
//plot the data with some display options  
//see examples and API documentation at http://www.flotcharts.org/  
$.plot($("#chartdiv"),   
[ { data: series1, label: yLabel} ], {   
series: {  
lines: { show: true },  
points: { show: true }  
},   
grid: { hoverable: true },  
xaxis: {   
tickSize: [1, "day"],  
mode: "time",  
timeformat: "%m/%d/%y"  
}  
}  
);  
  
alert("Charting complete");  
}

In this method first we loop through the data and pass each element to a getKeyValPairs helper function to extract the necessary fields. It returns an array with the Y value to plot as element 0 and a JSON object containing all other fields as element 1. It looks like this:

function getKeyValPairs(entity, valuefield) {  
///   
/// extracts an aggregate value and grouping components from a FetchXML response entity element and returns them in an array  
///   
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 = [];  
entity.find("a\\:KeyValuePairOfstringstring").filter(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").text();  
var groupingText = '"' + key + '":"' + value + '"';  
groupingArray.push(groupingText);  
});  
var stringToEval = groupingArray.join();  
var dateFields = eval("({"+stringToEval+"})");  
outputArray.push(pointValue);  
outputArray.push(dateFields);  
  
return outputArray;  
}

Once the data fields are returned by they getKeyValPairs method, the processData function creates a timestamp from the supplied month, day and year fields (Flot requires timestamps to plot by datetime), and then adds the timestamp and count to an array.

After the loop is complete, we sort the array by date to make sure the line will be plotted along our points in the correct order. Finally we call the Flot plot function with the data array and some pre-determined options. There are a lot of cool configuration options you can specify for Flot plots, but explaining them is beyond the scope of this point. Please take a look at the Flot examples page for more.

Finally we need some code to do the tooltip display. First we have a generic showTooltip method that will be executed when the mouse moves over a data point. You can customize the style to your liking.

function showTooltip(x, y, contents) {  
///   
/// function to show tooltip when user mouses over data points  
///   
$('' + contents + '').css( {  
position: 'absolute',  
display: 'none',  
top: y + 5,  
left: x + 5,  
border: '1px solid #fdd',  
padding: '2px',  
'background-color': '#fee',  
opacity: 0.80  
}).appendTo("body").fadeIn(200);  
}

Then we have code that binds a function to the chart plothover event so that the showTooltip method gets executed at the right time with the right data:

//bind a function to execute the showTooltip function at the appropriate time  
var previousPoint = null;  
$("#chartdiv").bind("plothover", function (event, pos, item) {  
$("#x").text(pos.x.toFixed(2));  
$("#y").text(pos.y.toFixed(2));  
  
if (item) {  
if (previousPoint != item.dataIndex) {  
previousPoint = item.dataIndex;  
  
$("#tooltip").remove();  
var date = new Date(item.datapoint[0]);  
var x = item.datapoint[0].toFixed(2),  
y = item.datapoint[1];  
  
//call showTooltip with the timestamp converted back to mm/dd/yyyy format  
showTooltip(item.pageX, item.pageY,  
item.series.label + " on " + (parseInt(date.getMonth()+1)) + "/" + date.getDate() + "/" + date.getFullYear() + " = " + y);  
}  
}  
else {  
$("#tooltip").remove();  
previousPoint = null;   
}  
  
});

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_example.htm (8.30 kb). Just upload it as you would any other web resource, publish and open. At that point you will be presented with a screen that looks like this:

Either leave the pre-populated FetchXML in the textarea or supply your own that does a count by month, day and year. As long as it returns those three group-by fields and a Y value aliased as "plotValue," it should work. Once you click the "fetch and process" button, you will start to see some output and a couple of status alert windows before the output finally displays. The end result should look like this:

Of course this chart is actually not that exciting, but once you take a closer look at how you can format the line chart output with Flot, you'll see there are all sorts of interesting things you can do. You can also embed the charts in dashboards as a replacement for the existing Dynamics CRM line chart controls. Stay tuned for my next post when I show how to generate multi-series charts.