Add Key Figures to Dashboards in MS Dynamics CRM

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.

MS Dynamics CRM key figures total, sum, average, next est. close date. CRM chart custom xml.

And here, put in context on a sales dashboard on top of the pipeline funnel.

Key Figures in Context of Sales Dashboard MS CRM. Chart component with custom chart xml.

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.

1 CRM Chart editor to set up key figures. MS Dynamics CRM chart editor. Create base chart for furhter xml customization.

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.

2 Fetchxml edit the aggregates for the date to min and max. MS Dynamics CRM charts xml.

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.

3 first import of chart xml - Have all key figures, prior to editing layout in the MS CRM chart xml.

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.

4 Remove unwanted sections from the chart xml in MS Dynamics CRM.
Click to enlarge. Yellow sections removed. Green section added.

 

Remove

  • AxisY2 section
    • And YAxisType=”Secondary” from one of the series
  • Titles section
  • Legends section

Add

  • 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.

Standard properties in the presentation description modified in hte chart xml in MS CRM.
Click to enlarge. Yellow sections modified.

 

  • 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

The CustomProperties

Edit custom properties in the chart xml for MS Dynamics CRM
Click to enlarge. Yellow sections modified.

 

  • 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.

Microsoft Dynamics CRM chart with important key figures for Opportunities.

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.

Thank you for reading. Please sign up for the newsletter to stay up to date on new blog posts and follow me on Twitter

Here’s the xml file. As usual, provided for fun and games only.

<visualization>
  <visualizationid></visualizationid>
  <name>Key Figures - by CRM Chart Guy</name>
  <primaryentitytypecode>opportunity</primaryentitytypecode>
  <datadescription>
    <datadefinition>
      <fetchcollection>
        <fetch mapping="logical" aggregate="true">
          <entity name="opportunity">
            <attribute groupby="true" alias="statecode" name="statecode" />
            <attribute alias="SumTotal" name="estimatedvalue" aggregate="sum" />
            <attribute alias="Average" name="estimatedvalue" aggregate="avg" />
            <attribute alias="CountOfOpp" name="estimatedvalue" aggregate="count" />
            <attribute alias="NextCloseDate" name="estimatedclosedate" aggregate="min" />
            <attribute alias="LastCreated" name="createdon" aggregate="max" />
          </entity>
        </fetch>
      </fetchcollection>
      <categorycollection>
        <category alias="statecode">
          <measurecollection>
            <measure alias="SumTotal" />
          </measurecollection>
          <measurecollection>
            <measure alias="Average" />
          </measurecollection>
          <measurecollection>
            <measure alias="CountOfOpp" />
          </measurecollection>
          <measurecollection>
            <measure alias="NextCloseDate" />
          </measurecollection>
          <measurecollection>
            <measure alias="LastCreated" />
          </measurecollection>
        </category>
      </categorycollection>
    </datadefinition>
  </datadescription>
  <presentationdescription>
    <Chart Palette="None" PaletteCustomColors="91,151,213; 237,125,49; 160,116,166; 255,192,0; 68,114,196; 112,173,71; 37,94,145; 158,72,14; 117,55,125; 153,115,0; 38,68,120; 67,104,43; 124,175,221; 241,151,90; 186,144,192; 255,205,51; 105,142,208; 140,193,104; 50,125,194; 210,96,18; 150,83,159; 204,154,0; 51,90,161; 90,138,57;">
      <Series>
        <Series ChartType="Bar" Color="Transparent" IsValueShownAsLabel="True" Font="{0}, 30px" LabelFormat="Sum of Opportunities #,#,k" LabelForeColor="59, 59, 59" CustomProperties="BarLabelStyle=Left, PixelPointWidth=150, MaxPixelPointWidth=5000"></Series>
        <Series ChartType="Bar" Color="Transparent" IsValueShownAsLabel="True" Font="{0}, 28px" LabelFormat="Average amount #,#,k" LabelForeColor="59, 59, 59" CustomProperties="BarLabelStyle=Left, PixelPointWidth=150, MaxPixelPointWidth=5000"></Series>
        <Series ChartType="Bar" Color="Transparent" IsValueShownAsLabel="True" Font="{0}, 26px" LabelFormat="Number of Opportunities #,#" LabelForeColor="59, 59, 59" CustomProperties="BarLabelStyle=Left, PixelPointWidth=140, MaxPixelPointWidth=4000"></Series>
        <Series ChartType="Bar" Color="Transparent" IsValueShownAsLabel="False" Font="{0}, 20px" Label=" Next Est. Close Date #VALY" LabelForeColor="59, 59, 59" CustomProperties="BarLabelStyle=Left, PixelPointWidth=140, MaxPixelPointWidth=4000"></Series>
        <Series ChartType="Bar" Color="Transparent" IsValueShownAsLabel="False" Font="{0}, 15px" Label="  Latest Opportunity Created #VALY" LabelForeColor="59, 59, 59" CustomProperties="BarLabelStyle=Left, PixelPointWidth=140, MaxPixelPointWidth=4000"></Series>
      </Series>
      <ChartAreas>
        <ChartArea BorderColor="White" BorderDashStyle="Solid">
          <AxisY Enabled="False" Maximum="1" LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">
            <MajorGrid LineColor="239, 242, 246" />
            <MajorTickMark LineColor="165, 172, 181" />
            <LabelStyle Font="{0}, 10.5px" ForeColor="59, 59, 59" />
          </AxisY>
          <AxisX Enabled="False" LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">
            <MajorTickMark LineColor="165, 172, 181" />
            <MajorGrid LineColor="Transparent" />
            <LabelStyle Font="{0}, 10.5px" ForeColor="59, 59, 59" />
          </AxisX>
        </ChartArea>
      </ChartAreas>
    </Chart>
  </presentationdescription>
  <isdefault>false</isdefault>
</visualization>

Thanks for reading!

39 thoughts on “Add Key Figures to Dashboards in MS Dynamics CRM

  1. Hey, I have an issue, running crm online 2013. The bar grid is not going away. Everything else is working and I literally have an identical xml chart file. I even went as far as copying and pasting. Still, the grid lines are there…including the information stuff that we want to see. Is there some special setting i’m missing if using the identical xml chart file that you have provided?

      • Yes, I literally changed everything to transparent and that did not work, then I changed everything to white and it removed a few more lines. it looks like the highlighted section you mentioned needed to be removed shouldn’t. Instead you should white the legend and secondary Y axis…i’m going to test a bit more see if I can get it looking like yours without removing those sections.

  2. Really, really useful concept. My use case is slightly different. My opportunities have 3 flags: 1) Include in Forecast, 2) At Risk and 3) Upside. I want to show a summary block like this:
    Opportunities This Month
    Total: $1.68M
    In Forecast: $0.98M
    At Risk: $0.55M
    Upside: $0.37M
    Won to Date: $0.48M

    QUESTION: Can you suggest how I might get the sum of ‘Est. Revenue’ filtered by each of the binary fields?

  3. Any idea why occasionally one label won’t sit flush with the edge? I used your walkthrough and setup a similar chart a couple of months ago. Sometimes when the data changes, some of the text will disappear or appear in random locations. All of my CustomProperties contain BarLabelStyle=Left, but could there be something pushing the text out from the left side?

    • If the value of the bar is so low that CRM determines it might be better to put the label outside of the bar, rather than inside, then the alignment is going to be a little off. I tried to accommodate this in the chart by changing the Y axis to have a maximum of 1, and thereby forcing all the items to have the same alignment. But I guess there can be situations where that won’t work. Some trial and error around that might help. Giving the chart more space in the dashboard could work too.

  4. For the label, if I want to have a numeric text, such as 2015 Revenue, it shows the text as Revenue, and adds the 2015 to the actual Revenue Number. Do you know how I can get around that?

  5. This is awesome. Do you have an idea how we can adjust this to support Multilanguage?
    Usually, we add all language specific content into a global optionset and then link the language content with something like “o:prefix_chart_translations,693100015” into the name field.

    Thanks!

  6. Great info! Question, what’s the best way to manipulate the Sum of Opportunities (and Average Amount) to be able to accommodate millions or thousands?

  7. Love your work and incorporated this particular chart into my 2015 go live. I did notice that the chart doesn’t display well on the MSCRM App. Have you seen that and have any suggestions on resolving it?

  8. This is great, thank-you! I don’t suppose you might be able to suggest why my numbers are rounding though? I am trying to change the format, but it still rounds. For example, I’m averaging scores that have been submitted – this is showing the average as 7. If I export the data into excel and average the score is 7.4. If I try to change the number format in the chart, I just get something like 7.00.

  9. I’m not sure if you covered this and I’m just missing it, but I am trying to make a stacked column chart of the sales pipeline week by week. I’ve seen a screenshot of exactly what I want, but have not been able to find any directions or help to make it exactly like that. Any help would be much appreciated because all of my trials have ended in error. Thanks!

  10. This worked great! But now that I have Status on my horizontal axis, if I view this chart along a view that displays both open and closed opportunities I see two sets of data (one for each status). My questions is how would I add headers to each set of data so we can see which one applies to each status? Thanks!

    • Hi Andra
      You could just add the axis back in. That would show the different statuses, but it might look a little clunky. You could also create a chart for the different statuses allowing you to adjust what the most relevant KPIs are.

  11. Hi, I hava a question. Regarding, PowerBI Integration with CRM On-Premise 2016 we have learned that reports need to published as public in order to be displayed in CRM . I wanted to know if there is way to display published Power Bi Dashboard in CRM On Premise (IFD) -2016, in secured way without begin published as public ?

  12. I’m running Dynamics 365 and I followed your instructions pretty much exactly.
    I have the result of only seeing the ‘Number of Opportunities’ series with the others not showing, the labels, nor the values. The ‘Number of Opportunities’ series looks like it should.
    I modified the customproperties to try and see if that was the problem, but it didn’t change anything.
    FYI, I have smartlabelstyle=”true” with each series, which inst present in your code. Tried changing that to false and nothing changed.

    • Hi Rory. The only time I see the labels and values disappear, is if the chart is being rendered on an area significantly smaller area than what is needed. You can try to give the chart a lot of space just to see if it helps. Otherwise, there is no harm in removing the smartlabelstyle property, so that’s worth of shot. Or, it is possible that you may have put the maximum axis property on the X axis instead of the Y axis. Good luck.

  13. On the Est Close Date I see #VALY at the end instead of the date. There is a close date, any reason this would be happening? I’m in Dynamics 365.

  14. Fixed it. I can’t read and missed where you said the Dates needed IsValueShownAsLabel set to false!

  15. So, maybe I’m making my chart to complicated, but I’m getting duplicates when pairing it with view. Thoughts? So, duplicate Words on top of each other.

  16. I used this Key feature example for plotting account AR summary stored in custom fields in the account Entity. I am running into a similar issue where some of my labels are getting pushed and i think its because there are Negative values.

    I would like to know how can I represent both positive and negative values with the Key Feature chart. Right now it looks like the Negative values are crossing on the Y Axis. Is it possible to use this Key Feature chart with possible and negative values?

    • Hi I just wanted to give an update on the issues I was challenged with when trying to use a Key Feature chart to display MTD sales GP sales and AR Summary. All these fields stated are custom fields added to the account entity which gets updated via Scribe. Scribe capturing changes from our DATA Warehouse which get its data from our ERP system then replicated into CRM.

      My issue was that there were negative values which would push all positive vales out of the chart area when present. Since stake holders did not care to see negative values on their dashboard I added a Minimum=”0″ property to AxisY. This allowed for only positive values to display only, should negative values be present it would not render in the chart. See below for example.

      The second issue was with the label formats. My labels consisted of integers that display AR ex: LabelFormat=”Sum of 31-60 $#,#.##;(Sum of 31-60 $###,0.00)”

      • Thanks so much for all the insights. I have a feeling wordpress might have cut your comment a little short. Feel free to email me the whole thing to ulrik (at) elev8solutions dot com and I’ll be happy to amend the post with your tips.

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