One of the most common questions for sales dashboards in MS CRM is “can I have a total on that Sales Pipeline funnel chart?”
Seems like a fair question, but somehow this not possible. At least not in a manner where the number can be formatted in a way that can be read properly.
My preferred method, is to create a chart that shows nothing but the key figures. Total sum of opportunities, the average amount of the estimated revenue, how many opportunities there are, next estimated close date, etc.
The chart itself can look like this.
And here, put in context on a sales dashboard on top of the pipeline funnel.
How to create the Key Figures Chart in MS Dynamics CRM
As usual, start the chart editor so it can do the majority of the work.
I have added a series for each of the key figures I want the chart to show.
These are all of the chart type bar. For the dates, Est. Close Date and Created On, only the count aggregates are available. We will need to replace that later in the chart xml for minimum and maximum date values.
For the category I selected Status. My view for this only includes active opportunities, and for this type of chart I need all the opportunities to fall in to the same category. In this case it is the active statuses.
Edit the chart XML
Export the xml and open it in your editor. Notepad++ is still my go to xml editor.
In the fetchxml section, the date values will need to be replaced from the countcolumn aggregates.
In the example I have also renamed all of the aliases, so it is easier to see which attribute is what. If you do this in your xml too, remember to update the aliases in the categorycollection as well.
The series that displays the next Est. Close Date is set to “min”, giving us the lowest value of the dates which will be the next date an opportunity is supposed to close.
The series for the date of the latest opportunity that was created is set to “max”, which will give us the highest value in the set.
If you import the xml now, it should look like this.
Good. All the values we want are now in the chart including dates on the bottom two bars.
Now for some major clean up as we do not need the axes, titles or legends for this. They do more harm than good in this case.
- AxisY2 section
- And YAxisType=”Secondary” from one of the series
- Titles section
- Legends section
- Enabled=”False” to AxisY
- Maximum=”1″ to AxisY – this is to help with alignment of the text
- Enabled=”False” to AxisX
Edit the presentationdescription xml
We are now ready to work on the presentationdescription of the xml. This is where we set the labels, formatting and colors.
We will need to edit both the normal properties and the CustomProperties for this chart.
First we will edit the colors and labels.
- Added Color=”Transparent” to each series to make the bar invisible. We only want the label.
- IsValueShownAsLabel=”True” for sum, average and count as these are numbers.
- IsValueShownAsLabel=”False” for the date values as we need to use keywords to display these better.
- Font size has been increased for each series.
- LabelFormat for sum, average and count values to add description.
- Label for each of the date values with the #VALY keyword to insert the date without time.
- Notice there are some extra spaces in front of the date values. It takes a little tinkering with these to get the alignment right on the final chart.
You can read more on label formatting in this post LabelFormat Cheat Sheet
- Added BarLabelStyle=Left to each series to help with alignment.
- Adjusted PixelPointWidth for each series to accommodate the larger font sizes set earlier.
- Adjusted MaxPixelPointWidth for each series due to the larger font sizes.
Import and here’s the final chart again.
One chart with all four key figures inside one chart component to put on a dashboard.
If you need more, the MS CRM chart xml will support up to nine key figures added in this manner.
Krishna Jacques has been kind enough to point out that if you need to include numbers in your label, such as labels for number of days out like “Sum of 30-60” and then the value, you will run in to some formatting issues. It is fixed by adding single quotes around the numbers.
Also, date formats can be customized using labels like “MMMM dd yyyy”
If you do this, make sure you set IsValueShownAsLabel to True and add the custom date format in the LabelFormat property like this.
More details on the date formatting strings here. https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings
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
Here’s the xml file. As usual, provided for fun and games only.
Thanks for reading!