Archive for category Week Chart

Custom Date Formats in Charts in MS Dynamics CRM

I get a lot of questions on how to change the date format on the labels. A common one is changing the week grouping from “Week 35 of 2016” to “Week of Aug 29.”

Coming from Europe, I think week numbers are an awesome way of keeping track of the weeks, but like the metric system it just never caught on here in the US, regardless of how much sense it makes.

So even though week numbers are the better option, like column/bar charts over funnel charts, let’s have a look at how we can work with the date formatting on labels.

First up, MS Dynamics CRM does not support changing the date labels on an axis. If you change the xml for the labels, the chart will either just show you the standard date formatting, or the chart will show a render error, even though the xml is correct. For this reason, I’ve previously stated that this requirement cannot be met with charts in Dynamics CRM.

However, unlike the axis, you can actually format the dates on the label for the series. So to address the question on changing the week numbers on the axis label, I will try and remove those labels and use a series label instead to show the date in the desired format.

So the end result will look like this:

MS Dynamics CRM Chart with modified xml to display custom date formatting

Rather than this standard labeling:

The system week grouping in MS Dynamics CRM chart created in the chart editor.

It is not perfect, so if you can live with the drawbacks I mention throughout this post, it will probably work for you.

Build the Base Chart

Let’s create the foundation. In the chart editor, we will need two series, and of course, the week we are grouping by.

Chart Editor in MS Dynamics CRM. Two series and group week to prepare for adding custom date formatting in the chart xml.

We need two series. One to show the actual count of opportunities, and one to hold the label for the custom date formatting.

Export the Chart XML

Open the chart xml in Notepad ++ and get ready to make some changes.

In the CRM chart xml below, I have updated all the auto generated aliases to something more meaningful in this context.

        <fetch mapping="logical" aggregate="true">
          <entity name="opportunity">
            <attribute groupby="true" alias="groupbyweek" name="estimatedclosedate" dategrouping="week" />
            <attribute alias="dateformat" name="estimatedclosedate" aggregate="min" />
            <attribute alias="countofopportunities" name="estimatedclosedate" aggregate="count" />
        <category alias="groupbyweek">
            <measure alias="dateformat" />
            <measure alias="countofopportunities" />

In the xml, my changes and additions are in green.

Besides updating the aliases, I made one significant change in this section. In the attribute for the “dateformat,” I changed the aggregate to “min” for minimum.

Also note that I have the “dateformat” before the “countofopportunities” in the category collection. This way the count, which is the number we want, is drawn last and on top.

Before you import, scroll down on your chart xml and make this VERY IMPORTANT change.

  <AxisY LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">
	<MajorGrid LineColor="239, 242, 246" />
	<MajorTickMark LineColor="165, 172, 181" />
	<LabelStyle Font="{0}, 10.5px" Format="CRM Chart Guy" ForeColor="59, 59, 59" />

In LabelStyle for the first Y axis, add Format=”CRM Chart Guy” as above.

If you do not add this format you will get a rendering error. This seems to be a bug in how Dynamics CRM renders charts, but this is the way to get around it.

Now you can import the chart and make sure everything is still working.

Chart xml modified to display dates instead of values in MS Dynamics CRM

So far, so good. You should now be able to see that one of the bars contains the date, while the other still has the count.

As you have probably figured out by now, the date that we are going to get may not be the Monday of that given week. It will be the earliest date of that week, where data was present. So use with care as it could be misleading if your first date happens to be the Friday of a given week. However, for daily events, it should be fairly accurate, or at least close enough.

Edit the Series

Go back to the chart xml and locate the two series. We need to make the following updates:

  • Remove the legend items – not needed in this case
  • Draw the bars on top of each other instead of side by side
  • Make the date series bar invisible/transparent – we only want the label
  • Give the bar displaying the count a brighter color so it is easier to read text on it
  • Change the date label format
  • Move the date label so it is left aligned on the chart
	<Series ChartType="Bar" IsValueShownAsLabel="True" Color="Transparent" IsVisibleInLegend="False" LabelFormat="'Week of' MMM dd" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="BarLabelStyle=Left, DrawSideBySide=False, PointWidth=0.75, MaxPixelPointWidth=40">
	  <SmartLabelStyle Enabled="True" />
	<Series ChartType="Bar" IsValueShownAsLabel="True" Color="LightBlue" IsVisibleInLegend="False" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40" YAxisType="Secondary">
	  <SmartLabelStyle Enabled="True" />

Here are the additions to the chart xml in the same order as above:

  • IsVisibleInLegend=”False” to both series
  • DrawSideBySide=False to one of the series (this custom property will automatically apply to all the bar series)
  • Color=”Transparent” to the dateformat series
  • Color=”LightBlue” to the countofopportunities series (more on colors in CRM Charts here)
  • LabelFormat=”‘Week of’ MMM dd” on the dateformat series
  • BarLabelStyle=Left to the dateformat series custom properties

Import the xml back in to MS Dynamics CRM

XML for the chart modified custom date format and other cosmetic changes.

Custom Date Format

The LabelFormat=”‘Week of’ MMM dd” is what gave the label the “Week of Aug 29” label, or whichever date was the lowest for that group.

Here are the most common options you can use:

  • “d” = The day of the month, from 1 through 31
  • “dd” = The day of the month, from 01 through 31
  • “ddd” = Abbreviated name of the day; Mon, Tue, Wed etc.
  • “dddd” = Full name of the day
  • “M” = The month, from 1 through 12
  • “MM” = The month, from 01 through 12
  • “MMM” = Abbreviated name of month, i.e. Jan, Feb, Mar etc.
  • “MMMM” = Full name of month
  • “yy” = The year, from 00 to 99
  • “yyyy” = The year in four digits

Pay attention to the case of the letters. “m” is for minutes, while “M” is for months.

The full set of options can be found on MSDN here.

As in this post, you can combine the options like “dd MMM yy” for “31 Aug 16”.

Also, when adding leading text like ‘Week of’, put it in single quotes to avoid some letters being displayed as a value instead.

Chart Area and Axes Modifications

Ok, let’s fix those axes and the labels that just say CRM Chart Guy.

This is actually really simple. I just deactivated them by adding  Enabled=”False” to both AxisY and AxisX.

<ChartArea BorderColor="165, 172, 181" BorderDashStyle="Solid" BackColor="10, 59, 59, 59">
  <AxisY  Enabled="False" LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">
	<MajorGrid LineColor="239, 242, 246" />
	<MajorTickMark LineColor="165, 172, 181" />
	<LabelStyle Font="{0}, 10.5px" Format="CRM Chart Guy" ForeColor="59, 59, 59" />
  <AxisX  Enabled="False" LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">
	<MajorTickMark LineColor="165, 172, 181" />
	<MajorGrid LineColor="Transparent" />
	<LabelStyle Font="{0}, 10.5px" ForeColor="59, 59, 59" />
  <AxisY2 LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">
	<MajorGrid LineColor="239, 242, 246" />
	<MajorTickMark LineColor="165, 172, 181" />
	<LabelStyle Font="{0}, 10.5px" ForeColor="59, 59, 59" />

Fun fact: I think this is my first post where I left the secondary Y axis alone 🙂

Now, I did a little more than that. Right before the AxisY, I made a few modifications to the ChartArea.

  • Changed BorderColor from “White” to “165,172, 181” (I just copied the gray color from the major tick mark)
  • Added BackColor=”10, 59, 59, 59″ to give the background a little more substance. (Copied the label ForeColor and added a lot of transparency)

Import and your result should look a little like this.

Axes and other items on the chart XML has been cleaned up for the custom date chart in MS Dynamics CRM.

Notice there may be some conflicts between the date label and the actual value, when the value is low. That can be alleviated by either simply removing the value, it will still appear in the hover text, or giving the chart some more horizontal space if it is on dashboard.

The context where I usually use custom date formatting is on Key Figures Charts.

Key Figures Chart in MS Dynamics CRM displaying to the point relevant data where it is needed.

Using the custom date formatting, I can write the dates in whatever format desired and can even leave out irrelevant pieces.

Add Some Corporate Branding

Finally, add this section right before the </Chart> closing tag.

	<TextAnnotation X="50" Y="93" Text="Elev8 Solutions" TextStyle="Default" Font="Verdana, 30px" ForeColor="LightGray" ToolTip="Elev8 Solutions - Microsoft Dynamics CRM Partner -"/>

Chart with an annotation added in the chart xml for custom corporate branding.

Looks great! You can read more about Elev8 Solutions and our innovative immersive approach to CRM projects here. Which also happens to be where I work.

As always, thanks for reading and please sign up for my newsletter on this page and follow me on Twitter

, , ,


Display only complete weeks or months in charts

Here’s an issue that can be rather annoying. You want to display activities by week. You set up your chart and have a view that includes data from the last 5 weeks.

MS CRM chart with incomplete week highlighted

Incomplete week highlighted in chart

The only problem is, that a view showing the last 5 weeks doesn’t show the last 5 weeks. It is showing you the last 5 x 7 days, or 35 days. If those 35 days end on a Thursday, your chart, showing you information by week, will only have data from two days of that week. In other words, the chart will (inaccurately) present you as a slacker that week. We all know that is not the case, so we will need to make sure that those charts have an accurate representation of weekly data.

To correct this, I will create a view with 6 weeks of data, but limit the chart to show only the last 5 weeks. That will ensure that all columns in the chart represent a complete week’s data.

Note: This approach also works for months. However, if your fiscal periods are aligned with months, a View with the last 5 fiscal periods, will actually be the last 5 full months. A view with the last 5 months, will be the last 5 x 30 days = 150 days.

First, I’ll create the weekly chart in the editor and then export the chart xml.

I’ll start by adding an order clause, and reverse the standard order. The descending=”true”, means the most recent week will now be the first item, rather than the last.

Add order clause to MS CRM chart xml. order alias="start" descending="true"

To remove the sixth straggler week, I’ll add Maximum=”5.75” to the Axis X. This will ensure that I am only displaying the first 5 items and not the 6th. This is why I had to change the order of the weeks.

Maximum added to X axis in the MS CRM Chart xml

Why set the maximum at 5.75 and not just 5. If set to 5, the maximum property would cut the chart exactly at 5, which would be the middle of the 5th column at the interval mark, highlighted below. If maximum was 5, only half a column would be displayed.

MS CRM Chart activities by week, displaying only complete weeks by modifying the chart xml.

Now we have a chart where all the weeks have a complete set of data. The exception of course, is the current week, which depending on the day might not be complete. That however, makes much more sense to the user.

If you want the oldest week to the left, add IsReversed=”True” to the X axis properties.

X Axis is reverased by adding IsReversed="true" to the xml for the MS Dynamics CRM chart.

Thank you for reading. Please sign up for the newsletter to be up to date on new blog posts and follow me on Twitter 

, , , , ,