Archive for category BarChart
When setting up a new Dynamics 365/CRM trial a few standard dashboards are included. However, I feel they lack something, and those who follow me on Twitter know I occasionally like to make fun of particularly the funnel chart. So here’s a hopefully improved version, that you can use, either in your organization or simply to spice up a demo environment.
Well, why not do something about it then. Plus, I’ve toying with this idea for a while anyway.
I did have the audacity to use the term “ultimate” in the title and the dashboard I provided is clearly not. However, with lots of input, we can make this much better.
Therefore; I would love to hear from you if you have suggestions how to make these dashboards better.
Suggestions could include, but are certainly not limited to:
- Ideas for new KPIs
- Ideas for new Charts
- Ideas to improve formatting and make the charts easier to decode
- Using charts contextually on record forms
- Better use of colors between the different charts
My only request is that you elaborate on your suggestion, and I will try to incorporate as much as possible and hopefully have this grow into something very useful.
At the bottom of this post, I’ve included a zip file containing a solution with all the components for the dashboards in this post. The zip file contains both a managed and unmanaged solution that can be imported to Dynamics 365/CRM, and all the chart xml files individually.
It should be possible to import the charts on Dynamics 365/CRM organizations, so I will try to adhere to the following rules and assumptions:
- Only use the charts within Dynamics 365/CRM*
- Only use system fields
- Only use system relationships
- Avoid using calculated fields for KPIs – unless it is a system field
- Take as much advantage of the existing setup as possible
*Why am I limiting myself to only using the charts within Dynamics 365/CRM and not including Power BI and Customer Insights. While Power BI and Customer Insights are powerful features, they are both tools external to Dynamics 365/CRM. You cannot as easily, share the charts and KPIs within CRM itself. Charts also have some significant advantages too. They can be contextual to the user logged in, respects the security roles, and they are real-time. “I just won an opportunity. I’ll happily wait an hour and then look at the sales leaderboard to see my position.” said no sales person ever!
The Sales Dashboard
The Sales Dashboard consists of the following charts:
1. Sales Funnel
Somehow it is not a sales dashboard if there isn’t a funnel on it. It is the universal indicator of a sales process regardless of how wrong it may be. I have done my best to overcome some of those areas.
2. Sales Leaderboard
The Sales Leaderboard is also a standard component on a sales dashboard. This one includes both won and in progress revenue. Users are ranked per the amount won. Current user is emphasized in a stronger color so it is easier to see your own position.
3. Sales KPIs about the open opportunities
KPIs include est. revenue, average value, number of opportunties in the pipeline, next est. close date and date the last opportunity was created. On the dashboard, the user can flip views between “Open Opportunities” vs. “My Open Opportunities” to get both group and personal KPIs.
4. Phase Loss Rate on Est. Revenue
This chart gives you the percentage of revenue lost per phase in the sales pipeline. For example, in the qualify stage, if opportunities est. 100 million entered the qualify stage, but only 45 million of est. revenue made it to the develop phase, then the loss rate for qualify is 65%. The same calculation for the develop phase, but of course excluding all the opportunities that never made it that far. In an ideal world, you would lose more opportunities in the initial stages and have a much higher win percentage towards the end.
Additional charts with variations on how the calculations are made are included in the zip file, including using the number of opportunities rather than the est. revenue.
5. Sales KPIs for Won Opportunities
Like the other KPI chart, but this one focusing on KPIs for the opportunities that were won.
Scroll down on the dashboard and you get to the Competitor Strength chart.
The competitor strength chart lists all your competitors, ranked by how many opportunities you have lost to them.
On top of the lost bar, you can see how many opportunities are currently in progress.
Finally, in another bar, you have the est. revenue of the open opportunities where this competitor is a threat.
Sales Pipeline Analysis
As mentioned earlier, I have included all the pipeline analysis charts in one dashboard for review. They all work in the same manner, although the calculations have a different focus as described by their names.
As always, samples are provided for fun and training purposes only. Test properly prior to using in a production environment.
Update Notes November 20, 2016.
Pipeline Charts now only uses the beginning number of the step name, so as long as your phases are 1 through 4, then you would not need to make changes. Won opportunities will also be filtered correctly and do not necessarily need to be moved to the close stage. Regardless of the stage on a won opportunity, it is assumed that it has made it through all the stages.
Added some of the pipeline charts to a separate User form to analyze an individual’s performance against how the company KPIs.
Zip file includes all the chart xml files individually and a managed and unmanaged version of the solution file for Dynamics 365.
Solution files for pre-Dynamics 365 environments are no longer included. However, all the chart xml files can be imported separately into previous versions. Presumably, all the way back to CRM2011 although I have not been able to test that.
Added a chart for the Opportunity Products to see what products are in the pipeline. Products less than 10% of the total are grouped into “Other”.
Original Solutions Notes
All the charts that use stages or the pipeline rely on the Opportunity field called Pipeline Phase. Most trial instances have data in this already.
Otherwise, it is populated either manually or by a workflow. The chart also assumes that the naming of the phases is kept from the trial so they are called 1-Qualify, 2-Develop etc. The assumption I made in the calculations is that won opportunities are in the 4-Close phase. Therefore, make sure that the process that updates the pipeline phase puts won opportunities 4-Close. They really should be in that stage anyway if won.
The solution files with Dyn365 in the name are specifically for new trials or environments that have already been upgraded. This solution also includes the Competitor Chart.
Solution files with CRM_8.0 in the name are for CRM2016 and earlier. This version does not include the competitor strength chart as there’s a bug in CRM that does not allow the distinct=true property to be imported in a solution file. This was fixed with Dynamics 365.
All the chart xml files are in the zip file as well, so you can import them separately, make modifications etc.
Thanks for reading. Hope you enjoy and please share and let me know of any good suggestions.
And don’t forget to sign up for my newsletter on this page and follow me on Twitter Follow @CRMChartGuy
N:N Relationships, or Many-to-Many Relationships, can be a little tricky to get information out of.
As an example competitors out-of-the-box have an N:N relationship with opportunities. Let’s say we wanted to look at a list of competitors and see how many active opportunities they are currently engaged in. With the chart editor and views, the best bet is to open each competitor record and count how many active opportunities it is related to. We can make that much easier with a chart like this.
Before I get started on how to build the chart, let’s first have a look at how N:N relationships are structured in MS Dynamics CRM.
I will use the competitor opportunity relationship as an example, but this applies to all system and custom N:N relationships.
While it is not apparent in the solution designer, the relationship is controlled by an intersect table, in this case called “opportunitycompetitors”.
We will need this exact name of the intersect table for the fetchcollection in our chart xml.
To find the name of the intersect table, open up competitors for customization in the solution editor.
Click N:N Relationships and open the relationship to opportunity.
Here, find the “Relationship Entity Name” towards the bottom of the form and make a note of the exact name for later.
Now that we have the name of the intersect table, on to building the chart. A full sample of the chart xml can be downloaded at the bottom of this post.
Build The Base Chart
As always, I start by creating a base chart I can use for editing the chart xml. I am starting on the competitor entity.
A simple bar chart counting competitors, grouped by competitor. Not very useful yet.
Simple – Count Opportunities Per Competitor
Export the xml and open it in your xml editor (Notepad++ perhaps).
Here’s the fecthcollection. I have renamed the obscure aliases to something more sensible. This is where we will do most of the work. If you change the aliases too, remember to also change them in the categorycollection.
Now I want to change the chart xml, so I get a link to the intersect table and can count the number of relationships the competitor has to opportunities.
I have now created a link to the intersect table and placed the count aggregate inside of it. The groupby is still directly on the competitor entity. I also changed the name of the count attribute to “competitorid”. This is because the intersect table only holds the id fields.
Import to MS Dynamics CRM and look at the result.
Now we have a list of competitors including a count of how many opportunities they are competing on.
On import you may run into this error message.
If that happens, just change the width of the chart or refresh the page and it will work.
Advanced – Count and Sum of Estimated Revenue from Opportunities
That was charting on N:N relationships in its simplest form. However, as always we would like to do more. How about including the sum of the estimated revenue of those opportunities, and also filter it to only include active opportunities.
In order to achieve that, we need to add an extra link-entity to get all the way to the opportunity entity, so we can access fields there directly. In the first example we stopped on the intersect table.
Sample of fetchcollection which links from competitor, via the intersect table, to the opportunity entity.
We now have two sets of <link-entity>, linking through the intersect table to the opportunity entity.
Notice the pattern in the links are name=”intersectable” from=”field with id from intersect table” to=”field with id from starting entity” and in the next line it is name=”the other entity” from=”field with id on other entity” to=”field with id on intersect table”. Since the id fields are mostly the same, once you have the name of the intersect table, these are fairly straight forward. Marketing lists are an exception to this.
Inside the <link-entity> to the opportunity entity, I have added a filter so we only include active opportunities. As usual, I just created a view in Advanced Find, downloaded the fetchxml, and copied the part with the filter.
I also added an extra attribute to include the sum of the estimated revenue for the opportunities.
Here’s what the final chart looks like.
I also made these optimizations to the chart, just to touch it up a little.
- In the Series
- LabelFormat=”C0″ – so values in the chart have no decimals – that’s the letter “C” followed by a zero
- DrawingStyle=Cylinder – in the custom properties just add some shading
- In the Axes
- Format=”C0″ – so dollar values on axis have no decimals
- YAxisType=”Secondary” – on the series containing the sum to get count and sum on different axes
- IsReversed=”True” – on the X axis so it is alphabetical from the top, not the bottom
A full sample can be found below with these changes included.
As a note, charting on N:N relationships also works on Marketing Lists (static only). That means you can create a list of users with a chart of how many marketing lists they are associated with. However, you will need to use a metadata browser to get the names of the intersect table (listmember) and its id field (entityid), which is different in this case.
Thank you for reading. Please sign up for the newsletter to stay up to date on new blog posts and follow me on Twitter Follow @crmchartguy
Sample chart xml for count and sum of opportunities per competitor
Here’s a sample chart xml ready for import to the competitor entity. It is made in an environment with no customizations so it should be easy to import. As always, samples are provided for fun only. Do not use in a production environment without proper testing.
If you made it this far. You’re awesome! Seriously. Tweet about it!
The 100% stacked bar chart is great to display the relative amounts within a series.
However, for some reason the 100% stacked bar chart comes without labels to indicate what the percentage is for each group. You can eyeball it, but that’s not very accurate.
It is a little tricky to get to the accurate result, so I will go through the process of getting there.
The obvious thing to try first, is to export the chart xml and add IsValueShownAsLabel=”True” to the series.
This gives us the sum of each individual phase. This could be useful in its own right, but I really want the percentage on the label.
The next obvious thing to try, is to put in Label=”#PERCENT” and set IsValueShownAsLabel=”False”. Why not? It works great for pie charts.
Now we get some great looking percentages, but the values add up to 100 on the second series. Not across like we would expect.
The magic combo to add to the series xml for percentage labels on a 100% stacked bar chart in MS Dynamics CRM is:
IsValueShownAsLabel=”True” LabelFormat=”#.00′ %'”
Finally we have proper percentages across on the bar chart as we would expect.
A couple of other optimizations for a percentage bar chart.
LabelFormat=”#.00 ‘%'” in the series to show the percentage sign and always have 2 decimals.
Format=”0’%'” on the Y-axis to remove decimals and add percentage sign.
More on where and how to add Format and LabelFormat in MS Dynamics CRM charts here.
Thank you for reading. Please sign up for the newsletter to stay up to date on new blog posts and follow me on Twitter Follow @crmchartguy
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.
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.
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”.
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.
Here’s a couple of design options to improve bar charts, fix some pet peeves of mine, or simply change things up a little.
Those pet peeves are truncated labels and always putting the top record on the bottom. Fortunately, both of those can be fixed as seen below.
I’ve also positioned the labels on the inside of the chart, which is a better utilization of space.
Top X Charts
If you make a Top 5 Bar Chart, you get this.
It has always puzzled me that the “top” item is on the bottom. Nobody want’s to see their name on the bottom of a list. My first idea was to change the order in the xml from descending=”true” to descending=”false”. While that will reverse the order of the Bar chart, it will also only return the bottom 5 records rather than the top 5. Good to know if you ever need to make a bottom x chart of something.
Turns out the xml modification is even easier. Add IsReversed=”True” to your AxisX properties and you now get a Top 5 Bar chart with the top record on top.
Truncation of Axis Labels
Label truncation is particularly annoying when users name all their records something like “Campaign for xyz” or “Opportunity for xyz”. All you see on the labels are the “Campaign for…”. Somehow the “…” always contains the most important part.
To fix that, plus a few other modifications, I’m going to:
- Add the axis label to the bar series, to get the label on the inside of the chart
- Left align the axis label via Custom Properties
- Make the bar color semi-transparent so it’s easier to read the labels
- Increase the text size a little
- Remove current truncated labels by disabling the X axis
- Use a StackedBar chart type
There are 4 spaces in front of the #AXISLABEL. That is to control the positioning and ensure the label doesn’t start before the bar does.
Font is increased from 9.5px to 11px.
ARGB colors was used to add transparency. In this case a little over 50% transparency.
Custom property BarLabelStyle=Left is added to make sure the labels are aligned along the X axis.
StackedBar is used instead of a Bar chart, because that, in conjunction with BarLabelStyle=”Left”, forces all the labels to start along the axis. On a Bar chart the label may move right, till the end of a short bar. Even when it’s left positioned.
On AxisX; IsReversed=”True” to get the top item on top, and Enabled=”false” to remove the original truncated labels.
Hope you liked it. Please sign up for new posts or follow me on Twitter Follow @crmchartguy