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.

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.

Gantt Chart in MS Dynamics CRM

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.

Chart Editor set up for creating Gantt chart in MS Dynamics CRM

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.

Chart based on setup in chart editor - step 1 for creating MS CRM Gantt chart

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)
    aggregate min
  2. Add the custom property DrawSideBySide and set it to False
    DrawSideBySide false
  3. Remove the secondary Y axis
    3 Secondary Y axis delete Count Distinct

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

Chart after first modifications - Gantt chart in MS CRM

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.

Color White

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.

Gantt chart in MS CRM 2013
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 value
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 Sales Stages
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  

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.

Format

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.

72 thoughts on “Create a Gantt Chart in MS CRM

  1. Great post! This will clearly come in handy if you have some type of Project Management handling in your CRM. Two Comments 🙂
    1. If I hide the legend series like you suggest they are also hidden at the bars so the individual dates are no longer visible. Instead I updated the with the additional criteria [Enabled=”false”]. This hides the Legend but keeps the dates at the bars.
    2. If you have two Opportunities with the same name only the first seem to show. What you want is to have two blue bars at the same row but at different date periods. Any idea on how to fix this or why it occurs?

    Thanks again for some great posts – you have really open my eyes when it comes to using charts in CRM.

      • WTF – Updated the Legend xml container with Enabled=”false”!. greater than and less then chars are apparently forbidden in comments 😦

    • Hi Martin – Thank you very much. Glad you liked it.
      1. If you use the IsVisibleInLegend you can hide the series in the Legend. If you at the same time keep the IsValueShownAsLabel=”True” in the series, you will still have the date label inside the chart. That way you can control per series, which you would like to keep in the Legend or not.
      2.If you group by “name”, then two opportunities of the same name will be combined. I’d recommend always using “opportunityid” for the groupby. This will list two opportunities of the same name separately.

  2. Thanks ChartGuy! That’s the thing. I actually started by grouping by a related entitys parent entityId but still it only shows the first instance even though there are 2 or more date “periods”. In my example the chart is looking at the start and end dates of a custom entity “Demo Logs” which in turn has a Relation to “Installation” (custom) which in turn has a parent entity called Solution (custom). So by grouping on ParentSolutionId of the Related Installation, that should do the trick as you describe – although is does not 😦

    If you click on a bar in the chart though, the corresponding view filters and two (when there actually are two) records appear for that Solution – but still the Chart shows only one bar at that Solution row??

    Thanks for your input 🙂

  3. Hi CRM Chart Guy, Thank you very much for providing this site, I find your site immensely helpful and a great resource.

    If you don’t mind I have a question that I can not fathom after many hours of research:

    Its basically: How to sort a stacked bar.

    I have created a stacked bar chart which shows each owner of an opportunity along the bottom and each bar shows the sum($ estimated) for each opportunity at each stage. So This will show each sales person and then show stacked above their name how much money is in each of the opportunities grouped by opportunity stage.

    The only issue I have is that I cant order the stacked bars (or the legend) so that the opportunity stages list from 1 to 6 (there are 6 stages).

    I have numbered each stage name (and the underlying ID is numbered the same) however the legend and the stacked bars stack randomly. (however always stack the same random way)

    Do you know how I can make sure that the bars stack in the desired order ?

    Many thanks in advance for any help you can provide.

    With regards,
    Mark

    • Hi Mark. Thanks for reading.
      Option Sets are notorious for sorting. The sort order is not based on any of the values available to modify in the option set. I’m speculating, but there might be a reason why the oob Pipeline Phase field is just a text field.

      You could have a workflow copy the value of the option set into a text field. That’ll make it easy to sort by.

      Otherwise you’ll need to go this route https://crmchartguy.wordpress.com/2013/03/10/aggregate-total-on-top-of-stacked-column-charts-or-bar-charts-in-ms-crm-2011/ and create a filtered series for each of your opportunity stages. This will give you a lot more control over the order, colors and design, but you might lose a little on the ability to drill-down.

      • Hi CRM Chart Guy. Your response is very much appreciated. I’m going to look at implementing both those options and consider the merits of all three. Its funny that something that would seem so basic is not simple to achieve!
        Many thanks again.
        With regards,
        Mark

  4. Hi CRM Chart Guy,

    I am pretty amazed at your talent, you’re doing thihings I had no idea were even possible with CRM. I am having a slight issue with this chart, I can get it to work fine per the instructions above, however when I add it to a dashboard it is defaulting back to the ‘base’ view, rather than the ‘Gantt’ chart view, do you have any idea why this might be? In the Dashboard editor it displays correctly, so I am pretty stumped!

    Many thanks,

    Ryan

    • Oh, wait a minute, it seems to have resolved itself now! Apologies for the confusion.

      I was wondering if you had advice as to how to change the Date Format on the X Axis from the American style (Month/Date/Year) to British style (Date/Month/Year)?

      • Hi Ryan, thanks for reading.

        Interesting question. Normally you can change the format in your personal options. Or alternately define the format in the system settings. The charts will use these settings to display the dates.
        For whatever reason, that format doesn’t apply to an axis created in this way. It’ll stay with the US format regardless of your personal or system settings. So if you do change it, the date labels inside the chart will have UK formatting, and the axis will have US formatting.

      • Hi CRM Chart Guy,

        How odd, it isn’t a major issue though. I appreciate the work you’ve gone into to creat these charts, wish I could claime to be even half as good at XML!

        Ryan

  5. Hi. Excellent blogs. Keep them coming!
    I am also stuck in the date formatting issue on the x-axis. It also seems that the X-axis is ignoring any formatting I try to give it in the XML. Need to come up with a workaround I guess.

    • After some investigations I found my workaround. You must give the Y-axis a formatting parameter. Below is an example. Why the Y-axis and not the X-axis which seems the obvious choice? I honestly don’t have a clue. Below is a link to more date formatting options. The end result is however that the format is hardcoded in the chart so your US based users will be confused.

      http://msdn.microsoft.com/en-us/library/8kb3ddd4(v=vs.110).aspx

      • Hi Lasse. This sounds very interesting. Thanks for sharing. Are you on 2011 or 2013? And if 2011, which UR?

        I’ve in the past tried to use the date formatting options without any luck. It wasn’t supported by CRM at the time. I will have another look now though.

        The axes are reversed on bar charts, so that’s why you are having the X/Y axis mix up.

      • It won’t let me respond to you reply to your response so I have to write here. I am working on a on-premise CRM 2013 installation with no update rollup installed. So the build is version 6.0.0.809.

        I got the date sorted out so the only drawback is that it will show like that also for those who have chosen the US format. Luckily not an issue in this project.

        I disabled the basic axis labels and used your ideas from your previous blogs to put the series labels within the chart so now it is using the space sensibly. Attached is a link to the chart. Even the sorting is making sense now.

        Thanks. It is really useful.

      • That is a very cool find. Thanks. Unfortunately the date keyword formatting doesn’t seem to work in any other situation than this one. But for this purpose it works very well and you can define your own date formats. For example Format=”d MMM” to display 15 Aug. If I find out how it can be utilized in other areas I’ll be sure to write about it.

  6. Is there any way to in a Gantt chart with horizontal bars to have a single thin vertical bar or line to show represent today’s date? So that there is a line to show today so you can relate where you should be in the Gantt chart?

    • Not really unfortunately. That type of dynamic indicator is not supported by the axis properties. The only way I can think of how that might be possible, is if you have a process, that every 24 hours update a field on each opportunity, or whatever entity you are using, with today’s date. Then you might be able to do some creative formatting with labels or bar types to indicate where today is on the chart.

  7. I followed the sets above to create the Sales Chart but I get an error when I try to view the chart in CRM. The error only says “An error occurred while the chart was rendering.

    I am testing this chart out in the following environment:
    Trial – CRM Online 2013 v6.1.0.575 (BD 6.1.0.575)

    Here is the xml code I am using, what am I doing wrong?:

    {10D0EA8F-11E8-E311-8240-6C3BE5A8A0E4}
    Sales Gant Chart
    opportunity

    false

    • I can’t see the code, but from what I hear, it seems that the CRM chart engine have become a lot more sensitive. Also around label formatting.
      I have seen the “Gantt Chart” work in the spring release as well, so it is possible.

      • I have experienced the same issue after applying 2013 SP1 and have not been able to make it work. It appears that both and the “min” aggregate and custom property “DrawSideBySide” is not supported. The chart imports OK, but it won’t render in the UI. Are you able to provide some suggestions for trouble shooting as I’m a huge fan of the work you have done with this chart concept?

  8. I too am having an issue getting this to work on 2013. It appears aggregate=”min” is not supported. It is not an option in the Chart editor and the chart will not import either once I have modified the xml file to change from “count” to “min”. any suggestions? I would really like to use this functionality.

    • never mind. I am on CRM2011 and just say this note:

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

      • I am working in CRM2013 SP1 and I also get the error. It worked for me about two months ago, but it’s no longer working. Any ideas?

  9. hi,
    I am trying to set the aggregate value for the date field to min and getting an error. I a doing this for CRM 2013 and it does not work, any other way I can do this.
    Thank you

  10. I am also using CRM 2013 (cloud version) and am getting the rendering error when I set the date fields to “min”. If anyone else has made this work, please let me know!

    • Hi Louis, I work in CRM 2015 online environment and I struggle to get it work. It’s throwing error after changing the aggregates and also the mentioned trick for CRM 2011 does not work in 2015. Have tried a lot of modifications of the XML file, but with no luck. Have you been successful?

      • Hi CRM Chart Guy, thanks for pointing that out, it works now! My bad, next time I will make sure to read till the last paragraph before asking.
        I can now confirm that this gantt chart works in 2015 Online environment, but no unfortunately without dates on the Y axis.

  11. Hi I am using CRM 2015 online,

    I just did first 3 steps (Change aggregates to min, adding Dawsidebyside and Removing Secondary Y axis) and i am getting error.

    “An error occurred while chart was rendering.”, Solution imported without any issues. Do you think something changed in CRM 2015.

  12. Hi, Is there any way to keep the dates not overlapped, like in the above example titled (Final Gantt chart in MS CRM 2013 showing Created On dates and Est. Close Dates for open opportunities), there is start and end date overlapped and hard to read, if there is any way to not do that please let us know, that will be very helpful. Thanks.

  13. Hi.. Thank you for a very nice chart!
    I am using CRM 2011 and got the basic Gantt Chart working. Now I am trying to “color-code the whole bar” according to status (open, lost, won), but it is not working.
    Anyone has an working example of this?

  14. Hi
    I have tried everythink I know.. it doesn´t work…
    I have crm 2013 6.1.3.119
    if I change the agregate from “Count” to “min” I get the rendering error.
    Can you help me please?

    regards Stefan

  15. Hi CRM Chart Guy
    I have recently deployed the PSA Application in D365 and noted with interest that the Project Timeline chart that ships with the Microsoft Solution includes reference to ‘The CRM Guy’ 😉
    I tried to modify the chart to include more services with filters to show Projects at different statues in different colours. Your post on the subject was invaluable.
    I have almost got it to work with one issue – When I add the filters a default date 30 December 1899 appears on the Y axis for each project.
    Not sure why.
    Can you help?
    Thanks
    Richard

    • Hi Richard
      That is awesome!! Thanks for pointing it out. I actually hadn’t seen that.

      The 1899 date usually appears when there’s a date field that has no value and it’ll get the lowest possible date.
      Make sure you don’t have any empty date fields, either by populating the data or try and exclude those records via the fetch. Hope that helps.

  16. Great post! I see parts of it are availale out of the box nowadays, but the different colors used per stage are not. How did you do that?

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