Conditional formatting in Dynamics CRM Leaderboard Charts

When you plan a data visualisation, the primary defining factor is always “what relationship am I trying to show?”, or to put it another way, “what question should this chart answer?”. For example, if you are displaying a chart of recent sales you might break this down by month or by salesperson, depending on whether you are trying to show how sales are changing over time, or how the members of the sales team compare to one another.

If you are comparing sales performance then you might decide to sort according to the sales revenue to produce a ranked chart or “leaderboard” showing best to worst sales performance (or maybe just top ten, for example). It might look something like this:

Me me me me me, it’s all about me

This sort of display tends to lead to another question almost immediately in the minds of the sales people who see this chart – “where am I in the rankings?” What you really want to be able to do is produce a chart that displays the same data for everyone, but highlights the most relevant data, usually the data belonging to the current user. For this, you need to customise your chart to do conditional formatting, to get something that looks more like this:

The problem is, there is no such thing as conditional formatting for Dynamics CRM charts. So what we need to do is to out-think the system and configure a chart that gives the result we want, using magic.

“Any sufficiently advanced technology is indistinguishable from magic” – Arthur C. Clarke

Remember – there is no spoon, it is your mind that bends

I have been doing some pretty strange things with data visualisation for more years than I care to remember. Before getting involved with CRM I did a lot of advanced Excel stuff for clients, and the same sort of approaches I would have used there still stand me in good stead. In general, if you want to display something in more than one way, such as in different colours, or selectively displaying data values, or highlighting high and low points, the way to go about it is to cheat. I recently wrote an article about where I learned to cheat at charts from some great sources.

Usually this involves splitting the data into parts, to display these as multiple series, each formatted as you want, but then put back together. The laws of Gestalt psychology tell us that our brains are wired in such a way that we will usually see things very strongly as belonging together. So while we might change the colour of some data points, we will still see them as part of an overall single continuum, even though the inner workings of the chart structure are actually using two or more series.

Building our sales leaderboard chart with conditional formatting

Let’s start out the easy way, building as much as possible using the UI before exporting the XML and editing it. So create a chart showing sales opportunities by owner, with a sum of the Actual Revenue. Change the chart style to a stacked bar chart. A bar chart will make the labels easier to read compared to using a column layout.

Add a “Top X” rule – this will sort the bars according to their values to produce a ranked leaderboard, and will filter so that you are showing only the best performing sales people. The top 10 or 20 might be good; or you can use any number up to the maximum of 100. If you choose a number higher than the number of salespeople you need to include then you will get the ranking without any filtering effect.

Add the Actual Revenue field a second time and make sure that this one is a stacked bar too. Use a suitable view for the chart preview, such as Completed Opportunities this Fiscal Year while you are doing this, to make sure it looks right, then save your chart. You should get something that has the revenue doubled up, like this:

Unfortunately, despite telling CRM to use stacked charts, it still shows these side by side, and plots the second series on a secondary axis instead, which you will need to remove. However, by choosing stacked charts at this stage this saves having to change the chart type later, and stacked charts by default do not show values on the data points, so this helps reduce the chart junk already.

Export the chart and open the XML file with a suitable editor, such as Notepad++.

Sort out the aliases

The first thing I always do at this point is to change the auto-generated aliases into something more human readable. In this case I have three aliases to replace in multiple places as highlighted below:

<datadefinition>
<fetchcollection>
<fetch mapping="logical" aggregate="true" count="5">
<entity name="opportunity">
<order alias="_CRMAutoGen_aggregate_column_Num_0" descending="true" />
<attribute groupby="true" alias="_CRMAutoGen_groupby_column_Num_0" name="ownerid" />
<attribute alias="_CRMAutoGen_aggregate_column_Num_0" name="actualvalue" aggregate="sum" />
<attribute alias="_CRMAutoGen_aggregate_column_Num_14" name="actualvalue" aggregate="sum" />
</entity>
</fetch>
</fetchcollection>
<categorycollection>
  <category alias="_CRMAutoGen_groupby_column_Num_0">
    <measurecollection><measure alias="_CRMAutoGen_aggregate_column_Num_0" /></measurecollection>
    <measurecollection><measure alias="_CRMAutoGen_aggregate_column_Num_14" /></measurecollection>
  </category>
</categorycollection>
</datadefinition>

The first one is used to aggregate Actual Revenue for all records, which is also used to sort the bars. The second is the category – the Owner of the Opportunity in this case. The third will be for the first series which will later be filtered, so for now I will use the alias ActualMine for this one. I have also rearranged the lines in the fetch collection – I sometimes find it easier to make sense of the XML quickly if I keep things in the same order, so I have the category (groupby) attribute(s) first, then the series measures in order. I also prefer to specify an attribute and alias before using it, such as for the order property here, even though these will work if they are the other way round.

<datadefinition>
  <fetchcollection>
    <fetch mapping="logical" aggregate="true" count="5">
      <entity name="opportunity">
        <attribute groupby="true" alias="Owner" name="ownerid" />
        <attribute alias="ActualSales" name="actualvalue" aggregate="sum" />
        <order alias="ActualSales" descending="true" />
        <attribute alias="ActualMine" name="actualvalue" aggregate="sum" />
      </entity>
    </fetch>
  </fetchcollection>
  <categorycollection>
    <category alias="Owner">
      <measurecollection><measure alias="ActualMine" /></measurecollection>
      <measurecollection><measure alias="ActualSales" /></measurecollection>
    </category>
  </categorycollection>
</datadefinition>
</datadescription>

Get rid of the secondary Y axis

Next you should delete the references to this second Y axis from the chart XML. Remember, that the X axis is always the category axis, and the Y axis is always the values axis, regardless of the orientation (bar or column). There are two places you need to get rid of this: in the second series definition you need to delete the reference to the YAxisType

<Series ... YAxisType="Secondary">

and then also delete the secondary axis definition, that is everything from

<AxisY2 ...>
  [through to]
</AxisY2>

Choose better colours

I then changed the PaletteCustomColours to two shades of the same hue. Since both series represent actual sales revenue, it is generally best for them to be in the same colour, whereas if this were a chart comparing sales forecast to actual, then different colours might be a better choice. In this case I chose a dark and light green, mainly so we can see the clear distinction from the original colours. In reality, I would tend to stick to a specific palette for a project, for example where sales are green, customer service is blue, marketing is purple, or whatever makes sense. Keeping to a theme like this so that a given colour always has a similar meaning can really help users to understand their data more intuitively.

<Chart Palette="None" PaletteCustomColors="45, 126, 45; 102, 204, 102;">

You need to make sure you get the colours in the right order relative to the series. The alternative would be to specify the colour directly in each series instead.

Add custom legend text

I have also added a LegendText property to each series at this stage, so I can easily see which series is which. This will replace the default “Sum (Actual Revenue) (£)”. I also removed the from both series.

<Series...LegendText="Mine">
<Series...LegendText="Not Mine">

By now, having made quite a few changes already, we should reimport the chart to take a look. You should now have something that looks like this:

Configure the series filtering

So far we have still ended up plotting the same values twice over. Now we need to split those so that one series is plotted only for the current user, and the other is drawn for everyone who is not the current user. Because the charts are stacked, we will have one value plotted with a zero value invisible on top of it, and then a whole series with one gap, plotted on top of a load of zeroes. The effect will look like one complete series, with a single bar being highlighted by using a darker colour.

The general approach here is to use a link-entity, just as you would to get an attribute from a parent entity record, but in this case link to the original entity, get the attribute you need and filter the records. Then do a second link to the original entity, get the necessary attribute, and apply a different, mutually exclusive, filter. For our leaderboard, we need to get the Actual Revenue value in both cases, whereas for other charts you might need different attributes – such as Estimated Revenue for Open Opportunities, and Actual Revenue for closed ones. Set up the two link-entity clauses, one of which will re-define the previous ActualMine alias, the second will define a new alias ActualNotMine. We still need the original ActualSales alias to use for the sorting to keep the ranked chart in the right order. So the data definition now looks like this, with the changed alias rows highlighted:

<datadefinition>
<fetchcollection>
<fetch mapping="logical" aggregate="true" count="5">
<entity name="opportunity">
<attribute groupby="true" alias="Owner" name="ownerid" />
<attribute alias="ActualSales" name="actualvalue" aggregate="sum" />
<order alias="ActualSales" descending="true" />
<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
<attribute name="actualvalue" aggregate="sum" alias="ActualMine" />
<filter type="and">
<condition attribute="ownerid" operator="eq-userid" />
</filter>
</link-entity>
<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
<attribute name="actualvalue" aggregate="sum" alias="ActualNotMine" />
<filter type="and">
<condition attribute="ownerid" operator="ne-userid" />
</filter>
</link-entity>
</entity>
</fetch>
</fetchcollection>
<categorycollection>
  <category alias="Owner">
    <measurecollection><measure alias="ActualMine" /></measurecollection>
    <measurecollection><measure alias="ActualNotMine" /></measurecollection>
  </category>
</categorycollection>
</datadefinition>

Now for each record in the dataset, it is linked to itself, compared against the filters and if it matches, the attribute value is included in the aggregate (sum, count, etc).

Tidying up the axis format

The axis has a lot of annoying digits displayed, which just add to the visual clutter, so it will help to apply a custom number format to reduce this. Getting rid of the decimal places would be a good start, but I chose to display these values as “thousands” to reduce them even further. To help make this clear to the reader, I included a different format for the zero on the axis as “0 K”.

I have also added a title for the axis to describe what is being displayed and reinforce the scale, and configured this to be shown at the far end of the axis where it is less distracting. So our Y axis looks like this:

<AxisY Title="Actual Revenue ('000)" TitleAlignment="Far" ...>
    ...
    <LabelStyle Format="#,;-#,;0 K" .../>
</AxisY>

Reducing wasteful white space

I also find that for a lot of charts, we don’t need so much white space, and we can draw things closer to the edges of the chart area. This makes an even bigger difference if you are using a chart like this one with very few bars in the chart pane, or if you click to enlarge the chart from a dashboard. The white spaces are “margins”, and you can hide these by setting IsMarginVisible to False. Note again that the X axis here is the category axis, displayed vertically because we have a bar chart. Hiding the margins on the X axis will spread out the bars along the category axis. If you also hide the margins on the Y axis, then the longest bar will very nearly reach the edge of the chart area, rather than leaving a significant gap before the end of the axis. The amount of gap on the Y axis varies, and depends on the size of the intervals between axis tick marks and labels, amd as you can see in the screenshots on this page, it can be as little as about 5% and as high as nearly 20% wasted. I left the Y axis alone here, and just modified the X axis.

<AxisX IsMarginVisible="False" ...>

Removing more chart junk on the X axis

There are a couple more things you can do to improve this chart by reducing visual clutter. The X axis has clear categories with labels that fit and align neatly with the bars, so there is really no need for tick marks along this axis. Also, the name of the chart and the names of the categories should make it obvious to anyone what this axis represents – salespeople (owner), so the title is also pretty unnecessary. There is no way to actually remove those elements, but you can make them transparent so they disappear, and you can make the title font smaller to reduce the blank space it takes up. 3 pixels seems to be the smallest font size you can specify here.

<AxisX IsMarginVisible="False" TitleForeColor="Transparent" TitleFont="{0}, 3px" ...>
<MajorTickMark LineColor="Transparent" />

Adding some final touches

We have a pretty good chart now, certainly a step up from the original one or out-of-the-box Sales Leaderboard chart, but there are still some things we can do to make this even better.

Firstly, we don’t really need that legend. To be honest, I only put it there so you could see what was going on for this article. So let’s hide that first. The odd thing is that you can’t hide the legend directly, what you do is configure each series to not be visible in the legend, and CRM very sensibly hides the legend completely if you do this for all series, since there is nothing left to show.

<Series ChartType="StackedBar" IsVisibleInLegend="False" ...>
<Series ChartType="StackedBar" IsVisibleInLegend="False" ...>

I also wanted to emphasise the idea of rising to the top of the leaderboard, by putting the best performer at the top, not the bottom. As the CRM Chart Guy points out, if you reverse the sort order, this also means you will get a bottom 10 (or 50, or whatever) instead of top 10. So rather than reverse the sort, you need to configure the X axis to be reversed.

<AxisX IsReversed="True" ...>

This also moves the Y axis to the top of the chart. If you prefer the axis to remain at the bottom, simply add back in those references to YAxisType=”Secondary” in both series, and copy and paste the Y axis section, add in a 2 to make it … and republish. Personally I like it there in this case, so I left it alone.

I have also added a value label to the ActualMine series by adding IsValueShownAsLabel=”True” and made the font for this white and fairly large.

The number format for this data label is similar to the Y axis, but you will need to make a couple of small adjustments. Firstly, you need to suppress the zero format, otherwise all those “invisible” zero bars will show up these labels. Unlike Excel, you can’t just leave it blank after a final semicolon, you actually need to add a space to be displayed. Of course, you could use various different formats, to show more precision for example, since it will be just for the current user, so not very cluttered.

The position of the label is controlled by adding BarLabelStyle to the comma-separated  CustomProperties I found I needed to add a couple of spaces in front of the number format to push the label into the bar a little so it did not run up against the axis.

I’ve removed the legend by adding IsVisibleInLegend=”False” to both series, but I have left in the LegendText property. It has no effect on the chart but makes it easier for anyone else to figure out which series is which for any later troubleshooting, and means that if I enable the legend again later, it already has a useful label. I have increased the PointWidth property to 0.8 from 0.75, to close the gaps between bars slightly, and increased the MaxPixelPointWidth to 100. You won’t see any change from this second property except when viewing the chart in the chart pane or with plenty of vertical space on a dashboard.

With all these changes, each series should look something like this:

<Series ChartType="StackedBar" IsVisibleInLegend="False" LegendText="Mine"
IsValueShownAsLabel="True" LabelFormat="  # K,; -# K,; " Font="{0}, 24px" LabelForeColor="255, 255, 255"
CustomProperties="BarLabelStyle=Left, PointWidth=0.8, MaxPixelPointWidth=100"></Series>

So this is what my final version looks like, with the original single series chart shown below for comparison.

The xml for my final version is below, for reference.

<visualization>
<visualizationid>{817D8B1D-B7E9-E411-80FB-FC15B4263E1C}</visualizationid>
<name>Sales Leaderboard Final</name>
<primaryentitytypecode>opportunity</primaryentitytypecode>
<datadescription>
<datadefinition>
<fetchcollection>
<fetch mapping="logical" aggregate="true" count="5">
<entity name="opportunity">
<attribute groupby="true" alias="Owner" name="ownerid" />
<attribute alias="ActualSales" name="actualvalue" aggregate="sum" />
<order alias="ActualSales" descending="true" />
<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
<attribute name="actualvalue" aggregate="sum" alias="ActualMine" />
<filter type="and">
<condition attribute="ownerid" operator="eq-userid" />
</filter>
</link-entity>
<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
<attribute name="actualvalue" aggregate="sum" alias="ActualNotMine" />
<filter type="and">
<condition attribute="ownerid" operator="ne-userid" />
</filter>
</link-entity>
</entity>
</fetch>
</fetchcollection>
<categorycollection>
<category alias="Owner">
<measurecollection><measure alias="ActualMine" /></measurecollection>
<measurecollection><measure alias="ActualNotMine" /></measurecollection>
</category>
</categorycollection>
</datadefinition>
</datadescription>
<presentationdescription>
<Chart Palette="None" PaletteCustomColors="45, 126, 45; 102, 204, 102;">
<Series>
<Series ChartType="StackedBar" IsVisibleInLegend="False" LegendText="Mine" IsValueShownAsLabel="True" LabelFormat="  # K,; -# K,; " Font="{0}, 24px" LabelForeColor="255, 255, 255" CustomProperties="BarLabelStyle=Left, PointWidth=0.8, MaxPixelPointWidth=100"></Series>
<Series ChartType="StackedBar" IsVisibleInLegend="False" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.8, MaxPixelPointWidth=100" LegendText="Not Mine"></Series>
</Series>
<ChartAreas>
<ChartArea BorderColor="White" BorderDashStyle="Solid">
<AxisY Title="Actual Revenue ('000)" TitleAlignment="Far" LabelAutoFitMinFontSize="10" TitleForeColor="59, 59, 59" TitleFont="{0}, 12px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">
<MajorGrid LineColor="239, 242, 246" />
<MajorTickMark LineColor="165, 172, 181" />
<LabelStyle Format="#,;-#,;0 K" Font="{0}, 10.5px" ForeColor="59, 59, 59" />
</AxisY>
<AxisX IsMarginVisible="False" IsReversed="True" LabelAutoFitMinFontSize="8" TitleForeColor="Transparent" TitleFont="{0}, 3px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">
<MajorTickMark LineColor="Transparent" />
<MajorGrid LineColor="Transparent" />
<LabelStyle Font="{0}, 10.5px" ForeColor="59, 59, 59" />
</AxisX>
</ChartArea>
</ChartAreas>
<Titles>
<Title Alignment="TopLeft" DockingOffset="-3" Font="{0}, 13px" ForeColor="59, 59, 59"></Title>
</Titles>
<Legends>
<Legend Alignment="Center" LegendStyle="Table" Docking="right" IsEquallySpacedItems="True" Font="{0}, 11px" ShadowColor="0, 0, 0, 0" ForeColor="59, 59, 59" />
</Legends>
</Chart>
</presentationdescription>
<isdefault>false</isdefault>
</visualization>