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!

Advertisements

, , , , ,

  1. #1 by J Bachu on March 31, 2015 - 1:13 pm

    Awesome. Many thanks for sharing this.

  2. #2 by Patrick on May 15, 2015 - 11:44 am

    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?

    • #3 by CRM Chart Guy on May 15, 2015 - 11:57 am

      Sounds like this could be related to 2013 specifically. I’d suggest you go through the xml and change all the colors to “Transparent” except for the labels.

      • #4 by Patrick on May 18, 2015 - 8:03 am

        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.

  3. #5 by MezMarianne on May 22, 2015 - 2:12 am

    Awesome. It works.

  4. #6 by Nicholas on June 18, 2015 - 8:49 am

    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?

  5. #8 by Jeff on August 5, 2015 - 5:03 pm

    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?

    • #9 by CRM Chart Guy on August 5, 2015 - 5:44 pm

      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.

  6. #10 by Avneet on September 17, 2015 - 4:27 pm

    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?

    • #11 by Avneet on September 18, 2015 - 2:46 pm

      Nevermind, I just had to put single quotations around the Label and it worked!

  7. #12 by Bernd on March 20, 2016 - 1:25 pm

    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!

  8. #13 by Allison on March 29, 2016 - 2:45 pm

    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?

  9. #14 by Kevin on May 10, 2016 - 12:29 pm

    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?

  10. #15 by Roy on September 21, 2016 - 3:09 am

    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.

    • #16 by Roy on September 21, 2016 - 4:04 am

      My fault… whole number field! Set on a decimal field then this works.

  11. #17 by Jason on October 26, 2016 - 3:32 am

    I’ve followed your guide and it works perfectly. Would it be possible to utilize this for an Option Set?

  12. #18 by Brenna Rudisill on November 3, 2016 - 7:40 am

    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!

    • #19 by CRM Chart Guy on November 3, 2016 - 8:34 am

      Hi Brenna. What would the week by week be based on? Est. Close date?

  13. #20 by Andra L on November 22, 2016 - 10:36 am

    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!

    • #21 by CRM Chart Guy on November 23, 2016 - 7:55 am

      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.

  14. #22 by Enthusiast on December 23, 2016 - 2:47 am

    Also, can we make these key figures as hyperlink so that clicking on them would take us to the respective view

  15. #23 by Gary on January 11, 2017 - 10:10 am

    Thanks for the really useful KPI charts – is it possible to change the date format on the KPI to dd/mm/yyyy format?

  1. Hosk’s Top CRM Articles of the week – 27th March – Hosk's Dynamic CRM Blog
  2. Hosk’s Top CRM Articles of the week – 27th March - Microsoft Dynamics CRM Community
  3. Custom Date Formats in Charts in MS Dynamics CRM | crm chart guy

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

%d bloggers like this: