A major limitation, or so I thought, with MS CRM 2011 charts, is that they can’t display data points that are not included in the dataset. Non-existing data could be just as important to the chart and overview as the data that’s actually present.
A common example, especially here at the beginning of the New Year, are charts showing User activity for a given time period. At the beginning of the period, not all Users have created new activities yet, and are therefore not included in the chart at all.
The example above show the number of activties for the current week for Sales Rep 1 and 3. Sales Rep 2 have no activities yet for the current week, so the chart does not include that User. In order to better overview our organization, we would want to include Sales Rep 2 with a count zero activities. Doesn’t sound like too much to ask, right? In the example with 3 reps, it might not be as important, but even with just 7-8 or more Users, a missing person or item could easily get lost. Besides, a good chart shows the necessary data immediately. Slowing down to count items to see who’s missing is not the approach we want.
The trick to include the zero data lies in a combination of adding a filter on a single attribute within the chart xml file, and having a larger set of records filtered by the View attached to the chart.
Or to put it another way; to create the chart we’ll have a View showing All Activties, but then we’ll add a filter to the chart, so it only counts the values that meet our criteria. In this case; Activities with a Start Date in This Week.
I’ll use the system chart “Activities by Owner” as a base and make modifications to that.
Above is the original. We want to replace the highlighted attribute in the fetchcollection of the xml file, so it includes a filter for our criteria. Currently it just counts everything.
IMPORTANT: We can’t just add the filter to the attribute in line with the rest of the fetchcollection. If we did that, the filter would apply to the whole data set and we would be back where we started. We only want the filter to apply to the data we are counting, but make sure all the other data is still included. To achieve that we have to make a link-entity to the same entity that we are already on. The process is similar as if we wanted to create a link to a different entity, except it’s self-referential.
We add the link-entity information along with the attribute we are counting and add the filter below it.
Also note I changed the aggregate from “=count” to “=countcolumn”. If left at “=count”, it would count all the records in the dataset and not just the ones we want filtered.
Note: The easiest way to create the filter expression in xml is by making them in Advanced Find and then downloading the FetchXML file from there.
Remember also to match the alias in the measurecollection with what we named it when aggregating it.
When imported back into CRM I now get this chart
Now it is showing all my Sales Reps, incl. the one that does not have any data matching the criteria. Now I can easily see who have not created any activities yet this week.
It’s a little work to add zeros to a chart, but that piece of data can be just as important as everything else.
I’ve experienced that MS CRM 2011 implementations can get very long lists of Views over time, especially as charts can need their own View to go with it. This approach could signifcantly reduce the need for addtional Views and keep the filtering within the charts themselves.
I’ve just started experimenting with these self-referential link-entities in the chart xml, but it seems there is some interesting potential hidden there, starting with the ability to include the value zero in charts like the one above.