Include records with no value in charts!!!

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.

Rep missing from chart

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.

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 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.

Change in measurecollection to match fetch

When imported back into CRM 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 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.

Advertisements

, , , , ,

  1. #1 by Adam Vero on January 24, 2013 - 12:23 pm

    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. #2 by Adam Vero on January 24, 2013 - 12:31 pm

    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.

    • #3 by Adam Vero on January 24, 2013 - 3:08 pm

      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.

      • #4 by crmchartguy on February 15, 2013 - 4:43 pm

        Great idea for using it on Cases. I think I’ll use that scenario for a future blog post as it can present some other obstacles as well.

  3. #5 by Rune Daub on February 11, 2013 - 12:00 pm

    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.

    • #6 by crmchartguy on February 11, 2013 - 4:54 pm

      Yes, but you would need to make sure you have dummy records for each month that are included in your “View”, but are excluded in the Chart xml.

      • #7 by Chris Manners on July 20, 2015 - 7:27 am

        How do you get dummy records for each month?

      • #8 by CRM Chart Guy on July 20, 2015 - 11:08 am

        In the case of invoices, you’d create an invoice for each month with zero value so the dummy record wouldn’t affect the sum. If you are counting records you’d have to factor it in the xml. There are some details on it in this post.

      • #9 by Chris Manners on July 20, 2015 - 1:28 pm

        Is there any way to do it without dummy records?

  4. #10 by Lars on March 14, 2013 - 6:42 am

    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 🙂

    • #11 by CRM Chart Guy on March 14, 2013 - 5:34 pm

      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. #12 by Rimmer on March 28, 2013 - 1:40 pm

    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).

    • #13 by TP on February 26, 2014 - 3:01 am

      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. #15 by Noel on May 29, 2014 - 11:00 am

    Excellent article! Very simple to do and very effective. Thanks for posting

  7. #16 by Julie Massin on April 8, 2015 - 2:24 pm

    ❤ ❤ ❤

    Thank you so much for the great tips.

  8. #17 by cweiner on August 7, 2015 - 6:06 am

    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.

  9. #18 by sharjeelbutt on September 16, 2015 - 8:32 am

    How do you download the fetchxml for the filters?

    • #19 by CRM Chart Guy on September 16, 2015 - 9:33 am

      Open Advanced Find and create your query in the visual editor. In the menu there will be a button called Download Fetchxml.

      • #20 by sharjeelbutt on September 18, 2015 - 5:32 am

        I should’ve commented the moment I figured it out. Thanks for the reply though!

  10. #21 by sharjeelbutt on September 18, 2015 - 5:38 am

    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?

    • #22 by sharjeelbutt on September 18, 2015 - 7:11 am

      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…

  11. #23 by Avneet on November 5, 2015 - 3:19 pm

    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?

  12. #24 by Adi on October 4, 2016 - 8:39 am

    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”!

  1. Specify Exact Colors for Each Priority in the Microsoft Dynamics CRM 2011 Case Chart | crm chart guy
  2. Multiple entities in one MS CRM chart | crm chart guy

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

%d bloggers like this: