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.

  <datadescription>
    <datadefinition>
      <fetchcollection>
        <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" />
          </entity>
        </fetch>
      </fetchcollection>
      <categorycollection>
        <category alias="groupbyweek">
          <measurecollection>
            <measure alias="dateformat" />
          </measurecollection>
          <measurecollection>
            <measure alias="countofopportunities" />
          </measurecollection>
        </category>
      </categorycollection>
    </datadefinition>
  </datadescription>

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

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

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

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.

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

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

11 thoughts on “Custom Date Formats in Charts in MS Dynamics CRM

  1. I really appreciate our willingness to help even without getting much in return. Hopefully knowing that you’re the man when it comes to CRM charting will suffice! This one directly answers one of my users’ biggest complaints. I agree and tried to argue that it’s better to refer to the weeks by their relative number in the year but how do you change a culture. Anyway, I appreciate the help very much!

  2. Hi,

    I’ve built this chart and it’s awesome, so thank you for the steps! One thing I’ve noticed though is that sometimes the label on the top bar isn’t left-aligned with the others. Instead it’s centered. Any way I can fix this to force it to stay on the left?

    Thanks!

  3. I tried using just the formatting for dates on the Axis on a normal graph in the Axis, so changed it to
    Format=” ‘Week of’ MMM dd” . However, it is not updating the format.

    What am I missing?

  4. So, can you do this with multiple measures? I am trying, but it doesn’t seem to be working. I have a chart showing count of Open, Blocked and Completed by Week. So, I thought if I put the date format in front of each one, it may work? No luck so far.

  5. Hi
    I really appreciate all you do. I do have a question on regards to this chart. How would I be able to add the missing weeks to show “0” if no activity was logged. ie in your example Week 16; week 24 etc. I am trying to create a chart to show activity by month but need to also show if no activity was recorded in the month Is this even possible?

  6. Hi – Just wanted to clarify that it is still the case that we cannot edit the actual axis to show something like “Week of Aug. 20” instead of “Week 34 of 2017”, correct?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s