Archive for February, 2013

Specify Exact Colors for Each Priority in the Microsoft Dynamics CRM 2011 Case Chart

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.

Orginal Chart Cases by Priority (Per Day)

Orginal Chart Cases by Priority (Per Day) in CRM 2011

However, a more pressing issue is that the 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.


Case Chart CRM 2011 only showing High records, all blueCase Chart CRM 2011 only showing Low records, all blue

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

Order Chart xml code High on top

New line with the Order clause is highlighted

Let’s import the chart xml back into CRM 2011 and see what it looks like.

Case Chart Ordered by Priority - High on Top, CRM 2011

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


PaletteCustomColors - Original in the crm chart xml

Original custom color order for the case chart

With new colors

PaletteCustomColors after color changes, CRM chart xml

New color codes added to the first three 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.

Priority with the right colors

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.

View incl the date with dummy dates

I’ll use 1/1/1950 for the example as it is way outside our possible date range for the cases.

Here’s the CRM chart now, including the dummy records.

chart with dummy records

Now we need to modify the CRM Chart xml so the dummy records do not display in the chart.

fetchcollection before link-entity

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.

fetch collection with link entity

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 CRM chart xml and have a look.

chart without dummy records but with 1950

Excellent. The Jan 1 1950 day is now empty, even though we know there are three records in the dataset. 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.

chart no Normal but still ordered and colored correctly w 1950

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, a commenter had 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.

Axis modifications

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

Final Case chart with Colors and Orders fixed

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 follow my blog or on Twitter. 

, , , , ,


Add insight to your pipeline with Advanced Goal Charts

The goal charts in CRM are already quite complex and hold a lot of information. The goal charts are combination of the Column and Point charts and display 4 different series (or Legend Entries). So why would I possibly want to make that even more complex. Well, some selling scenarios do require a more nuanced look at your pipeline.

First lets have a look at the built-in “Goal Progress (Money)” chart.

  • Target – the blue bull’s eye shows what we are aiming for
  • Today’s Target – the triangle is a calculated measure of where we should be today
  • In-Progress is the current value of our pipeline
  • Actual is the value of Won Opportunities

Once you have gotten used to seeing that many legends on one chart, this chart is actually very good and easy to read. I scan it and see if the green bar meets the triangle and if the grey bar is higher than the blue dot.

In business terms that would sound more like “Is my actual revenue for this sales period on track and do I have enough Opportunities of high enough value to meet my sales goal for the period”.

Where this chart is lacking information, is that the In-Progress series only show you the combined value of your pipeline. You actually have no idea if these are new Opportunities early in their development phase, or if contracts are almost ready to be signed. Maybe that’s why its the color grey.

“Weighted Revenue” (Est. Revenue * Probability of Winning%) is often used to calculate how much you reasonably can espect to close. Whether or not your probability is based on a defined gated sales process, or an estimate entered by the User, is really up to what works best for your business. For this specific scenario I prefer the gated sales process as it not only tells you the likelihood of winning, but also more importantly, gives you an indication of how far in the sales process you actually are.

You can read more about sales processes and weighted revenue on Richard Knudson’s very fine I won’t go into much detail here on exactly how to add Weighted Revenue to an Opportunity using Processes.

Back to the chart. It would be fairly easy to just change the goal so it rolls up weighted revenue instead of the Est. Revenue. However, that would only give us a view of how much our weighted revenue is. That might be equally as confusing as just having the whole pipeline. We also want to add the rest of the pipeline so we know what our full pipeline value is.

Enter “Floating Revenue”

When calculating the Weighted Revenue, as a part of the process, make sure you also have a field called Floating Revenue where you insert the remaining amount (Est. Revenue – Weighted Revenue = Floating Revenue). In the Process you would add Est. Revenue to your new field and in the next step decrement it by the value in Weighted Revenue.

We also need to add the Floating Revenue as a Roll Up field for the Goal. Luckily, up to three Roll-Up fields on one Goal Metric is supported. I’d suggest using a new Goal Metric rather than to edit the existing one.

For our Goal Metric for the Advanced Goal Chart we have:

  • inprogressmoney – Weighted Revenue (originally for Est. Revenue)
  • actualmoney – Actual Revenue (same as the orginal Goal Metric)
  • customrollupfieldmoney – Floating Revenue (the new roll-up field)

Now that we have the data being rolled into the Goal, we need to modify the chart to display the extra data. Export the chart “Goal Progress (Money)” and use it as a base.

Notice the actualmoney and inprogressmoney fields. We will need to add the Floating Revenue to the list of attributes as well as for the measurecollection and the Series. We will get into that later. Note that the order here is relevant as we want to keep the stacked column chart with Actual Revenue on the bottom, then Weighted Revenue and finally topping up with the Floating Revenue.

Here are the additions to the xml file.

In the fetch collection I’ve added Floating Revenue – schema name on the Goal entity is customrollupfieldmoney.

The new field also needs to be added in the measurecollection

Finally we can add the extra Series. Since the Floating Revenue is fairly similar to Weighted Revenue from a charting perspective, we can just copy that series and make modifications to it.

In addition to copying the series I modified the color property for the both the Weighted and Floating Revenue. I gave Weighted Rev. the DarkGray color, and Floating Rev. the LightGray color. Using the color names make it easier to not get them mixed up, rather than modifying the RGB values.

Let’s import it back into CRM and see what happens. And remember to use the Advanced Goal Chart with the Advanced Goal Metric.

So far, so good. The data is now being displayed as intended with the full pipeline broken into two, giving us an idea of where we are in the sales process for those Opportunities. However, there are still some items we can clean up before it’s really good.

First let’s fix the legend texts to something better than “Custom Rollup Field (Money)” etc.

Adding the “LegendText=”XYZ” will allow you to modify the legends to something more sensible and utilize space better. In this case there’s really no reason to add the “(Money)” to the end of every description.

It’s getting better, but we can stil do some improvements.

Let’s remove the “Name”. We do not need to display that the title of the Series is “Name”. In this case referring to the Goal Name. We can’t actually remove it, but we can make it transparent by changing the color.

The Y-axis can be improved a little by writing $100k instead of 100,000. That’s a lot of zeros to repeat on this chart.

We do that by adding “Format” property and setting it to $#,#,k.

I would like to do the same for my Actual Revenue by adding the figure to the chart.

I’ve changed the IsValueShowAsLabel to “True” and added the same format to the label.

NOTE: Here on the Series the property is called “LabelFormat”. On the Axis it is just “Format”.

Let’s have a look at how we are doing.

Starting to look like the finished product. Now this chart has one drawback that I want to fix. While I can easily hover my mouse over the grey parts of the stacked column to get the value, I still only get the value of either the Weighted or Floating revenue. The chart will not give me the full value of the pipeline. I can’t just look at the full height of the column either, as that includes the Won revenue. I would need to do some extra math here in order to get the full pipeline value. That is something I’d expect the chart to do for me.

To get the full pipeline value on the chart we need to add it as a field to the Goal Record. We can’t just add an extra rollup field as only 3 are allowed. However, all we need is a process that aggregates the Weighted and Floating Revenue to a new money field every time the Goal Record is recalculated.

I’ll add the new Series to the xml file in the same manner I added Floating Revenue to the fetch collection, measurecollection and Series.

For the Series I copied the “Target” series.

I added the IsValueShownAsLabel property, set the LabelFormat and increased the font to 12px. I also changed all the marker colors to “Transparent” so we do not see a Marker on the chart. Just the full value of the pipeline. Note that I added the IsVisibleInLegend=”False” as I do not want it to show up on the legend. I also removed the name of the series that I copied from.

Now, let’s have one more look at the chart.

Now we have a Goal Chart displaying the Won revenue and the pipeline divided into how much its weighted displayed against our goal and how far we should be today.

When reaching towards the end of a goal period, this chart makes it easy for Sales Managers to see how close their sales people are to reaching their goals. If your pipeline is mostly LightGrey, it means you are probably working with mostly new Opportunities making it unlikely that you will reach your goal. On the other hand, if the pipeline is mostly DarkGrey, you are far in your sales process and all the more likely to close within the goal’s period.

Note: The term “Floating Revenue” is something I invented for this chart. Not sure if there’s an official term for it, but “Floating Revenue” seems to make sense and works well opposite the term “Weighted Revenue”. “Floating Revenue”, you read it here first 🙂

Now that we have gotten this far, why not try and over-engineer this thing a little.

Since Weighted and Floating Revenue isn’t an exact science, we can add some approximation to how the chart is viewed. To do this, we will change the grading in the pipeline so it changes according how much of the revenue is weighted, without giving the exact amount.

Gradient colors xml

Click to enlarge

We wil add a TopBottom grading on the colors and make sure the gradients meet each other, where the Floating Revenue turns into Weighted Revenue. In the example above I’m using the grey color (RGB 100,100,100) but I’m using it in the ARGB format so I can make the gradient go from very transparent to almost solid.

ARGB goes from 0, completely transparent, to 255 completely solid. More on that in this blog post.

For Floating Revenue I’ll start the transparency 20 and go to 120. For Weighted Revenue I’ll continue from 120 and go to 220.

In the example below, it should be clear which of the two goals is further in their sales process, and thus more likely to reach their target. The gradient also makes it clearer that the 475k is the value of the full pipeline.


The colors can be modified to make the transition more or less apparent depending on how much emphasis you want to give it. You could also make a 10 or 20 point difference in the color grading so you have subtle indicator as to exactly where Weighted and Floating Revenue meet. I could also remove either “Floating” or “Weighted” in the Legend and just have the In-Progress value.


, , , , ,