“Mind the gap” to make your CRM charts look better

Supercar

In the supercar and racing industries, the key question for all major design decisions is “does it make the car go faster?” This can be interpreted fairly loosely, for example better brakes or gripper tyres make it possible to drive the car faster without compromising safety. And beyond that, there is still room for creativity and the aesthetics of beautiful design, but in the end, the question still reveals a single-minded focus that we can learn from in other disciplines too.

When designing charts, I aim for functionality first and foremost. I want the end result help people to understand their data better, see patterns and trends, identify outliers and gain insights that support better decisions. But as with the design of a desirable sports car, we can still spend some time on making data visualisations pleasing to look at, and make the best use of the space we have available on a range of devices from small tablets to widescreen monitors.

Charts shrink and stretch depending on the space available

When you view a dashboard, charts are always the same height, regardless of the size of the window, whereas the width will reduce proportionally as the window width decreases. Using CRM 2013 or 2015, dashboards use a responsive design, so the layout will change and components will be pushed down so they don’t just become ridiculously narrow. The only time the height of a chart will be reduced significantly is when you use a view combined with the chart pane above it, with CRM in a window that is already not very high.

For the purposes of this article, I am going to focus on column charts, because they will more often be displayed at a variable width. This means that your design should take account of what they will look like in very narrow windows, but also how they might appear when stretched very wide or viewed on their own. Everything discussed applies in exactly the same way to row charts, but is less likely to be an issue in reality.

Manage column widths versus white space

There are two key properties for a series in a column chart that manage the column widths and white space: PointWidth and MaxPixelPointWidth. These are both contained within the CustomProperties, which is a comma-separated set of properties, as seen in this XML snippet from a simple chart built in CRM and exported:

<Series ChartType="Column" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"...>

These same properties can be used with Column, StackedColumn, StackedColumn100, and the equivalent row chart types.

PointWidth

if you have ever played around with changing these properties, you may have found that they don’t always seem to do very much, so let’s figure out what they are each supposed to do and how they relate to one another.

Let’s take a look at PointWidth first. This is a decimal value from 0 to 1, and simply represents how much of the chart is taken up by the columns, relative to the white space. A setting of 0.75 means that the columns should take up 75% of the space, So when your charts are relatively narrow, the columns and gaps are both resized so that if you measure from the left side of one category column to the left side of the next category, the columns take up 75% of that width.To put it another way, taking the inverse of the PointWidth, the gaps will take up 25% of the available space. Whether you have a single column, or multiple clustered columns in each category, the gaps will be 25% of the chart width, and the columns together will take up 75%.

The example below shows two charts of the same two series of data, one using stacked columns, one using normal (clustered) columns. In both cases the PointWidth is set to 0.5 for both series. You can see the gaps are 50% of the available space in both charts, so the PointWidth is the value for all series together, not each one.

Two charts with PointWidth 0.5 cropped

Personally, I prefer to make the columns a bit wider than the OOB setting; I find around 0.85 works well for single or stacked columns, and 0.8 for multi-column charts. You should be wary of using a value of 1 for PointWidth

If there is more space available, charts expand to use it, so in reality if your chart has plenty of room, the columns will be spread out and the gaps between them will be considerably more than the 25% you might expect. So you should take the PointWidth, figure out the inverse of it, and think of this as a the minimum space for the gaps, remembering that it can be larger.

Comparing PointWidth to Excel’s Gap Width

Excel handles the relative widths of columns and gaps quite differently. For a series in an Excel chart you can specify the gap width from 0 to 500%. This is a measure of the width of the gaps relative to the width of a column. By default this is set to 150%. If you plot only a single column or stacked column, this is definitely excessive in my view. However, if you have two or more “clustered” columns, higher values for this setting start to make a bit more sense because it measures the size of the gap relative to one of the columns, not the group. So for a single column a setting of 20-30% could be fine, but if you have four clustered series for example then 70-100% might be more suitable, as this is equivalent to 15-20% gaps overall.

In the example below, a gap width of 100% means that the gap is the same width as one of the clustered columns.

Excel gap width 100 percent highlighted

Also notice that Excel always scales both the columns and the gaps to retain the selected proportions all the time, regardless of the size of the chart. So in Excel the gap width is always used, rather than being a minimum as it is in CRM.

MaxPixelPointWidth

This setting is in contrast to PointWidth, and is used to manage the opposite problem – when a chart is stretched out sideways, you might not want to have columns that are are too wide. As a general rule, if the column widths exceed the heights, the sheer amount of “ink” visible can make it much harder to focus on subtle differences in height. Conversely, if the gaps between columns are too wide, it can be difficult to compare column heights to one another accurately.

If you have a column chart with only a few categories that is likely to be displayed fairly wide, such as half the width of a dashboard on a widescreen monitor, you should test this with some different values for this property. I find that the default 40 pixels is a bit narrow most of the time, so I generally go for about 80 to 100 pixels here. Don’t forget, this does not mean your columns will normally be this wide, this is setting a maximum that you will only ever see if the chart is displayed very wide, or has very few categories. If the columns reach the maximum width, the chart is plotted with increasingly large gaps to make up the space.

As an example, if you are looking at a chart of “My Cases resolved per month”, this might look fine most if the time, combined with a view of Cases resolved in the last 12 months. But someone who just joined the organisation would only have one month showing up, so the columns would be much wider than usual and this maximum width can prevent the chart looking awful. This scenario would of course be avoided if you instead plot all the data for everyone in the business (so there are always records for every month), and highlight the current user’s contribution by filtering the chart to produce a “conditional formatting” effect.

In the final example of this article, exactly the same stacked chart shown above is displayed across the full screen. Notice that the PointWidth of 0.5 is now ignored, and instead the MaxPointWidth of 100 pixels is being used to limit how wide the columns appear (click for full size version).

Stacked chart with PointWidth 0.5 wide display

Now that you know how these two properties control the display of your charts, you can start to use these to make your charts look a bit more polished and professional, and possibly more attractive and “easy on the eye”, no matter what size they are displayed at.

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

<datadefinition>
<fetchcollection>
<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" />
  </filter>
 </link-entity>
 <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" />
  </filter>
 </link-entity>
</entity>
</fetch>
</fetchcollection>
<categorycollection>
 <category alias="MonthResolved">
  <measurecollection>
    <measure alias="CountMine" />
  </measurecollection>
  <measurecollection>
    <measure alias="CountNotMine" />
  </measurecollection>
 </category>
</categorycollection>
</datadefinition>

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

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

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" />
</AxisX>
<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" />
</AxisY2>

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.

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:

Sales ranked by owner - single series stacked bar chart

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:

Sales ranked by owner - reformatted axes

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

ThereIsNoSpoon

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:

Sales ranked by owner - two series stacked bar chart

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:
Sales ranked by owner - two series stacked bar chart single Y axis new colours

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" />

Sales ranked by owner - reformatted axes

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.

Sales ranked by owner - reversed order and added label

Sales-ranked-by-owner-single-series-stacked-bar-chart.png

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>

Special privileges in CRM Security Roles

There are several privileges in Dynamics CRM that control access to things like settings and user personalisation features, rather than data records. If users are missing some of these then they might not be able to sign in to CRM at all, or might not be able to use it properly. In particular, there are six privileges that can only be set at User level

Privileges that can be set at “User” Level only

There are a few privileges that you can only set to User level or None in any security role. Five of the entities for which this is true are on the Core Records tab, and you can easily find out which they are by looking at the System Administrator security role (shown below). Even this “super user” does not have global rights to these items so they stand out as the only rows not covered in green circles:

System Administrator Security Role

(Minor note: the above screenshot was taken from CRM 2015. In CRM 2013 and earlier, you will see that UserEntityInstanceData is written as one word with no spaces, and appears below User Entity UI Settings.)
Find out what these 5 settings are for, and how to configure them »

Modify Dashboard properties in CRM 2015

In CRM 2011 you could create system and personal Dashboards. You could start from scratch using one of the predefined layout templates, or copy an existing one using “Save As”. Choose a name, give it a description and away you go.

If you changed your mind about the purpose of the dashboard you could simply open it back up and change the name of the dashboard, and you could click the “Properties” button on the Ribbon to change the description.

With CRM 2013 there was a big change – although you could change the name of a dashboard, there was no “Properties” button on the command bar to change the description, so if you got it wrong when you first created and saved it – tough, you could not go back and fix it later. And “Dashboard” is not an entity you can add to a Solution to work on the command bar RibbonXML using Ribbon Workbench, for example.

Dashboard 2013 no properties button

Despite reporting this as a suggestion that needed fixing by using the Connect site, nothing happened for the last year. The workaround of making a further copy of the dashboard and setting the correct properties is all very well, but could mean having to change the default dashboards for each module in the SIteMap, and for users to have to set a new default if they were using the existing one.

Anyone using CRM for Tablets could only use the “out of the box” Sales dashboard, but if you wanted to change this you could modify every aspect of it (including the display name) to use it for non-sales purposes but you still could not change the description. Read what is changing in CRM 2015»

Workflow OR conditions in CRM 2013 SP1

Everyone is writing about all the big changes in Dynamics CRM 2013 Service Pack 1 (aka Spring ‘14 Release for CRM Online, previously codenamed “Leo”). There are tons of great features around Service management, new tools for developers, improved tablet support and so on.

If you want to find out all about the new service management functionality, how to enable and take advantage of the SLAs, entitlements, Case routing and related features, sign up for our in-person classroom training course What’s new in Dynamics CRM 2013 Spring ’14. In one day of accelerated training you will learn all about configuring new service features, as well as high-level coverage of other items such as tablet support, server-side synchronisation, status transition controls, and more!

At the CRMUG UK Meeting in July we are also focussing on “Service” and will have plenty of time to hear from Microsoft about the new release, as well as a session from Wael Hamze of Barclays for developers, including some of the new things in the SDK.

In all the kerfuffle there has not been much mention of a little feature that I have been waiting on for a long time, that will make a small but important difference to the development of Workflows. You can now create conditions and group clauses using OR and AND conditions. Read how to use OR conditions in Dynamics CRM 2013 Workflows with SP1 »

Why Use Access Teams in Dynamics CRM 2013

It seems to be “Access Teams Week”.

Larry Lentz wrote an article CRM 2013 Access Teams in a Nutshell which is a great introduction to what Access Teams are and what they do. Ben Hosking wrote a post in his series about preparing for the CRM Customization exam MB2-703: Access Teams and Access Team Templates how to use them and key facts.

Both of these are really good primers on the mechanics of using Access Teams but I think they miss addressing one really big question – why would you want to use Access Teams in the first place?

I think there are several reasons to use Access Team Templates, some in terms of usability, others from a more technical or performance perspective, and some cases for using manually-created Access Teams.

Read 4 key reasons to consider using Access Teams in Dynamics CRM 2013 »

CRM Masters Spring Special Offer

CRM Masters logo small

 

We have a limited time special offer discount on our Dynamics CRM training at CRM Masters.

Book on one of our June or July courses now and get £45 off

Register for any of our scheduled Dynamics CRM training courses before the end of May for the special early bird price of only £350 per person per course.

You can book as many people on as many courses as you want, and get the discount for each person.

In order to give you the best possible training, seats on our custom CRM training courses are strictly limited, so hurry to book your place while they are still available.

This offer applies to four of our courses: Update Your Skills to Microsoft Dynamics CRM 2013, Managing Business Processes with Microsoft Dynamics CRM 2013, Visualizing Microsoft Dynamics CRM Data and What’s New in the Leo Release of Microsoft Dynamics CRM 2013

Training for Dynamics CRM from CRM Masters

CRM Masters logo small

I’m excited to announce that I’ve partnered with Feridun Kadir to set up a new Dynamics CRM training business, CRM Masters Ltd.

We have a schedule of training courses that will give you the practical knowledge that you need to implement, customize and maintain CRM 2011 and 2013.

Our courses are designed to give you highly-focused instructor-led training to cover topics you won’t find on other courses.

Please review our list of Dynamics CRM courses and if there is an area that you feel you would like training in that you don’t see please let us know.

Security Roles and Teams in CRM – An Inconvenient Half-Truth

Over the course of the last two years or so reading everything I can about Dynamics CRM, as well as teaching many classes of people how to get the most out of their CRM systems, one thing which comes up again and again is how to best structure Business Units, Users and Security Roles, and sometimes Teams as well to get the exact model you want to match your business requirements for who has access to which records and when.

Users inherit Security Roles from Teams – right?

One concept I have seen repeated many times is that “Users inherit security roles from all the Teams they are in”. And generally this seems to be a reasonable way to describe how it works, but occasionally odd behaviours seem to show up which make this appear to be less than 100% accurate.

I also had a gut feeling for a while that this was not the best way to describe the way this works. I prefer to say that “when a User is in a Team, they can act as if they are the Team, with the rights that the Team has through its Security Roles, but only while considering records in the same Business Unit as that Team”.

More on this later, and the one part of the model that this description does not do justice to.

Overall this means Security Roles use a kind of “impersonation” when Teams are involved and that the rights the User has are not only ‘borrowed’ very temporarily from the Team but they are relative to where the Team is – so access levels / depths such as “Business Unit” or “Parent / Child Business Unit” operate from the Business Unit where the Team is.

So how does this really work?

If you really want to read how security roles work in terms of determining access to a whole bunch of records (to display the results of a view) or a single record, then you need to read the white paper Scalable Security Modelling with Microsoft Dynamics CRM 2011.

42 pages later you will probably know exactly how the queries are built to actually enforce the security model, but that may not have made it much clearer from a practical, day-to-day design point of view. To be fair, the point of that white paper is to explain the underlying architecture and query methods properly so you can figure out the performance impact of different security approaches, rather than demonstrating how this informs your design from an end-result “who can see what” point of view. One thing that is never mentioned is any idea of inheritance or merging of privileges from Teams to Users. Every kind of access request is checked against User and Team permissions separately (exactly what is checked depends on things like whether the User has Global access level privileges to that entity at all, and whether the record is owned by the User or any of their Teams. These can help shortcut the otherwise brute force querying that would be necessary, especially to return all records in a view).

“You can’t handle the TRUTH!”

By now, I bet some of you are ready to shout at the screen – “we know Users don’t actually inherit the roles and keep them for themselves, but it works just as if they did, so it’s just a kind of shorthand and we all understand what we really mean, so don’t be pedantic”.

Tom Cruise in A Few Good Men - I Want the TRUTH!I always argue that I am not pedantic, I just like things to be exactly correct – “I want the TRUTH!”

In this case, it is CRM which is pedantic, and does not always behave as expected if you believe that a User can act as if they have all the Roles that their Teams have, all of the time. If you are betting your security model on it working this way then either you will end up with Users who can’t do their job, or possibly a gaping hole in your security. Neither sounds good to me.

Read more of this post