Highlighting contribution using conditional formatting in CRM charts

Following on from my previous about conditional formatting in Dynamics CRM leaderboard charts I thought I would take a look at another use for this approach, this time in relation to customer service rather than sales.

You will often be asked to produce charts to help managers see the amount of work being done by a department, such as the number of Cases being resolved per month. While this is useful in itself, an individual user might like to see how they have contributed to the overall effort, using a chart such as this:

Resolved Cases with contribution highlighted

Steps to build this chart

Create the basic chart using the UI tools

Start off by creating a chart of Cases, and add a series to count them. In order to count the records you still have to choose a field. I always recommend to choose the field that has the same name as the entity. This is the internal primary key field that has the record’s GUID, so it is absolutely guaranteed to be filled in every time. I know there are other system fields such as Created On or Modified By that are also reliably populated, but I find it is much simpler for someone to interpret your intentions if you do Case > Count:All than Modified By > Count:All. In the second example I am distracted by the choice of field and start to wonder what the user who modified the record has to do with the way the chart works.

So, select the Case field, and select Count:Non-empty for the aggregation. We will come back to this point later. Add a second series exactly the same.Make sure both of these series are set as stacked column charts.

Add a category – use Modified On, since we don’t really have a “resolved on” date, but we know that Cases cannot be updated after they are resolved, so Modified On can be used as a substitute, or “proxy” for this. Select Month as the date grouping level. So your chart creation dialog box should look like this:
Create chart for resolved Cases by month

Save the chart and export the XML, so we can get to work on filtering the series to produce our conditional formatting effect.

Filtering the records

We filter the series just like we did in the previous conditional formatting chart example, by adding link-entity sections to the chart XML that refer back to the same entity. Note I have also changed the aliases used to something I can easily read and understand. The code below highlights the first link-entity clause and corresponding measure alias..

<fetch mapping="logical" aggregate="true">
<entity name="incident">
 <attribute groupby="true" alias="MonthResolved" dategrouping="month" name="modifiedon" />
 <link-entity name="incident" from="incidentid" to="incidentid" link-type="outer">
  <attribute alias="CountMine" name="incidentid" aggregate="countcolumn" />
  <filter type="and">
    <condition attribute="ownerid" operator="eq-userid" />
 <link-entity name="incident" from="incidentid" to="incidentid" link-type="outer">
  <attribute alias="CountNotMine" name="incidentid" aggregate="countcolumn" />
  <filter type="and">
    <condition attribute="ownerid" operator="ne-userid" />
 <category alias="MonthResolved">
    <measure alias="CountMine" />
    <measure alias="CountNotMine" />

Notice the use of the special FetchXML operators for the ownerid of eq-userid and ne-userid, as well as how the aggregate type shows up as countcolumn rather than just count. This is essential, because otherwise both series will actually count all the records and ignore the filters, instead of only counting the rows that have a value in that column because they match the filters. (This weird behaviour is a borderline bug, but at least the workaround for it is easy).

Change the overall layout

We also have the same problem as before, in that we have the two series being drawn against two separate Y axes. Since each axis will automatically adjust its own scale, this won’t work, and also means the two columns will not be stacked on top of each other, since they are on independent axes.

However, for time-based charts like this one, I find that people are often most interested in the most recent data, so if they are going to try and read any value off the chart, it will be from the last column, at the right hand side. So, rather than rushing to remove the second Y axis like we did last time, this time we will leave that alone, and instead configure the first series to also be plotted on the second axis by adding YAxisType=”Secondary”. Let’s also change the palette colours and hide both series in the legend at the same time (so the legend disappears) using IsVisibleInLegend=”False”:

<Chart Palette="None" PaletteCustomColors="210,96,18; 241,151,90;">
 <Series ChartType="StackedColumn" YAxisType="Secondary" IsVisibleInLegend="False"
   Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"  >
 <Series ChartType="StackedColumn" YAxisType="Secondary" IsVisibleInLegend="False"
   Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"  >

Reduce the clutter on the axes

Now we can tidy up the axes a little bit too, to reduce the visual distractions. Some things you should always consider here:

  • Do you need tick marks on the category axis?
  • Can you make the axis line colour lighter?
  • Will it help to remove the margin on one or other axis, to use more of the space?
  • Do you need titles on either axis, and if so, should you add a custom title rather than the default one?

When it comes to this last point, a good option can be to use a tooltip rather than a title. Labelling a chart clearly is obviously important so that people can be sure of what they are looking at. But once someone has been using the same chart every day for weeks, a title such as “Count of Cases” can be an unnecessary distraction, especially if it is clearly implied by the chart’s name anyway. A tooltip can be a good way to get the right balance – add a description for those who need it, avoid any ambiguity about scaling and generally make sure people know how to interpret the chart.

I have chosen to make the following changes, highlighted in the code below:

  • remove the margins on the horizontal axis to make the chart fill more of the space across the screen
  • remove the titles on both axes (by making them transparent and 3 pixels in size
  • remove the X axis tick marks
  • added a tooltip to the Y axis (Note: ToolTip with two capital Ts – properties must be entered in the correct case)
  • set a fixed interval for the Y axis so there is a label for every 100 Cases – there were too many numbers being shown by default, and they were entirely unnecessary.
  • made the tick marks on the Y axis a little lighter in colour
<AxisX IsMarginVisible="False" LabelAutoFitMinFontSize="8"
TitleForeColor="Transparent" TitleFont="{0}, 3px"
LineColor="165, 172, 181" IntervalAutoMode="VariableCount">
<MajorGrid LineColor="Transparent" />
<MajorTickMark LineColor="Transparent" />
<LabelStyle Font="{0}, 12px" ForeColor="59, 59, 59" />
<AxisY2 LabelAutoFitMinFontSize="8"
TitleForeColor="Transparent" TitleFont="{0}, 3px"
ToolTip="Count of resolved Cases, highlighting your own contribution"
LineColor="165, 172, 181" Interval="100" IntervalAutoMode="VariableCount">
<MajorGrid LineColor="239, 242, 246" />
<MajorTickMark LineColor="204,204,204" />
<LabelStyle Font="{0}, 10.5px" ForeColor="59, 59, 59" />

Further thoughts

There are of course lots of different ways you could present this chart. If the timeframe was very long, it might make sense to use a line chart to show the filtered series for “my” Cases, and a second line for an unfiltered series of all Cases.

If you want to emphasise “my” performance and only show the whole team / business for comparison, you could use a column for mine (much bolder and highly visible) and a line or point chart for the overall team results.

If you want to emphasise the level of contribution, you could use a 100% stacked chart. This means that in months with more or fewer Cases being logged and resolved, the viewer remains focussed on the proportion of those that they have dealt with, rather than the actual number. To change this simply requires a change of chart type to StackedColumn100, and some thought about the interval for the Y axis, a custom number format, and a different tooltip to explain to the user what they are looking at. An example of this same chart as a 100% stack is shown below. It is obvious in both versions of the chart that this user contributed less in March (perhaps because of an absence). In December their contribution appears to be lower than usual in the normal stacked chart, but we can see from this one that as a proportion it actually remained pretty level, in a month when there was simply less work to be done (perhaps because everyone is off at the same time during the holidays).

Resolved Cases with contribution 100% stacked

In a follow up post I will look at some other options and settings used in this chart to manage the width of the columns, and demonstrate how you could display the Y axis on both the left and right hand sides without them being scaled differently.