Update 4/14/2017: Post updated to apply to all version of Dynamics CRM from CRM 2011 to Dynamics 365.
I’ve already written one post about a short-coming of the out-of-the-box Cases By Priority Chart. That post addressed a relatively minor nuisance about how cases with the priority level “Normal”, are colored red, while the “High” priority items are blue, when the opposite seems more natural.

However, a more pressing issue is that the Dynamics 365/CRM chart only colors the series that are available in your View. This means it would be impossible for the viewer to decode a chart without reading the Legend every single time.
Example
These two case charts look the same, but the work that will go into handling them, certainly is not. The user must carefully read the legend, every time, before they know what priority the blue color is representing.
To improve on those issues for the Case Chart in Microsoft Dynamics 365/CRM I will do the following:
- Ensure that the Priority levels have meaningful colors
– High (Red)
– Normal (Yellow)
– Low (Green) - Ensure that the same color is always on the same priority level, i.e. Red is always high
- If there’s no cases with priority high, then there’s no Red color.
The result in this scenario should be:
- A Dynamics CRM Chart that shows all Cases in the legend
- Cases for the current week, with days along the X axis
- Cases stacked according to Priority are always in the same order
To achieve this, we need to combine some approaches that I’ve blogged about before.
Modify the order of the colors
Order the data so the Priority series are always colored the same
We also need to make sure that the levels High, Normal and Low are always a part of the series, even if they are no Cases with that type present. This is the tricky part as it includes adding records with no or zero value to our Dynamics CRM Chart.
Note: The method I am using here works by adding a set of dummy records to the dataset, but without actually counting them in the chart. However, creating a set of dummy records may not always be preferable. It is also possible to create this priority stacked column chart, without the use dummy records, and still get all values into the legend and define the exact colors for each series. That method also comes with its own set of drawbacks, but I’ll have to save that for a later blog post.
Step 1 – Set the Order of the Priorities
I have exported the default Cases By Priority (Per Day) chart. The only modification I have made to the original XML, is that I’ve replaced the “Created On” date, with the “Follow Up By” date. The only reason for this change is that it is faster to input demo data.
To sort the data I add a line with an order clause on the priorities, with descending=”true” to put the value “High” on top of the chart.

Let’s import the chart XML back into Dynamics 365/CRM and see what it looks like.
Now we got them in the right order with “High” on top.
The chart here is ordering according to the values in the Option Set and not alphabetically so some experimentation may be needed.
Note: Ordering by Option-Sets in the chart xml is not an exact science in MS CRM 2011 Charts. Particularly in situations like this, where the Option-Set is the secondary groupby clause. The date is the first groupby clause because we need that on the X axis. If we had added an extra Priority level to the system Option-Set, we would likely see some odd orders regardless of how we sort them.
Step 2 – Changing Colors in the Dynamics 365/CRM chart to match specific series
The stacked bar chart orders the colors from the bottom and up, which mean that I’ll have to insert the colors in the custom palette in order of Low, Normal, High. Remember I sorted them so I had High on top, meaning it’s the last one to get a color from the Palette.
Chart XML for the priority chart – with original colors in RGB, and what I’ve replaced them with.
Original

With new colors

I use the color names. They are much easier when testing than the RGB codes. If needed, we can always adjust them afterwards to the exact color nuance using either HEX, RGB or ARGB codes.
Note: I could have just written Green Yellow Red in the XML, but those colors just looked horrendous for this CRM chart. An overview of all the colors available in CRM Charts can be found in this blog post.
Here’s the chart, after importing the chart XML, with the new colors and the desired order.
Step 3 – Make sure the colors don’t change depending on available records
However, if we have a week where not all types of cases are present, we still get the wrong color on our chart, so we need to ensure that we always have at least one record of each priority type.
To achieve that we will:
- Create a set of dummy records. Three records, one for each Priority type
- Adjust our View to include the dummy records
- Modify our CRM Chart XML to not show them, but only create them in the Legend
I’ll modify my case View to show only cases for this week, which is what I want the chart to show. Then add the specific date that I have created the dummy records on.
Here’s the Advanced Find View using the OR parameter to include the date for the dummy records.
I’ll use 1/1/1950 for the example as it is way outside our possible date range for the cases.
Here’s the Dynamics CRM chart now, including the dummy records.
Now we need to modify the CRM Chart XML so the dummy records do not display in the chart.
The highlighted attribute above in the fetchcollection counts the number of Cases. (The entity name for Cases is “incident”). This is the attribute we need to change, so it only counts cases for this week.
I’ll do that by adding a self-referencing link-entity and adding a filter to the attribute.
I’ve replaced the count attribute with everything between the highlighted <link-entity> tags above.
What happens is that we create a self-referential link to the incident entity and add a filter with the “this-week” condition. The <link-entity> allow us to apply a filter to this attribute only. Had we just added the filter to the attribute directly, it would have applied to the entire chart, and thus excluded our dummy records completely.
Highlighted and underlined in red is the aggregate, changed from “count” to “countcolumn”. If you forget this part, the filter will not be applied and include our dummy records in the count. “countcolumn” is normally used for counting non-empty records, but when applied in this manner, it will count only the records that matches the filter following in the XML.
Let’s import the latest Dynamics 365/CRM chart XML and have a look.
Excellent. The Jan 1 1950 day is now empty, even though we know there are three records in the data set. The fact that we can see the date, 1/1/1950, proves that they are there.
To check if the colors stay true to their designated series, I’ll go ahead and delete all the cases, in this week, with priority “Normal”. In the old chart, we would then see all the priority “High” cases get the next color in line, which in this case would be the khaki yellow.
Great, we can now see that the chart Orders all the Priorities correctly, regardless of whether or not they are a part of the current record set. Another bonus is that the legend still show us all the types of priorities.
In a previous post, someone commented with a very good suggestion of perhaps even leaving out the legend to utilize the space better, since the color-coding does not change and is implicit. Alternately, if you repeat the same color scheme, you could just leave the legend on one of the charts for reference.
Ok, so what about that 1950 date for the dummy record!
Step 4 – Modify the X and Y axis
Had our chart not used a date on one of the axes, such as a pie chart or a stacked column chart by User, we would have been done now. Those charts would have nowhere to display the 1950 date of the dummy records.
The process that enabled us to include all the Priorities, also adds the extra date to the X axis.
We’ll edit the chart XML one last time and remove that date. Since we are modifying the X axis anyway, we might as well do a few other improvements at the same time.
On AxisY, I added Title=”Number of cases”. “Number of cases” just seems like a much better descriptor than “Count:Non-empty (Case(Case))”.
On AxisX, I changed the TitleForeColor to “Transparent” and reduced the TitleFont to 5px. Since the chart is already writing the day date, it would be redundant for the Dynamics 365/CRM chart to also tell me that the columns are grouped by day. You cannot remove the axis title on CRM charts, but making them transparent more or less achieves the same thing.
Lastly, the most important addition is adding Minimum=”1” to the X axis. This will skip the first series and start the chart with the first day of the current week. The X axis by default starts at “0”, so setting it to start at “1” instead, will skip the first day of the series, which also happens to always be the date of our dummy records.
One last look at the chart. High, Normal and Low are all in the legend even though we only have records with the High and Low Priority. There are only dates for the week that we want.
The Y axis title is short and precise. No title on the X axis. We are clearly looking at days.
Now, we just might want to adjust those colors a little, but that’s a different story.
Hope you enjoyed it. Please subscribe to my newsletter or follow me on Twitter. Follow @crmchartguy
Thanks for taking the time with this post, this was helpful.
A different chart I’m working on may not be a good candidate for the dummy records though, so I’m quite interested in seeing details about the alternative method you mentioned. My chart will be placed on the form of a parent record and the content records will utilize the ‘Only Related Records’ feature, which don’t guarantee I will have representation from each color band, or allow me to inject dummy records into that view.
Thanks for reading.
That method is used in this post https://crmchartguy.wordpress.com/2013/03/10/aggregate-total-on-top-of-stacked-column-charts-or-bar-charts-in-ms-crm-2011/
I’m trying to build a “Count:Non-empty” bar chart that illustrates the number of Appointments by owner but only want owners with >1 to appear on the x axis. Unfortunately it’s putting the Owners name and zero’s even though they don’t have anything. Is there a way to just make owners with at least 1 appointment appear? Leave off the zeros? I thought that was the point of Count:Non-empty??
Any help is appreciated. 🙂
I am trying to build a chart at record level that will give me the performance (Green, Red, Yellow) for each month. So, when I go to entity A, I have a sub-grid that will display the status on each month. I need a chart with colors exactly matching the status for months. We would not be able to add dummy status record for each entity record. Any help is appreciated.
If you can filter the statuses into three different series, then you can color each series individually and you won’t need any dummy records. The method is described in this post https://crmchartguy.wordpress.com/2013/03/10/aggregate-total-on-top-of-stacked-column-charts-or-bar-charts-in-ms-crm-2011/
Thanks for your reply. I will not be able to group them into series. For example, for one account, i will have status for 12 months, Jan – Green, Feb – Yellow, March – Red. In Account form, I should see this as chart with appropriate colors.
Is there a way to remove the chart icon when the chart is placed in a form? I know this is not a property of chart, but would be interested to know how to do this.
I have a question if I can change the colour on a chart based on the achieved percentage on a Goal. For example if I have archived 50% then the colour on the bar should be green but if I have only achieved 40% then I should have yellow as the colour on the bar.
Hi Håkan. That is possible, but you’d have to create two series or more depending on how many steps you need. An approach with only two colors could look like this. One series filtered for 50% and over and one series filtered for 49% and lower. Both stacked charts. Then you can assign the colors to the series individually. The filters will ensure that only the bar of the right size and color is being shown depending on the value.
Hi, is there a way to show use different colors for the same series, as in each individual record is represented in a different color?
Yes, but you can only use one of the predefined palettes for this.
https://crmchartguy.wordpress.com/2012/08/23/palettes/
Custom palettes, last I checked, are not supported in CRM for series.
Hi,
I have tried the above with a funnel chart but I keep getting invalid XML, is there something specific I have to do if its a funnel chart?
Unfortunately, this is not possible on funnel charts as they only support one series.
Hello,
We are wondering if step 3-4 is possible in CRM 2013? We can’t seem to figure out the XML ‘link-entity’.
Any help would be greatly appreciated.
Thanks!
Yes, all my chart xml tips should work on any version of CRM since 2011, incl the new Dynamics 365. You may want to check out this post for more details how the link-entity work. https://crmchartguy.wordpress.com/2013/03/10/aggregate-total-on-top-of-stacked-column-charts-or-bar-charts-in-ms-crm-2011/
Hello,
I was so happy to find this post!
Does it work the same with simple bar charts?
I have followed your steps, but get an error when i try to upload into CRM “Unsupported condition operator”
The only step i did not complete was Step 1 as I am not using a Stacked Bar Chart.
Any tips?
Thanks!
Hi, I’m trying to do the same thing with a simple column chart.
It’s showing customer survey responses to the question, “How likely are you to continue using our website?”, where there are 5 choices ranging from “Very Unlikely” to “Very Likely.” I’d like to have the “Very Unlikely” column show up as red, then so on through green for “Very Likely”.
Have you figured this out yet?
In this case I’d probably create a series for each of the categories and assign them a color directly in the series instead. That way you can also avoid including dummy records of any kind.
When I add the self-referencing link entity, it wipes the values from the whole chart. It displays where all the columns would have been, but they are marked with a number zero. Anything I should be on the lookout for?
What if your chart displays names instead of dates? I have everything set up properly and the ‘data’ for the dummy data is hidden, but the names still show. I can’t use Minimum=”1″ because the dummy data may be columns 1, 2, 5, 6, etc. Is there a way to hide the dummy data in this situation? Thanks!
Did you ever wrote a post on how to do that without adding dummy values? I’m interested in knowing that.
Hi,
Your blog is very useful.
I need to create a multi-stack bar graph based on the option set values.
Based on the value, the color should be displayed in the bar graph. I had exported the chart and updated the color values in XML. But then, if one of the values is not present in the results, the colors are not displaying properly. I want something like this(if the value is A, the color should be green, if it is B color should be Red, and so on).
Is there any way to achieve the dashboard graph legend color, based on the value without dummy values?
Because, If the user clicks on view the records that had contributed, dummy values will be shown right?