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.