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.
- Change the aggregates to “min” (this is the secret sauce)
- Add the custom property DrawSideBySide and set it to False
- 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.
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.
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.
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.
crm chart guy 
Reblogged this on Francois Pienaar and commented:
The Dynamics community keeps ’em coming. Great job!
Hi CRM Chart Guy, can you send the whole xml for one gantt chart, after importing the xml i get an error cannot rendering the chart.
Great post do you have the xml for the other examples, would love to get a head start on them.
If possible, i would also like this, it seems like it could be incredibly invaluable!
Reblogged this on Naked CRM and commented:
Great post on creating a gnatt chart using standard CRM bar charts and a little xml update.
[…] Reblogged from crm chart guy: […]
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.
@comment 1 I updated the ” with ”.
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.
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 🙂
If I change aggregate=’min’ I get an invalide xml or format error on import.
Hi Pierre – you shouldn’t see any errors on import, so I’m guessing it could be the wrong type of quotation mark or something similar. I use notepad++ (free) for all my xml edits.
Quotes seems fine. I also use Notepad++ what a great tool. Can it be that we are on Rollup 15? I tried pasting code but that it didn’t go through. Email maybe?
I actually get the same error. I use sublime text 2, I tried exporting the chart XML, just saving it using sublime then re importing, it worked fine.
Then I exported it again, I changed the count to min, saved it, tried reimporting, and got the error : The specified XML file “Gantt chart opp.xml” is either not valid XML or does not conform to the chart schema.
Thanks for your blog !
Hi,
is there a resolution to this problem? I also get the error when I change the attribute to “min”. If I change it back to “count”, the chart imports just fine…
Unfortunately changing the aggregate seems to only work in CRM2013. I’ve updated the post too with the information.
Thanks for the great post! If you want to make it work in CRM 2011, you could remove the aggregate function in the fetch-xml.
Or set it to false:
It worked for me in CRM2011!
Hi Nils – Thanks for sharing. That’s great news. I’ll update the post.
Hi Nils,
That is great news. Can you provide a bit more detail on how to make this work in CRM2011? I removed the aggregate function in the fetch-xml, but now it appears the category-xml is giving me an error.
Sorry for my late response. This is my code for CRM2011.
{AE362395-A471-E411-9406-00155D05B412}
Plantafel
sit_eaprojektpipeline
false
Thanks for sharing your code, Nils.
Could you please post it as a picture. WordPress does not show it as text.
Thank you
CRM ChartGuy,
Thank your for this post.
Simply removing aggregates and groupby in attributesand category collection does not work. Could you please be more explicit on how to make it work in CRM 2011?
Thank you again!
Hi Andrei
There’s a picture here of the data description https://crmchartguy.files.wordpress.com/2015/01/2011-data-description-for-gantt.png
It works!
Thank you very much for this example.
Keep doing great stuff!
You show how to start the y axis at a certain date, is there any way to start it on “today”?
That would have been nice. Unfortunately, I don’t think that’s possible.
Quotes seems fine. I also use Notepad++ what a great tool. Can it be that we are on Rollup 15? Here is my code.
{36EC913A-8078-E311-9F7E-005056A647C3}
Gantt
incident
false
Pierre, what I meant, is can I have a dynamic date in there? So the y axis always starts on todays date. Is this possible?
Thanks
Wrong thread Cameron sorry I don’t know about the date.
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
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
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.
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.
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?
I have the same issue too.
Post updated with information that might help on this issue.
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?
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
Post updated with information that might help on this issue.
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!
Post updated with information that might help on this issue.
Has anyone got this working in CRM 2015?
Hi Louis. I haven’t tried it in a “fresh” 2015 environment, but charts made for 2013 still works after CRM has been upgraded to 2015.
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 John. Did you try changing the Format on the Y axis? It’s described in the very last paragraph.
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.
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.
Hi Kiran
Check out the very last paragraph in the post. It addresses that issue.
Hi, It worked. Thanks :), one more question, the start date and end date are overlapping, is there a way to position them top or bottom.
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.
You can play with the labelstyle in the custom properties. There’s more on label positioning in this post https://crmchartguy.wordpress.com/2013/09/16/design-options-for-bar-charts-and-top-x-bar-charts/
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?
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
Hi Stefan
Check out the paragraph towards the end about overwriting the format on the Y axis labels. That is usually the cause of those rendering errors.
Hi – just wondering if there is any update / if this solution works with 2016 version of 365 online? Or are there any new ways to make Gantt charts in MS CRM?
Thanks,
Kris
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.
Richard, did you manage to resolve your date issue? I’m trying to do the same thing – put in colour coding depending on the status of the project, and am getting the 30/12/1899 date appearing on all my active projects. Below is the FetchXML I’m using to try to get the data, but given that each project is either open or closed, I don’t know how I can get around it!
Hi Sam
Wordpress unfortunately does not allow xml in the comments section. You definitely want to check your data or xml to ensure you don’t have any empty dates in there. You can use the FetchXML Builder in the XrmToolBox to check the actual data in a table format. You can see an example of that here.https://crmchartguy.com/2018/04/25/use-the-fetchxml-builder-to-help-create-charts-in-dynamics-365/
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?
Reblogged this on Passion Dynamics and commented:
Nice blog by crm chart guy – made it look easy and simple.
Great post! This had been a long standing user request.
Quick question: Is there a way to make it possible to drill down to records being graphed?
I have confirmed all the fields used on the chart are searchable… we on on 2011 RU18.
Thanks for your blog!
I seem to be missing something. I made the changes to the XML and the added the Y-Axis fix but I still get:
“The specified XML file “Gantt Chart (1).xml” is either not valid XML or does not conform to the chart schema”
Any thoughts?
Hi CRM Chart Guy,
can you provide the code source of the chart displayed at “Divide the bar into multiple sections” ?
Is there a way to dynamically adjust the Y Axis minimum? I.E. set it to start at the lowest date value in the set of results? I noticed it defaulted to 1900. You mentioned that it can be manually set to a specific date. That would not be practical as data will start at different dates. You also mentioned that perhaps a RangeBar would have had some advantages. Any tips?
No, but if you do not add a minimum it will auto-adjust. That’s as close as you will get.
Hi CRM Chart Guy,
I would like to ask you about situation when we have more than one Opportunity (Horizontal Axis).
For example I set Sales Projects instead Opportunity and a few Sales Projects has the same name.
In this case I see on the chart only one of them and all in the list.
How to solve that issue?
Is any unique ID for each Sales Project which I can add to the Horizontal series??
Also I would like to kindly ask you:
“can you provide the code source of the chart displayed at “Divide the bar into multiple sections” ?”
Thank you!
Use the opportunityid, or the ID field of the your project entity, instead of the name field to avoid combining opportunities or projects with the same name.
I don’t share chart xml’s that use custom fields which that one does. Basically because if anyone else uses them, they won’t work. However, the process I used to create it is similar to this post https://crmchartguy.com/2013/03/10/aggregate-total-on-top-of-stacked-column-charts-or-bar-charts-in-ms-crm-2011/ where you filter and aggregate different set of data and then you can assign the series colors as needed.
I am following your tutorial above but getting the following error below after doing the Charts XML edits.
An an error occurred while the chart was rendering in the Chart Edit window. Any suggestion on fixing this.
Hi Ankit. You may be missing the step for changing the format in the labelstyle section. It’s detailed in the last paragraph.
Can I send you the xml that I am working with.
Any chance you can put it on OneDrive or similar and send me a link? WordPress unfortunately breaks all xml code in comments.
Below is the link to the charts that I have uploaded to one drive.
https://aec360-my.sharepoint.com/:u:/p/apatel/EQKXdVmh3iNGqfF5veXAlVwBB_b_MhtscILqDLprJjDI-g?e=gr3TgI
Please let me know what you find incorrect with xml.
Hi Ankit. Yes, you are definitely missing the format change described in the last section of the blog post. This is what is causing the error. You also need to remove the secondary Y axis. You still have it in there.
Can you send me the working XML of the chart as I unable to fix my issue?
Is this working in dynamics 365? I followed all instructions but it shows all bars of same size.
Hi,
Sorry to return to old issue like this, but has anyone managed to create GANT -chart with comparison between Proposed and Actual dates? That’s 4 series I guess but I can’t seem to get rendering right and I was thinking can it be done at all?
Anyone managed to create color coded gant -chart? I managed to draw the color coding but logic of the start date is wrong because chart draws 30.12.1899 even when I have the data in place (Actual Start Date of the Campaign -record)
Hi Jukka
You will likely need to add a static start date to your axis. So add the needed value to your Y axis.
That being said, this method of creating a Gantt chart will not work when converting to the Unified Interface, so just a heads up that the process will be outdated. There’s unfortunately no substitute way of creating this at the time of writing.
Thanks, I did that out frustration today but good to know my decision isn’t all bad 😉 Hopefully some solution will be found for UI.
Thanks for providing this info. I have a question I don’t yet see asked/answered. I was able to create the chart. It appears when editing the chart, but it doesn’t appear in the dashboard. Any ideas?
Thanks,
Cassie