Add Key Figures on Opportunities to Sales 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.

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.

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.

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

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.

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.

Updates 12/18/17:

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.

<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!