Create a Gantt Chart in MS CRM

Until recently I thought that Gantt charts couldn’t be done in MS Dynamics CRM without turning to some serious development, which I found unfortunate because this is a popular type of chart and users find them easy to read. Turns out you can, and it’s not that difficult to create a simple Gantt Chart in MS CRM.

Important Update regarding Unified Interface in Dynamics 365.
When converting to the Unified Interface in Dynamics 365 or any Model-Driven Power App you will also get a new charting engine. This comes with some limitations as Min and Max values on a date fields are no longer possible. This Gantt chart method relies on the min aggregation for a date field and will therefore NOT work in the Unified Interface. As of writing, there is no alternate method for a Gantt chart using the built-in charts.
Updated: 3/23/2020

EDIT regarding CRM2011: This post relies on changing the aggregate of a date field to “min”, which seems to be exclusive for CRM2013. CRM2011 will give you an import error when you make that change to the xml. However, a reader has been kind enough to send me the details of how make it work for CRM2011. In the fetchxml you will need to remove the aggregate=”min” from each of the attributes, and also remove the groupby=”true” along with its corresponding alias in the categorycollection. Thank you Nils for sharing.

In this post, I’ll create a simple Gantt Chart for the opportunity entity, that shows when the opportunity was created and when the Est. Close Date is. It is a good visual indicator for how long the opportunity is planned to be open, rather than looking at the dates. After that, I’ll share some ideas for more advanced versions. Everything of course, is made with only xml modifications for standard MS CRM charts.

Here’s the simple version which I’ll create step by step in this blog post, showing Created On and Est. Close Date.

Chart Editor – Create the base for the Gantt Chart

Since there is no Gantt Chart type in MS CRM, we have to use the standard bar chart instead and make some modifications to the xml so it looks like a Gantt Chart. What I am really doing is creating two bar charts, one bar to reach Est. Close Date, and then one bar to “cover” a part of the bar up till the Created On date. This post uses a custom Created On date field to use in place of the system Created On field, just so it’s easier to create demo data.

As always, I’ll use the chart editor to do most of the work.

Note that the order of dates here are important. The first date on the chart, in this case Created On, should be on the bottom. The chart type is Bar, not StackedBar.

Let’s have a look at the result.

Basically this looks nothing like a Gantt chart. The chart is just counting the number of dates. That is because count aggregates are the only available options in the chart editor.

Chart XML edits

Let’s export the xml and make some edits.

1. Change the aggregates to “min” (this is the secret sauce)
   
2. Add the custom property DrawSideBySide and set it to False
   
3. Remove the secondary Y axis
   

Let’s import it back into CRM and have a look.

Much better. We still need to do some xml modifications, but let’s take a look at what happened.

Changing the aggregates to “min” changed the Y axis to a date format, and the values relative positioning are correct in comparison to the dates. Very unlike a date grouping which only includes the values present in the data set on the axis. You get the same result using the “max” aggregate, so you can pick either.

The DrawSideBySide=False ensures that the two bars are drawn on top of each other, rather than next to each other. Looking at the image above, the blue bar, Est. Close Date goes all the way back to the X Axis, but a part of it is covered by the orange “Created On” bar. If a DrawSideBySide property is added to one series, it is automatically applied to all series of the same type, so we only need to add it on one of them.

Finally, we need to remove the secondary Y axis, because it’s not needed in this scenario (as usual).

Let’s make the next set of modifications to the chart xml. Change the color for the Created On series to white, by adding Color=”White” to the series.

White is also the background color and border color. This will make it appear as if there’s no second bar on the chart, and we only see that start and Est. Close Date for the opportunity. At the same time I’d recommend removing the series from the legend and/or modify the legend text to something more suitable.

Final Gantt chart in MS CRM 2013 showing Created On dates and Est. Close Dates for open opportunities.

Notes

If you need to force a specific start date on the Y axis, then this has be inserted as a number. The chart is using the same date number system as Excel where Jan 1 2014 is 41,640. So if we wanted the Y axis to begin on January 1 2014, we would insert Minimum=”41640″ in the AxisY properties. These numbers are easy to get by entering the date in Excel and then formatting the cell as a number.

The chart type RangeBar, originally seemed like a more suitable option to create a Gantt chart with, since it has two Y-values for start and finish. Unfortunately, the RangeBar chart does not allow a secondary date aggregate of “min” or “max”.

Advanced Options

Color code the whole bar

It is possible to add more series to the bar chart so you can color the bars differently depending on which phase in the pipeline they are, or you can group the estimated revenue into different categories. See this post for how to create and work with multiple filtered series in bar and column charts.

Gantt Chart for opportunities color coded according to estimated value

This chart was made creating 3 series, filtering the opportunities into different categories based on their estimated value, but could for example also be color coded according to current pipeline phase.

Divide the bar into multiple sections

We could also add more dates to the Gantt chart. Since it consists of a layered bar chart, we can have up to 9 different dates, which is the most allowed by the fetchxml. The fetchxml allows up to 10 attributes, one of which needs to be the groupby.

A Gantt chart for opportunities, where the end date for each stage in the sales process is registered in a separate field, could look like this.

Gantt Chart showing how much time was spent in each sales stage. Note that this was made only to show the capability of adding multiple date fields to the Gantt chart so each bar can have multiple sections. Further refinement on the chart above is certainly needed.

The user can then overview the progress on opportunities and how much time is actually spent in each phase and compare it to other opportunities. The tricky part is ensuring the dates are set in the right order in the xml, as the bars overlap each other.

Hope you liked the post. Please sign up for the email newsletter and follow me on Twitter  Follow @crmchartguy

IMPORTANT UPDATE: A lot of people are experiencing issues when creating this chart. This is due to MS Dynamics CRM, for whatever reason, no longer supports dates on the Y axis. You can get around it by adding this to the Y axis in your chart xml.

And then optionally you can make the labels transparent. It forces Dynamics CRM to not render a date on the Y axis, which is where it fails. Now it just writes some text instead. That dates are no longer supported on the Y axis is very unfortunate. The Y axis is basically just blank. The best option is to keep the date on some of the labels so you at least can see them inside the chart. Hopefully they’ll come back soon, but who knows. Please write a comment if you see it working with dates again.