When creating charts for Dynamics 365, the Advanced Chart Editor in the XrmToolBox can handle most of the presentation and formatting of the chart. The work with the data happens in the fetch part of the chart XML, which still needs to be edited manually. Especially if you create charts that utilize relationships and N:N many to many relationships.
For those situations, you can use the FetchXML Builder in the XrmToolBox to help modify the fetch. Aside from helping with the syntax, relationship names, property names, and basically writing the FetchXML for you, it can also show you data retrieved in a table format. That can be very helpful in troubleshooting the fetch part of your chart as you can see exactly how the data is retrieved.
The video below demonstrates how I use the FetchXML Builder to help create a chart on a N:N relationship from the User entity to the Security Role entity.
I’d recommend watching the video on full screen on YouTube as some of the property texts and drop-down boxes can be a little small.
Update: As Jonas from the FetchXML Builder accurately points out in the comments, there’s no need to do the second link-entity to get on the Security Role entity, just to do a count. I usually add this step as it then allows me to group and order by properties on the related entity as well. The step is also required if we were doing a sum on a money field rather than a count. You also need it if you want to filter the records on the second entity. For example, if we only wanted to count security roles with a particular name. I missed pointing that out in the video.
Hope you found it useful. As always, please sign up for my newsletter and follow me on Twitter Follow @CRMChartGuy
crm chart guy 
Pro Tip:
Since you are not actually counting “role names” but rather just “roles”, you can skip the second link-entity and just count roleid on the intersect entity!
See how: http://www.screencast.com/t/I4PwFwVRi
Yes, thanks for pointing that out. I have updated the post on why I usually include it and what you can do with that additional step.
Hi all,
Apologies for piggybacking on another post, but I was wondering if you had any advice or had made any posts in regards to it being possible to create a chart in Dynamics 365 that allows you to display the top ‘x’ information, alongside the overall information that this is pulled on.
to try & explain better, I have been tasked with creating a chart that would allow me to display the annual gross sales volume of our top 5 selling accounts, but also displaying the overall gross sales volume of all accounts within that same chart.
I had assumed i could maybe achieve this via manipulation of the secondary axis, but i am struggling with it only showing the ‘Top X’ on each series, which inevitably returns the same data.
Any help/guidance would be appreciated.
I think you probably need to have a workflow of some kind to tag the top account, and include that flag in the query.
But then again, I’m not the chart wizard.
Ulrik is 😉
As Jonas mentioned, you will need a property on the accounts for the query. If you do a top 5 in the fetchxml it will always apply to all records. Not just a subset. Only workaround I can think is creating a chart for all accounts, but limit the axis to only show the first 5 items. Then you could have a second series that uses the #TOTAL keyword for the label to display the total for all the records. You’ll probably be able to make the chart look decent. The biggest obstacle is that keywords like #TOTAL do not have formatting supported in Dynamics 365, so “$8.5M” will look like this “8537461” and be fairly difficult to read.
[…] charts on those. We just need a supported starting point. So in an attempt to prove myself wrong, I used the same process as described in this earlier post on security roles, and simply pointed the link-entity in the user chart, to the duplicate detection rules […]
FetchXML Builder is not available in the tools list anymore, Is there a tool that replaces it with the same functionality?
There was a brief issue with a recent update to it. It should be back now.