Include records with no value in charts!!!

Update 4/9/2017: Post updated and applies to all versions of Dynamics CRM from CRM 2011 to Dynamics 365.

A major limitation, or so I thought, with Microsoft Dynamics CRM 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.

Rep missing from chart

The example above shows the number of activities 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 have a better overview of 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.

Activities by Owner - Fetch Collection Original

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.

Here’s the new version with the filter. Everything between the two highlighted lines replaces the attribute mentioned.
New fetchcollection in chart xml

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.

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.

Change in measurecollection to match fetch

When imported back into Dynamics CRM/365 I now get this chart

Chart with all Users - final

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 also significantly reduce the need for additional Views by keeping 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 there, starting with the ability to include the value zero in charts like the one above.

Hope you found the tip useful. As always please sign up for my newsletter and follow me on Twitter.

28 thoughts on “Include records with no value in charts!!!

  1. Brilliant post!
    Really neat technique to achieve something which can otherwise be very frustrating. I have an immediate idea about where I can use this for one of my clients where we have a chart to show a count of the Attendees for their Events, broken down by status eg tentative, confirmed, attended. However, new Events don’t show up until there is at least one attendee, which is not a huge deal but this method would make things clearer.

  2. I’ve realised this might help with another frustration. If I have a chart showing Cases by priority and I have carefully coloured the series to use (say) light grey, medium orange and dark red for low, medium and high priority, it is really annoying if there happen to be no “medium” Cases at the moment because high then becomes series 2 and ends up orange. I’ll have to see if this technique can be applied to a stacked chart like that and make a series of zeroes still get treated as a series and “use up” a colour from the palette even though it has no visible data.

    • Hooray! It works! This means I can save space and make my dashboards less cluttered by not needing to include a legend as long as it is well understood within the userbase, for example using a standard red/amber/green or dark/medium/light.
      Performance must inevitably take a hit if you use views which return a huge dataset and then filter it in the chart instead, but may not be a big issue if you design carefully.

  3. Is there a way to get this to work with dates?
    Lets say.. show invoices for the last 12 months aggregated on a monthly basis.
    I tried to follow the guide, but cant seem to get it to work.

  4. I am trying to make dashboard showing appintment activity last, this and next week. I am having problems showing all sales reps and locking the colors to the different meeting stages (scheduled, open, canceled and completed).

    I have tried to alter the xml file, but when I get to entities or attributes I don’t have in my CRM system, I get lost.

    You should think about offering 1 hour sessions via remote desktop 🙂

    • Hi Lars
      Sounds like your main challenges is that you need full control on two types of data. You wan’t to make sure you have all the sales people in every chart, AND you want to specify colors for the different meeting stages.
      The sales person part you can solve by incl. dummy records in the same manner as this post describes.
      To specify the colors of the meeting stages, you would need to use something like a multiple series stacked chart, so you can color each type individually and stack them appropriately. That approach is described here https://crmchartguy.wordpress.com/2013/03/10/aggregate-total-on-top-of-stacked-column-charts-or-bar-charts-in-ms-crm-2011/
      A good trick for getting the mxl pieces you need, is to go to Advanced Find and create the query you want, add the right fields, then save it and download the FetchXML. That’ll give you filters, fields and entity names you need.
      I am a Consultant, so by nature I am of course available for hire. Hope the above helps, otherwise you can contact me at ubc (at) alfapeople.com

  5. Can I ask a question about this?
    Is it also possible to make percentage charts based on a column having or not having a value? Simple example: what is the percentage of email addresses filled in per contact (per created on date).

    • Thanks for the tips.

      However, I find it doesn’t work in my scenario. I have a stacked up bar chart with multiple series (about 6) and one of them is to display the null value like below, it wouldn’t pick up the count of those null value and show as blank on the chart:

      Do you have any idea? Thanks again.

  6. Does this work for 2013 as well? I keep getting the “The specified XML file “Activities by Owner with zero count.xml” is either not valid XML or does not conform to the chart schema” error when importing and i believe i followed the instructions correctly.

  7. I’m working on developing the chart where I’m comparing Last Year’s revenue to This Year’s revenue by month. I’m all good with your instructions up until the step where you state from having the months from both years on both X-axis to separating 2014 on the bottom axis and 2015 on the top axis (ie the secondary X axis has been added to the top of the chart). The moment I add this code to the Xaxis:
    Minimum=”0″ Maximum=”12.5″ IntervalOffset=”1″ Interval=”1″

    and this to Xaxis2:
    Minimum=”12″ Maximum=”24.5″ IntervalOffset=”1″ Interval=”1″

    My chart not only removes labels across the top axis, but also doesn’t display the results from THIS year. However, it displays months from both this year and last year on the bottom axis.

    Any thoughts?

    • Also, when you import the XML the firt time around, your chart shows only 1 Y Axis. I have one on the right and one on the left. I have two Y axis giving me the Revenue whereas you only have one…

  8. I have a line chart that shows number of qualified and disqualified leads by week. If for a certain week, I have 0 disqualified leads, and some qualified leads, the line chart for the disqualified lead breaks off during that week. Is there a way to show that null value as a 0 value so that disqualified line chart doesn’t break off?

  9. Thanks for this post! I was trying to use this approach with “sum” as the aggregator, but looks like this does not apply to “sum”!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s