Crowdsourcing the ultimate Sales Dashboard for Dynamics 365/CRM

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.

Crowdsourcing?

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.

Assumptions

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

Main sales dashboard with the most important KPIs for Dynamics 365/CRM

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.

Competitor Strength

Scroll down on the dashboard and you get to the Competitor Strength chart.

Competitor Strength for Open Opportunities in Dynamics 365/CRM

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

Sales Pipeline Analysis in six charts in Dynamics 365/CRM

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.

Download

As always, samples are provided for fun and training purposes only. Test properly prior to using in a production environment.

Sales Dashboard by CRM Chart Guy Version 0.0.0.1

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

, , , , , , , ,

8 Comments

Custom Date Formats in Charts in MS Dynamics CRM

I get a lot of questions on how to change the date format on the labels. A common one is changing the week grouping from “Week 35 of 2016” to “Week of Aug 29.”

Coming from Europe, I think week numbers are an awesome way of keeping track of the weeks, but like the metric system it just never caught on here in the US, regardless of how much sense it makes.

So even though week numbers are the better option, like column/bar charts over funnel charts, let’s have a look at how we can work with the date formatting on labels.

First up, MS Dynamics CRM does not support changing the date labels on an axis. If you change the xml for the labels, the chart will either just show you the standard date formatting, or the chart will show a render error, even though the xml is correct. For this reason, I’ve previously stated that this requirement cannot be met with charts in Dynamics CRM.

However, unlike the axis, you can actually format the dates on the label for the series. So to address the question on changing the week numbers on the axis label, I will try and remove those labels and use a series label instead to show the date in the desired format.

So the end result will look like this:

MS Dynamics CRM Chart with modified xml to display custom date formatting

Rather than this standard labeling:

The system week grouping in MS Dynamics CRM chart created in the chart editor.

It is not perfect, so if you can live with the drawbacks I mention throughout this post, it will probably work for you.

Build the Base Chart

Let’s create the foundation. In the chart editor, we will need two series, and of course, the week we are grouping by.

Chart Editor in MS Dynamics CRM. Two series and group week to prepare for adding custom date formatting in the chart xml.

We need two series. One to show the actual count of opportunities, and one to hold the label for the custom date formatting.

Export the Chart XML

Open the chart xml in Notepad ++ and get ready to make some changes.

In the CRM chart xml below, I have updated all the auto generated aliases to something more meaningful in this context.

  <datadescription>
    <datadefinition>
      <fetchcollection>
        <fetch mapping="logical" aggregate="true">
          <entity name="opportunity">
            <attribute groupby="true" alias="groupbyweek" name="estimatedclosedate" dategrouping="week" />
            <attribute alias="dateformat" name="estimatedclosedate" aggregate="min" />
            <attribute alias="countofopportunities" name="estimatedclosedate" aggregate="count" />
          </entity>
        </fetch>
      </fetchcollection>
      <categorycollection>
        <category alias="groupbyweek">
          <measurecollection>
            <measure alias="dateformat" />
          </measurecollection>
          <measurecollection>
            <measure alias="countofopportunities" />
          </measurecollection>
        </category>
      </categorycollection>
    </datadefinition>
  </datadescription>

In the xml, my changes and additions are in green.

Besides updating the aliases, I made one significant change in this section. In the attribute for the “dateformat,” I changed the aggregate to “min” for minimum.

Also note that I have the “dateformat” before the “countofopportunities” in the category collection. This way the count, which is the number we want, is drawn last and on top.

Before you import, scroll down on your chart xml and make this VERY IMPORTANT change.

  <AxisY 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" Format="CRM Chart Guy" ForeColor="59, 59, 59" />
  </AxisY>

In LabelStyle for the first Y axis, add Format=”CRM Chart Guy” as above.

If you do not add this format you will get a rendering error. This seems to be a bug in how Dynamics CRM renders charts, but this is the way to get around it.

Now you can import the chart and make sure everything is still working.

Chart xml modified to display dates instead of values in MS Dynamics CRM

So far, so good. You should now be able to see that one of the bars contains the date, while the other still has the count.

As you have probably figured out by now, the date that we are going to get may not be the Monday of that given week. It will be the earliest date of that week, where data was present. So use with care as it could be misleading if your first date happens to be the Friday of a given week. However, for daily events, it should be fairly accurate, or at least close enough.

Edit the Series

Go back to the chart xml and locate the two series. We need to make the following updates:

  • Remove the legend items – not needed in this case
  • Draw the bars on top of each other instead of side by side
  • Make the date series bar invisible/transparent – we only want the label
  • Give the bar displaying the count a brighter color so it is easier to read text on it
  • Change the date label format
  • Move the date label so it is left aligned on the chart
  <Series>
	<Series ChartType="Bar" IsValueShownAsLabel="True" Color="Transparent" IsVisibleInLegend="False" LabelFormat="'Week of' MMM dd" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="BarLabelStyle=Left, DrawSideBySide=False, PointWidth=0.75, MaxPixelPointWidth=40">
	  <SmartLabelStyle Enabled="True" />
	</Series>
	<Series ChartType="Bar" IsValueShownAsLabel="True" Color="LightBlue" IsVisibleInLegend="False" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40" YAxisType="Secondary">
	  <SmartLabelStyle Enabled="True" />
	</Series>
  </Series>

Here are the additions to the chart xml in the same order as above:

  • IsVisibleInLegend=”False” to both series
  • DrawSideBySide=False to one of the series (this custom property will automatically apply to all the bar series)
  • Color=”Transparent” to the dateformat series
  • Color=”LightBlue” to the countofopportunities series (more on colors in CRM Charts here)
  • LabelFormat=”‘Week of’ MMM dd” on the dateformat series
  • BarLabelStyle=Left to the dateformat series custom properties

Import the xml back in to MS Dynamics CRM

XML for the chart modified custom date format and other cosmetic changes.

Custom Date Format

The LabelFormat=”‘Week of’ MMM dd” is what gave the label the “Week of Aug 29” label, or whichever date was the lowest for that group.

Here are the most common options you can use:

  • “d” = The day of the month, from 1 through 31
  • “dd” = The day of the month, from 01 through 31
  • “ddd” = Abbreviated name of the day; Mon, Tue, Wed etc.
  • “dddd” = Full name of the day
  • “M” = The month, from 1 through 12
  • “MM” = The month, from 01 through 12
  • “MMM” = Abbreviated name of month, i.e. Jan, Feb, Mar etc.
  • “MMMM” = Full name of month
  • “yy” = The year, from 00 to 99
  • “yyyy” = The year in four digits

Pay attention to the case of the letters. “m” is for minutes, while “M” is for months.

The full set of options can be found on MSDN here.

As in this post, you can combine the options like “dd MMM yy” for “31 Aug 16”.

Also, when adding leading text like ‘Week of’, put it in single quotes to avoid some letters being displayed as a value instead.

Chart Area and Axes Modifications

Ok, let’s fix those axes and the labels that just say CRM Chart Guy.

This is actually really simple. I just deactivated them by adding  Enabled=”False” to both AxisY and AxisX.

<ChartArea BorderColor="165, 172, 181" BorderDashStyle="Solid" BackColor="10, 59, 59, 59">
  <AxisY  Enabled="False" 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" Format="CRM Chart Guy" 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>
  <AxisY2 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" />
  </AxisY2>
</ChartArea>

Fun fact: I think this is my first post where I left the secondary Y axis alone🙂

Now, I did a little more than that. Right before the AxisY, I made a few modifications to the ChartArea.

  • Changed BorderColor from “White” to “165,172, 181” (I just copied the gray color from the major tick mark)
  • Added BackColor=”10, 59, 59, 59″ to give the background a little more substance. (Copied the label ForeColor and added a lot of transparency)

Import and your result should look a little like this.

Axes and other items on the chart XML has been cleaned up for the custom date chart in MS Dynamics CRM.

Notice there may be some conflicts between the date label and the actual value, when the value is low. That can be alleviated by either simply removing the value, it will still appear in the hover text, or giving the chart some more horizontal space if it is on dashboard.

The context where I usually use custom date formatting is on Key Figures Charts.

Key Figures Chart in MS Dynamics CRM displaying to the point relevant data where it is needed.

Using the custom date formatting, I can write the dates in whatever format desired and can even leave out irrelevant pieces.

Add Some Corporate Branding

Finally, add this section right before the </Chart> closing tag.

<Annotations>
	<TextAnnotation X="50" Y="93" Text="Elev8 Solutions" TextStyle="Default" Font="Verdana, 30px" ForeColor="LightGray" ToolTip="Elev8 Solutions - Microsoft Dynamics CRM Partner - www.elev8solutions.com"/>
</Annotations>	  	  

Chart with an annotation added in the chart xml for custom corporate branding.

Looks great! You can read more about Elev8 Solutions and our innovative immersive approach to CRM projects here. Which also happens to be where I work.

As always, thanks for reading and please sign up for my newsletter on this page and follow me on Twitter

, , ,

3 Comments

Charts on Forms, or User/Account Specific Dashboards

Charts don’t just go on dashboards, they can go on the forms too.

It’s really just a sub-grid that displays a chart instead, but as this seems to be a heavily underutilized feature in MS Dynamics CRM, maybe I can bring some more focus on it with this post.

The primary example where form charts can come in handy is for the “Sales Activity Dashboard” that each sales person can look at and see their current statistics, opportunities, tasks etc.

Sales Activity Dashboard for User in MS Dynamics CRM

A common question from a sales manager is “That’s great, but I want to see all that same data, but be able to flip through the members of my sales team and see the data that they see”.

Simple enough as all the views can be filtered to a named user rather than “Equals Current User” which is used throughout the example above. Obviously very cumbersome since you would need a saved view for each sales person and you would have to change the views on each chart individually, or create a dashboard for each person on your sales team, etc.

MS Dynamics CRM dashboards do not have good ways of filtering an entire dashboard to a specific user. CRM2016 is getting a little closer, but the filter can only be applied to option sets and only in the new engagement hub.

However, the combination of charts you are looking at does not necessarily need to be on a dashboard.

The example above is the “Sales Activity Dashboard” that comes with Dynamics CRM. There are five charts on it. In this blog, I will go through the steps needed to add all these chart to the user form so the same charts are displayed, but specific to the user being viewed. Then a sales manager can flip through the users, and see the data they see on their “Sales Activity Dashboard.”

Like this:

Sales Activity Dashboard on User form, specific to the USer in MS Dynamics CRM

Same charts as the dashboard, but all user specific and you can circle through the list of users with the arrows in the upper right hand corner.

These record specific charts are a very powerful way of bringing insights onto a specific record. In this example it is a sales person, but we could also be looking at service and case KPIs for a service user. From an account record you can add charts with sales statistics, activity statistics, case and service KPIs, all specific to the account. It would give the account manager a very clear view on the current state of the account.

Putting chart on MS Dynamics CRM forms

Go to your solution editor and open the User form and click “Save As.”

Create New User form for Sales Dashboard in MS Dynamics CRM

Name the new form “Sales Dashboard.”

 

Open it and insert a new three-column tab.

Insert three-columns Tab on user form in MS Dynamics CRM

I chose three columns evenly distributed, because that is the same way the dashboard was. Open the properties on the new tab and rename it to “Sales Dashboard.”

Now it is time to insert some charts.

Select the first section in the new tab and insert a Sub-Grid.

Insert SubGrid in section on user form

In the Name and Label fields, just write the name of the chart. Make sure the Display Label on the Form box is unchecked. Neither of these values will be shown on the form, or need to be as they are already included in the chart.

Subgrid and chart settings to display chart on form in MS Dynamics CRM

In the Data Source section select

  • Records = Only Related Records
  • Entity = Opportunities (Owning User)
  • Default View = Open Opportunities

This is what pulls the right opportunities to build the chart on.

In the entity setting, make sure you select the right relationship to the opportunity, which is the owning user in this case.

For default view, use a similar, but not the same view as the dashboard. In this case, the dashboard uses the view “My Open Opportunities”. To get the expected result on the user form, use the view “Open Opportunities”. The “Only Related Records” is already filtering to only include opportunities for this specific user.

Scroll down and find the chart settings.

Subgrid chart settings on form

Select the same chart as the dashboard for this section, in this case “Sales Pipeline” and check “Show Chart Only.”

Leave the view selector off.

Note: You can choose to include the view selector and check the “Display Chart Selection”. This will enable you to switch views and chart in the same section on the dashboard. This can be very powerful as well, but in this case I am trying to replicate the standard dashboard as much as possible, and those features were not enabled there. I would highly recommend playing with those features as they can significantly improve your charts and dashboards.

Scroll back up on the properties and click the “Formatting” tab.

Formatting Tab 30 rows on subgrid settings

Leave the layout option at “One Column”

In the row layout, add the number of rows you want the chart to take up. This is a place where you need to be generous. 30 rows for the funnel chart will make it about the same size as the funnel chart on the main sales dashboard which we are replicating.

Repeat these steps for the other four charts that are on the sales dashboard.

Save the form and publish it.

Save and Publish form

Go to a sales person’s user record and  switch the form to “Sales Dashboard.”

Switch form on user to display sales dashboard

Enjoy the same sales dashboard, but specific to the user you are looking at.

Full Sales Activity Dashboard on User form in MS Dynamics CRM

Wow, a post with no XML editing!!!

Thanks for reading. Please sign up for my newsletter and follow me on Twitter

,

12 Comments

Charting on Activity Parties in MS Dynamics CRM

The Activity Party entity surfaced a little in the MS CRM blogosphere late last year.

First Jukkan wrote this excellent piece “Your Interaction Network in Dynamics CRM”

And then Joel Lindstrom and George Doubinski talked a little about the challenges of reporting on the activity parties in episode 14 of the CRM Audio podcast called “SSRS is Dead to George”.

While you can chart on activities with the chart editor and Excel, there are two questions you cannot answer without some xml customizations. I will use appointments as an example throughout this blog post although you can apply this to any of the activity types.

The two questions are:

1. How many people are included in each appointment?
Count of Attendees per Appointment - MS Dynamics CRM

2. How many appointments are CRM users included in?
Count of Appointments per User - MS Dynamics CRM Chart

This information is stored within the activity party table, which keeps track of all the contacts, users etc. that you add to the required and optional fields on the appointment form. It also keeps track of the owner and organizer.

A user can create their own appointments, but they can also be required attendees in other users’ appointments. That means you can have two or more users involved in a sales meeting, but in a normal chart, only the owner of the appointment would get “points” for the sales activity. If you are having regular sales meetings with multiple participants or if you use appointments to keep track of who attended what event, it can be useful to know how many people were actually involved in each of the appointments.

101 on Activity Parties

While I do urge you to read Jukkan’s blog on activity parties, here’s an ultra-short 101 in case you didn’t.

The Activity Party works as a manual N:N relationship between any activity and the people participating and what their participation role is.

If user John Smith is the owner of an appointment, and a required attendee, he will have two activity party records between his one user record and the one appointment record.

The official documentation on activity parties can be found here. ActivityParty entity details on MSDN

How many people per appointment chart

We need to create and download two pieces of xml before we open up Notepad ++ and start our chart xml editing.

1. A base chart we can build on.

Attendees Base Chart in MS CRM

2. A FetchXML from Advanced Find which includes details on the attendees we want to include.

Advanced Find link to activity party in MS Dynamics CRM for chart customization

In Advanced Find, select the participation types you want to include.

Participation Types selection in Advanced Find

Download the FetchXML.

Download Fetchxml button in Advanced Find

Open the FetchXML and find the <link-entity> section. Highlighted below.

Fetch downloaded from Advanced Find in MS Dynamics CRM for chart customization

Export and open the chart xml from the base chart and get ready for some editing.

In the xml for the base chart, locate the line with the aggregate in the fetchcollection. As usual I have renamed the aliases to something sensible.

Original fetch from the base chart with aggregate line highlighted

Now we need to edit the fetch, so that the <link-entity> section downloaded from Advanced Find goes inside the fetchcollection and that the aggregate ends up inside that section.

MS CRM Chart fetchcollection modified with link to Activity Party entity.

Note I did some other changes to the aggregate too.

  • Replaced name=”statecode” with name=”partyid” which refers to the attendee record. The statecode came from the field selected in the chart editor.
  • Added distinct=”true”. We only want to count people once.
  • Aggregate was changed to countcolumn so we only include records with a value.

Import the fetch and have a look.

Count of Attendees by Appointment - Final chart after customizing the chart xml in MS Dynamics CRM

 

How many appointments per user chart

Not all sales people are lone wolves. Some are pack hunters and go to meetings together. However, in standard CRM charts, only the owner of the appointment will get “points” for attending. We need to make sure all users are accounted for, and of course make sure that each user is only counted once. No double dipping by putting yourself in both the owner and the attendees’ fields.

The process is very similar as above, but we will need to create an extra <link-entity> from the activity party to the user entity so we can group by it.

Again, build this in Advanced Find and download the FetchXML. Include a lookup to the users to ensure only users are counted.

Advanced Find - by User - additional link to User entity

Modify the fetchcollection in your chart xml to include the added <link-entity> to the users.

Appointments per User fetchxml modified. Dynamics CRM chart customization

Other changes:

  • Aggregate is activityid rather than partyid since we are now counting appointments, not participants
  • Groupby was moved to the new link-entity section and is now on the user
  • Aliases updated to fit this scenario better

Import the chart and you’ll see a count of appointments per user.

Count of Appointments per User chart in Dynamics CRM - Chart customization XML

 

Bonus Chart – Count by any Party

Since any party is worth counting, we can skip the additional link-entity and group directly on the partyid. By doing this you get a mix of record types involved in the appointments so you can compare participation between Accounts, Leads, Contacts and Users.

Bonus chart for coounting the number of appointments for all party types. Fetchxml modified for import to a Dynamics CRM Chart.

Here is the chart after import.

Bonus Chart - Count of Appointments by all party types. MS Dynamics CRM Chart customization.

This is clearly candidate for a Top X chart. Read more about those here.

Thanks for reading. Please sign up for my newsletter and follow me on Twitter

, , ,

6 Comments

Bubble Charts in Dynamics CRM

I usually stick to the standard chart types in my blog posts, even though Dynamics CRM supports many other types such as radar, polar, error bar and bubble charts. So it is time to venture out a little bit.

In this post I will work with the bubble chart and show how to build one using only Dynamics CRM charts and some chart xml editing.

Bubbles Chart in MS Dynamics CRM made only with chart xml customizations

Here is the bubble chart I will build.

A couple of details on the setup. The X axis along the bottom has the Est. Close Dates, with the nearest close date on the left. The Y axis goes from 0 – 100 based on the Probability of the opportunity. In an ideal scenario, each opportunity, or bubble, will move from the lower right quadrant, to the upper left quadrant of the chart. As the Est. Close Date comes nearer, the Probability should also increase. This also makes it easy to identify opportunities that fall outside the intended path, such as having a very low probability, but set to close very soon, or a very high probability on an opportunity that is a year out.

Before you start making bubbles, we need to have a look at how Dynamics CRM renders the chart.

  • X axis is defined by the GroupBy and Category in the xml.
  • Y axis is the first Y value defined.
  • Bubble size is the second Y value defined.

Bubble Chart with Y value descriptions and x axis description - MS Dynamics CRM

Bubble Charts initially look great, but they do have some limitations in Dynamics CRM. Hopefully I will provide enough information for you to decide whether or not bubble charts work for your scenario. I will also have some suggestions on how to overcome some of the limitations.

This post also details how to create charts that use multiple Y values, which a number of the custom chart types do.

Chart Editor – Create the base chart

Let us get started and create a base chart on the opportunity entity we can use to customize the chart xml.

Chart Editor - intial setting both Y values - MS Dynamics CRM

Add the two series, Y Values, we need.

  1. Probability (height on Y axis)
  2. Est. Revenue (bubble size)

Add Est. Close Date as the Category to get that on the X axis.

Now export the chart xml and let’s have a look.

Create a measure with two Y values in the chart XML

In the screenshots below, I have changed the aliases to something that reads easier than the auto-generated ones.

The important part is to identify the measurecollection. In the chart xml we exported there are two measurecollections, one for each of the two series we added.

FetchCollection and CateogryCollection with new aliases - Measures highlighted

Bubble charts need one series with two Y values. Not two series with one Y value each.
We need to modify the measurecollection so we instead have one collection with two measures, i.e. one series with two Y values.

Like this
MeasureCollection updated to fit a Bubble Chart with two Y Values

Now that we only have one measurecollection, we also need to remove one of the two series that is in the xml. Remove everything related to the secondary Y axis at the same time as well.

Remove one series and the AxisY2 from the chart xml - bubble chart

Finally, change the chart type in the remaining series to “Bubble”, not depicted in the screenshot above, and import the chart xml into Dynamics CRM.

Initial Bubbles chart with the default markerstyle which is squares

Squares!! So far, so good.

Style the Bubbles!

Squares may not be exactly what we were looking for. The bubble, or marker, is defined the MarkerStyle options which goes in the series. See all the MarkerStyle options on MSDN.

“Circle” is the obvious choice for bubble charts.

We also need to use some of the custom attributes for bubble charts. Here is an overview of bubble chart options on MSDN.

Here are the changes I made in the series of the chart xml. Full XML sample is available at the end of the blog post.

Series xml - line breaks inserted - update of series for Bubble Chart in MS Dynamics CRM

Some additional line breaks have been added to fit the blog width better.

  • IsVisibleInLegend=”False”
  • MarkerStyle=”Circle”
  • IsValueShownAsLabel=”True”
  • LabelFormat=”$#,#,k”
  • Font=”Arial, 16px”
  • CustomProperties
    • BubbleUseSizeForLabel=True
    • LabelStyle=Bottom

I removed the legend because it is not needed for this chart and would be a waste of space.

IsValueShownAsLabel, LabelFormat, Font, LabelStyle and BubbleUseSizeForLabel are all related to creating the label with value of the Est. Revenue below the bubble in thousands. The last piece, BubbleUseSizeForLabel, is for displaying the second Y value as the label. Otherwise it would display the first Y value, which is the Probability. See the image with the squares for an example. More on label formatting in charts here.

Import the updated chart xml and your result should look like this.

Bubbles Chart cleaned up chart xml

Drawbacks

Now we have a bubble chart with nice labels. All the bubbles have the same color because they are all in the same series. That leads me to some areas that you need to be careful with when creating bubble charts.

Notable drawbacks about the bubble chart:

  • Cannot identify individual opportunities
  • Cannot do drill-down
  • Opportunities with the same Est. Close Date will be grouped in to one bubble!

These could be some pretty serious drawbacks, but I will show you some tricks to get around them. The solutions are definitely not foolproof and should be used with care. It all really boils down to your specific scenario. However, these tricks do work best with a limited number of opportunities.

Add Bubbly Colors

First step to make this a little better is to give each bubble its own color. We can do this by adding a palette to the series itself.

Add Palette to Series SemiTransparent

I use SemiTransparent as it has some nice bubbly colors (pun certainly intended).

Adding a Palette to a series will give each datapoint its own color. It can be a useful to distinguish columns etc. in charts where only one color is used. You can only use one of the named palettes. That means you cannot create your own color scheme for this. However, there is a number of named palettes to choose from. Click here for a full list of palettes with samples.

Import the xml with the palette and you should get something like this.

Bubbles Chart in MS Dynamics CRM with palette in series

Note that I also cleaned up the axes titles and intervals a little at the same time. Full chart xml sample available at the bottom of the post.

Create a “Legend Chart”

So far so good, but that does not solve the issues that I mentioned earlier. I can’t tell which bubble belongs to which opportunity. To solve this, I will make another chart, with the same color coding displaying the names of the opportunities. Basically, I am creating a chart which will serve as a legend.

This “legend chart” is build from a stacked bar chart. I will not cover the layout customization as they are detailed this post: Design Options for Bar Charts

Legend Chart Color Code chart to assist bubble chart

The most important part of this chart, is that the order has to match the bubble chart perfectly. Otherwise, the colors will not match. The bubble chart is grouped by, and therefore also sorted by date on the “Est. Close Date” field.

That means you need to use the Est. Close Date for the aggregation and then manually insert an order by that alias.

This is my starting point in the chart designer to create the base chart.

Color Code Chart Designer - create the base chart

Here is the fetchcollection with changes highlighted. Full sample is available at the bottom of this post.

Color Code Data Definition and ensuring order is correct in the chart xml

The order has been added manually and the aggregate has been changed to “max”, which allows us to group by the opportunity and not the date. Of course, also insert the same SemiTransparent palette in the series.

Bubble Chart and “Legend Chart” together

Bubble Chart and Legend Color Code chart with match on first and last color

Here is the bubble chart and the “legend chart” in a dashboard next to each other. The opportunity on top of the “legend chart” matches the color of the Est. Close Date that is nearest. You can also use the combination of the two charts to check that you are not impacted by some of the bubble chart limitations. If the bubble chart had grouped some opportunities into one bubble, the first and last colors of the bubble chart and “legend chart” would not match.

Full dasboard with Bubble Chart and Legend Chart in Microsoft Dynamics CRM made using only chart xml customizations!

Here is the final version in a dashboard.

Thanks for reading. Please sign up for my newsletter and follow me on Twitter

Bubble Chart XML Sample

As always, samples are provided for fun and training purposes only. Do not use in production without proper testing, and in this case, very carefully reviewing the mentioned drawbacks and limitations on this chart type.

<visualization>
  <visualizationid>{9E4D4D89-2084-E511-80E6-3863BB3CA578}</visualizationid>
  <name>Bubble Chart - by CRM Chart Guy</name>
  <primaryentitytypecode>opportunity</primaryentitytypecode>
  <datadescription>
    <datadefinition>
      <fetchcollection>
        <fetch mapping="logical" aggregate="true">
          <entity name="opportunity">
            <attribute groupby="true" alias="EstCloseDate" name="estimatedclosedate" dategrouping="day" />
            <attribute alias="Probability" name="closeprobability" aggregate="avg" />
            <attribute alias="EstValue" name="estimatedvalue" aggregate="sum" />
          </entity>
        </fetch>
      </fetchcollection>
      <categorycollection>
        <category alias="EstCloseDate">
          <measurecollection>
            <measure alias="Probability" />
            <measure alias="EstValue" />
          </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="Bubble" Palette="SemiTransparent" IsVisibleInLegend="False" MarkerStyle="Circle" IsValueShownAsLabel="True" LabelFormat="$#,#,k" Font="Arial, 16px" LabelForeColor="59, 59, 59" CustomProperties="BubbleUseSizeForLabel=True, LabelStyle=Bottom, PointWidth=0.75, MaxPixelPointWidth=40"></Series>
      </Series>
      <ChartAreas>
        <ChartArea BorderColor="White" BorderDashStyle="Solid">
          <AxisY LabelAutoFitMinFontSize="8" Title="Probability" Interval="20" Maximum="110" 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 LabelAutoFitMinFontSize="8" Title="Est. Close Date" 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>
      <Titles>
        <Title Alignment="TopLeft" DockingOffset="-3" Font="{0}, 13px" ForeColor="59, 59, 59"></Title>
      </Titles>
      <Annotations>
        <TextAnnotation X="50" Y="0" Text="By CRM Chart Guy #crmchartguy" TextStyle="Default" Font="Verdana, 8px" ForeColor="Gray" />
      </Annotations>
      <Legends>
        <Legend Alignment="Center" LegendStyle="Table" Docking="right" IsEquallySpacedItems="True" Font="{0}, 11px" ShadowColor="0, 0, 0, 0" ForeColor="59, 59, 59" />
      </Legends>
    </Chart>
  </presentationdescription>
  <isdefault>false</isdefault>
</visualization>

Color Code for Bubbles – “Legend Chart”

<visualization>
  <visualizationid></visualizationid>
  <name>Color Code for Bubbles - by CRM Chart Guy</name>
  <primaryentitytypecode>opportunity</primaryentitytypecode>
  <datadescription>
    <datadefinition>
      <fetchcollection>
        <fetch mapping="logical" aggregate="true">
          <entity name="opportunity">
            <attribute groupby="true" alias="_CRMAutoGen_groupby_column_Num_0" name="opportunityid" />
            <attribute alias="closedate" name="estimatedclosedate" aggregate="max" />
            <order alias="closedate" descending="false" />
          </entity>
        </fetch>
      </fetchcollection>
      <categorycollection>
        <category alias="_CRMAutoGen_groupby_column_Num_0">
          <measurecollection>
            <measure alias="closedate" />
          </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="StackedBar" IsVisibleInLegend="False" Palette="SemiTransparent" Label=" #AXISLABEL" Font="{0}, 12px" LabelForeColor="59, 59, 59" CustomProperties="BarLabelStyle=Left, PointWidth=0.75, MaxPixelPointWidth=40">
          <SmartLabelStyle Enabled="True" />
        </Series>
      </Series>
      <ChartAreas>
        <ChartArea BorderColor="White" BorderDashStyle="Solid">
          <AxisY Enabled="False" LabelAutoFitMinFontSize="8" Maximum="1" 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" IsReversed="True" 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>
      <Titles>
        <Title Alignment="TopLeft" DockingOffset="-3" Font="{0}, 13px" ForeColor="59, 59, 59"></Title>
      </Titles>
      <Legends>
        <Legend Alignment="Center" LegendStyle="Table" Docking="right" IsEquallySpacedItems="True" Font="{0}, 11px" ShadowColor="0, 0, 0, 0" ForeColor="59, 59, 59" />
      </Legends>
      <Annotations>
        <TextAnnotation X="50" Y="0" Text="By CRM Chart Guy #crmchartguy" TextStyle="Default" Font="Verdana, 8px" ForeColor="Gray" />
      </Annotations>
    </Chart>
  </presentationdescription>
  <isdefault>false</isdefault>
</visualization>

 

 

, ,

13 Comments

Funnel Charts Suck and You Shouldn’t Use Them

There are a lot wrong with funnel charts. And I am talking funnel charts in general – not just specific to Dynamics CRM.

Here are some of the issues I have with funnel charts:

  1. The size of the sections do not correspond with the values
  2. The “funnel analogy” is incorrect
  3. You can’t easily compare the sections just by eyeballing them

Funnel Chart Same Values in all segments - Dynamics CRM Chart

In this example all four sections represent $25,000 USD. However, the blue “1-Qualify” area is about four times larger than the “4-Close” area. Another pet peeve is that you often see funnels used to chart data that is not related to phases or sales stages, which is the only area where it may make sense to use them.

In any case, a simple column chart is always much better representation of the data. If you want to read more about why funnel charts suck, this is the blog post for you. Peltier Tech – Bad Graphics Funnel Charts

But regardless of how much I preach funnel chart abstinence, I know you are all just going to go out and make them anyway. So let’s explore the options and see if we can put the “fun” in funnel.

Funnel Chart Custom Properties

Similar to pie charts, funnel charts only allow one series and one Y value per point. It also cannot be combined with any other chart type. So aside from label and legend tooltip editing, the only area we can play with are the custom properties.

The Custom Properties for funnel charts are:

  • CalloutLineColor (Any color)
  • Funnel3DDrawingStyle (CircularBase , SquareBase)
  • Funnel3DRotationAngle (-10 to 10, 5)
  • FunnelInsideLabelAlignment (Center, Top, Bottom)
  • FunnelLabelStyle (Inside, Outside, OutsideInColumn, Disabled)
  • FunnelMinPointHeight (0 to 100, 0)
  • FunnelNeckHeight (0 to 100, 5)
  • FunnelNeckWidth (0 to 100, 5)
  • FunnelOutsideLabelPlacement (Right, Left)
  • FunnelPointGap (0 to 100, 0)
  • FunnelStyle (YIsHeight, YIsWidth)

Default values are in bold.

The custom properties are found in the series section of the chart xml.

XML standard custom properties <Series> <Series ShadowOffset="0" LegendToolTip="#LEGENDTEXT #VAL is #PERCENT of total #TOTAL" IsValueShownAsLabel="True" Font="{0}, 20px" LabelFormat="$#,#,k" LabelForeColor="59, 59, 59" ChartType="Funnel" CustomProperties="FunnelStyle=YIsHeight, FunnelNeck, FunnelNeck, FunnelPointGap=1, FunnelMinPoint, CallOutLineColor=Empty, FunnelLabelStyle=Inside, FunnelInsideLabelAlignment=Center, FunnelOutsideLabelPlacement=Right"> <SmartLabelStyle Enabled="True" /> </Series>

Note that the format for custom properties is a little different from the standard properties. All the custom properties may not be in the chart xml when you first export it, so add the ones you need.

You can find the official funnel chart properties on MSDN here.

FunnelStyle

There are two main styles for the funnel chart. The FunnelStyle determines if the value is displayed in the height, or in the width. The following two charts are based on the same data to visualize the difference between the two styles.

FunnelStyle=YIsHeight
YIsHeight FunnelStyle for Dynamics CRM Funnel Chart

FunnelStyle=YIsWidth
YIsWidth FunneStyle for Dynamics CRM Funnel Chart

YIsWidth does have some merit in that it addresses some of the standard funnel chart issues mentioned earlier. However, it does look odd that the first phase 1-Qualify is not drawn on the chart, although the value is present.

Funnel Neck

If your FunnelStyle choice is YIsHeight, then you can use FunnelNeckHeight or FunnelNeckWidth to adjust the funnel appearance.

FunnelNeckHeight and FunnelNeckWidth in Dynamics CRM Funnel Chart

FunnelNeckWidth=20, FunnelNeckHeight=20

With these you can adjust the rectangular area at the end of the funnel. The number you input is a percentage (0-100). The FunnelNeckWidth determines how wide the neck should be as a percentage of the chart. The FunnelNeckHeight determines how far up the funnel neck goes. I recommend increasing these percentages from the standard 5, to something more substantial like 20 as shown above. This way the last phase or two regains some of the lost surface area that I was complaining about in the beginning.

FunnelPointGap

A percentage that sets how much white space you have between the sections.

FunnelMinPointHeight

A percentage that sets the minimum height for a section. I recommend keeping this value at 0, its default, since increasing it will make the comparative sizes of the section even more obscure. An exception is if you have phases which usually contain relatively small values and you need to increase their visibility.

Label Position

The following custom properties control the label positioning.

  • FunnelLabelStyle (Inside, Outside, OutsideInColumn, Disabled)
  • FunnelInsideLabelAlignment (Center, Top, Bottom)
  • FunnelOutsideLabelPlacement (Right, Left)

If using FunnelLabelStyle=Outside, then you can use the FunnelOutsideLabelPlacement to set the label either to the left or the right of the funnel.

When placing the label inside, use the FunnelInsideLabelAlignment to align it center, top or bottom.

3D Funnel

Adding 3D to a funnel does not make it better. But if you must, then use the 3D custom properties to set it as a circular base and always set the rotation angle to zero. That way you get a decent look on the chart, but with some 3D shading on the colors. The default is 5 on the rotation angle, so make sure you change it.

  • Funnel3DDrawingStyle (CircularBase , SquareBase)
  • Funnel3DRotationAngle (-10 to 10, 5)

If not, you might end up with this monstrosity.

3D monstrosity - forget about it

Please just don’t!

Tips on Funnel Charts

Enough with the funnel chart slamming. Since it is a well-known chart type, it will be used and lots of users know exactly how to decode it when looking at it. It is even in the main default dashboard in Dynamics CRM. So here are some tips to quickly optimize a funnel chart.

Dynamics CRM Funnel Chart optimized via custom properties

Here are my suggestions:

  • Use the same color increasing in strength per phase, as the chances for winning hopefully also increases.
  • Label format to show values in thousands, increased the font and put them inside and centered.
  • Increase the neck height and width to giver better area representation for the later phases.
  • Added keywords for value, percentage and total to the legend tooltip.

LegendToolTip with values on Total, Percentage and Y value - no keyword formatting

Full sample xml of the above funnel chart is available at the end of this post.

Unfortunately, the keywords in Dynamics CRM charts do not support formatting. So, we cannot format 270000 as 270k in the tooltip. Or even put in thousand separators. If you have a minute, please go vote for that feature on CONNECT.

Here is a similar example with YIsWidth:

Funnel Example with YIsWidth Dynamics CRM Funnel Chart

I don’t think I got this one to work properly. It is still only displaying three sections even though we have four phases, so the full chart xml sample is for the YIsHeight version. The changes mentioned are highlighted in blue. As usual, samples are provided for fun only. It should be possible to copy and paste the whole thing into notepad ++, save it as an xml file and then upload it to the opportunity entity in Dynamics CRM.

Thanks for reading. Please sign up for my newsletter and/or follow me on Twitter

<visualization>
  <visualizationid></visualizationid>
  <name>Putting the Fun in Funnel</name>
  <description>Shows the sum of estimated revenue in each stage of the sales pipeline.</description>
  <primaryentitytypecode>opportunity</primaryentitytypecode>
  <datadescription>
    <datadefinition>
      <fetchcollection>
        <fetch mapping="logical" count="10" aggregate="true">
          <entity name="opportunity">
            <attribute name="estimatedvalue" aggregate="sum" alias="sum_estimatedvalue" />
            <attribute name="stepname" groupby="true" alias="stepname" />
            <order alias="stepname" descending="false" />
          </entity>
        </fetch>
      </fetchcollection>
      <categorycollection>
        <category>
          <measurecollection>
            <measure alias="sum_estimatedvalue" />
          </measurecollection>
        </category>
      </categorycollection>
    </datadefinition>
  </datadescription>
  <presentationdescription>
    <Chart Palette="None" PaletteCustomColors="90,91,151,213; 150,91,151,213; 200,91,151,213; 250,91,151,213;">
      <Series>
        <Series ShadowOffset="0" LegendToolTip="#LEGENDTEXT #VAL is #PERCENT of total #TOTAL" IsValueShownAsLabel="True" Font="{0}, 20px" LabelFormat="$#,#,k" LabelForeColor="59, 59, 59" ChartType="Funnel" 
                CustomProperties="FunnelStyle=YIsHeight, FunnelNeckHeight=20, FunnelNeckWidth=30, FunnelPointGap=1, FunnelMinPointHeight=0, FunnelLabelStyle=Inside, FunnelInsideLabelAlignment=Center">
          <SmartLabelStyle Enabled="True" />
        </Series>
      </Series>
      <ChartAreas>
        <ChartArea>
          <Area3DStyle Enable3D="false" />
        </ChartArea>
      </ChartAreas>
      <Legends>
        <Legend Alignment="Center" LegendStyle="Table" Docking="right" Font="{0}, 11px" ShadowColor="0, 0, 0, 0" ForeColor="59, 59, 59" />
      </Legends>
	  <Annotations>
		<TextAnnotation X="50" Y="0" Text="By CRM Chart Guy #crmchartguy" TextStyle="Default" Font="Verdana, 8px" ForeColor="Gray"/>
	  </Annotations>	  
      <Titles>
        <Title Alignment="TopLeft" DockingOffset="-3" Font="Segeo UI, 13px" ForeColor="0, 0, 0"></Title>
      </Titles>
    </Chart>
  </presentationdescription>
  <isdefault>false</isdefault>
</visualization>

, , ,

10 Comments

Compare This Year to Last Year with a Dynamics CRM chart

Comparing sales performance this period vs. the same period last year is a fairly standard metric. However, to create a chart that compares the period this year, with last years in the same date range can be tricky. For example, today is June 21st. I want to see my actual sales for this year to date, compared to my actual sales last year, until June 21st. Or Year to Date vs. Last Year to Date (YTD vs. LYTD).

YTD vs LYTD MS Dynamics CRM Chart Year to Data vs Last Yeard to Date

Or maybe I want to compare my sales this year and last year by month.

Chart compare to last year by month overlapped MS Dynamics CRM chart after customizing xml

In this post I will detail how I created both of these charts.

Compare YTD to LYTD in MS Dynamics CRM

Before I start to create the chart, I need to get some components ready first.

These are:

  • View of Won Opportunities – This and Last Year
  • Fetchxml filter for YTD
  • Fetchxml filter for LYTD

Create the View for the chart

Open Advanced Find on opportunities and create the following view:

Advanced Find - View Won This AND Last Year

Name it “Won Opportunities: This And Last Year.” Always use this view with the charts in this post.

After saving it, keep Advanced Find open and use it to create the fetchxml filters we need to modify the chart xml with.

Create the following filters and download the fetchxml. You can download the fetchxml without saving the View first.

Filter for YTD

Advanced Find - This Year

 Filter for LYTD

Advanced Find - Last Year and Older Than 1 Year

Using the Older Than X Years will make sure we only get the LYTD date range for the won opportunities.

Note: Are you on a version earlier than CRM2015, you can use “Older Than X Months”, with X being 12, and get the same result.

Chart Editor

Time to open up the chart editor.

Save a chart similar to the settings here.

Chart Editor to create YTD vs LYTD in MS Dynamics CRM

The two series for actual revenue, is what will be modified to YTD and LYTD using the filters we just downloaded from Advanced Find.

Now the chart xml is ready to export and we can have some fun!

Fetchxml edits

The two attributes for the actual value series will need to be edited so they use the filters we just downloaded.

xml original attrbiutes

Here is the fetchcollection with the additions made and the filters downloaded from Advanced Find highlighted in yellow.

      <fetchcollection>         <fetch mapping="logical" aggregate="true">           <entity name="opportunity">             <attribute groupby="true" alias="status" name="statecode" /> 			<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">               <attribute alias="LastYear" name="actualvalue" aggregate="sum" /> 				  <filter> 					  <condition attribute="actualclosedate" operator="last-year" /> 					  <condition attribute="actualclosedate" operator="olderthan-x-years" value="1" /> 				  </filter>             </link-entity>             <link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">               <attribute alias="ThisYear" name="actualvalue" aggregate="sum" /> 				  <filter> 					   <condition attribute="actualclosedate" operator="this-year" /> 				  </filter>             </link-entity>           </entity>         </fetch>       </fetchcollection>

Fetchcollection edits:

  • Created link-entity outer joins so the series only includes the filtered data.
  • Added the YTD and LYTD filters downloaded from Advanced Find.
  • Renamed the aliases from _CRMAutoGen_aggregate_column_Num_0 or similar to more sensible names.
    • If renaming the aliases, remember to update them in the categorycollection too.

Other changes:

     </fetchcollection>       <categorycollection>         <category alias="status">           <measurecollection>             <measure alias="ThisYear" />           </measurecollection>           <measurecollection>             <measure alias="LastYear" />           </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 XAxisType="Secondary" LegendText="This Year" LabelFormat="#,#,k;' ';' '" ChartType="Column" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="Point.75, MaxPixelPoint" />         <Series LegendText="Last Year" LabelFormat="#,#,k;' ';' '" ChartType="Column" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="Point.75, MaxPixelPoint" />       </Series>       <ChartAreas>         <ChartArea BorderColor="White" BorderDashStyle="Solid">           <AxisY 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 Format="#,#,k;' ';' '" Font="{0}, 10.5px" ForeColor="59, 59, 59" />           </AxisY>

  • Make sure the series are in the same order in both the categorycollection and in the series section
  • Custom LegendText
  • Custom LabelFormat
  • Format the Y axis numbers

The categorycollection is where you can adjust if YTD or LYTD should come first. In this example YTD comes first.

The new formatting is designed to show amounts in the thousands with negative and zero values suppressed. See this post for details on chart labels and formatting.

Import the XML and this should be your result.

YTD vs LYTD chart in MS Dynamics CRM. Custom chart xml.

Here is the chart and we can see how our performance is compared to the same period last year.

If you would like to see the data grouped by something else, such as customer or owner, you can open the editor and change the category.

Change GroupBy in editor for YTS vs LYTD chart, custom chart xml in MS Dynamics CRM

Just keep in mind that if you make any changes to the legend entries you will lose your xml customizations.

Compare to Last Year by Month

For better insights on our sales development, we can group the sales by month instead.

Start with the chart we just made and use the editor to change the groupby to Actual Close Date and Month.

Category Month

Click Save As and save the chart under a different name.

The result should look like this.

Compare to Last Year Month after changing groupby in chart editor.

Now we see the same data spread out per month instead. What I would like to do is include all of last years data and then have the month columns next to each other, so March of this year is next March of last year, etc.

In short, what I will do is remove the Older-Than-1-Year filter so we get all of last year. Next I will split the data onto two separate X axes. One for each year and set the chart to display them in sync with each other so the months line up.

Note: We are working with data by month. If data points are missing for a certain month, that month will not be included. For this chart, we are relying on having 12 data points for each year. If you do not have a data point for each  of the past months, then use dummy records to ensure a data point exists for the missing months. These can be of zero value and will not interfere with the calculations. You do not need records for future months.

Update filter and add secondary X axis

Export the chart xml.

First remove the “older than” clause in the fetchcollection.

              <attribute alias="LastYear" name="actualvalue" aggregate="sum" />               <filter>                 <condition attribute="actualclosedate" operator="last-year" />                 <!-- <condition attribute="actualclosedate" operator="olderthan-x-years" value="1" /> -->               </filter>

Now scroll down and add the secondary X axis. Both in the Series and Axis section.

<visualization>   <visualizationid></visualizationid>   <name>Compare to Last Year by Month - overlap axis</name>   <primaryentitytypecode>opportunity</primaryentitytypecode>   <datadescription>     <datadefinition>       <fetchcollection>         <fetch mapping="logical" aggregate="true">           <entity name="opportunity">             <attribute groupby="true" alias="status" dategrouping="month" name="actualclosedate" />             <link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">               <attribute alias="LastYear" name="actualvalue" aggregate="sum" />               <filter>                 <condition attribute="actualclosedate" operator="last-year" />                 <!-- <condition attribute="actualclosedate" operator="olderthan-x-years" value="1" /> -->               </filter>             </link-entity>             <link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">               <attribute alias="ThisYear" name="actualvalue" aggregate="sum" />               <filter>                 <condition attribute="actualclosedate" operator="this-year" />               </filter>             </link-entity>           </entity>         </fetch>       </fetchcollection>       <categorycollection>         <category alias="status">           <measurecollection>             <measure alias="ThisYear" />           </measurecollection>           <measurecollection>             <measure alias="LastYear" />           </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 XAxisType="Secondary" LegendText="This Year" LabelFormat="#,#,k;' ';' '" ChartType="Column" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="Point.75, MaxPixelPoint" />         <Series LegendText="Last Year" LabelFormat="#,#,k;' ';' '" ChartType="Column" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="Point.75, MaxPixelPoint" />       </Series>       <ChartAreas>         <ChartArea BorderColor="White" BorderDashStyle="Solid">           <AxisY 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 Format="#,#,k;' ';' '" Font="{0}, 10.5px" ForeColor="59, 59, 59" />           </AxisY>           <AxisX Minimum="0" Maximum="12.5" IntervalOffset="1" Interval="1" 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>           <AxisX2 Minimum="12" Maximum="24.5" IntervalOffset="1" Interval="1" 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" />           </AxisX2>		           </ChartArea>       </ChartAreas>       <Titles>         <Title Alignment="TopLeft" DockingOffset="-3" Font="{0}, 13px" ForeColor="59, 59, 59" />       </Titles>       <Legends>         <Legend Alignment="Center" LegendStyle="Table" Docking="right" IsEquallySpacedItems="True" Font="{0}, 11px" ShadowColor="0, 0, 0, 0" ForeColor="59, 59, 59" />       </Legends>     </Chart>   </presentationdescription>   <isdefault>false</isdefault> </visualization>

I added XAxisType=”Secondary” to the series for this year to indicate which series should go on the secondary X axis.

The AxisX2 section is identical to AxisX except for the “2” so you can copy it and add the number.

Import the chart xml back into MS Dynamics CRM.

Month over Month 2nd X axis added in chart xml

Notice that the secondary X axis has been added to the top of the chart. The blue columns are actually bound to the top (secondary axis) and the orange are on the bottom axis.

Overlap axes

Next step is to make the two axes overlap so it is easy to compare a month this year, to the same month last year.

We have 24 data points. One for each month of the two years. The series for last year is displayed on Axis 1 and should show points 0 – 12 (Jan-Dec of 2014). Axis 2 has the data for this year and should show points 12-24 (Jan-Dec of 2015).

To achieve that, I add Minimum and Maximum values to each axis so they are aligned.

Here are the chart xml axis edits.

<visualization>   <visualizationid></visualizationid>   <name>Compare to Last Year by Month - overlap axis</name>   <primaryentitytypecode>opportunity</primaryentitytypecode>   <datadescription>     <datadefinition>       <fetchcollection>         <fetch mapping="logical" aggregate="true">           <entity name="opportunity">             <attribute groupby="true" alias="status" dategrouping="month" name="actualclosedate" />             <link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">               <attribute alias="LastYear" name="actualvalue" aggregate="sum" />               <filter>                 <condition attribute="actualclosedate" operator="last-year" />                 <!-- <condition attribute="actualclosedate" operator="olderthan-x-years" value="1" /> -->               </filter>             </link-entity>             <link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">               <attribute alias="ThisYear" name="actualvalue" aggregate="sum" />               <filter>                 <condition attribute="actualclosedate" operator="this-year" />               </filter>             </link-entity>           </entity>         </fetch>       </fetchcollection>       <categorycollection>         <category alias="status">           <measurecollection>             <measure alias="ThisYear" />           </measurecollection>           <measurecollection>             <measure alias="LastYear" />           </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 XAxisType="Secondary" LegendText="This Year" LabelFormat="#,#,k;' ';' '" ChartType="Column" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="Point.75, MaxPixelPoint" />         <Series LegendText="Last Year" LabelFormat="#,#,k;' ';' '" ChartType="Column" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="Point.75, MaxPixelPoint" />       </Series>       <ChartAreas>         <ChartArea BorderColor="White" BorderDashStyle="Solid">           <AxisY 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 Format="#,#,k;' ';' '" Font="{0}, 10.5px" ForeColor="59, 59, 59" />           </AxisY>           <AxisX Minimum="0" Maximum="12.5" IntervalOffset="1" Interval="1" 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>           <AxisX2 Minimum="12" Maximum="24.5" IntervalOffset="1" Interval="1" 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" />           </AxisX2>		           </ChartArea>       </ChartAreas>       <Titles>         <Title Alignment="TopLeft" DockingOffset="-3" Font="{0}, 13px" ForeColor="59, 59, 59" />       </Titles>       <Legends>         <Legend Alignment="Center" LegendStyle="Table" Docking="right" IsEquallySpacedItems="True" Font="{0}, 11px" ShadowColor="0, 0, 0, 0" ForeColor="59, 59, 59" />       </Legends>     </Chart>   </presentationdescription>   <isdefault>false</isdefault> </visualization>

IntervalOffset=”1″ is added so the axis does not show the zero value (i.e. Dec 2014 on the 2015 axis).
Interval=”1″ is added to make the intervals consistent between the two axes and show every month.

Notice the maximums are set to 12.5 and 24.5. That is to make sure the data for the last month is displayed in full. If set to 12 and 24 only half the column would display.

Import the xml and this should be the final result.

Chart compare to last year by month overlapped. Chart xml custom MS Dynamics CRM

The chart type on Last Year can be changed to Line, ChartType=”Line”, to get this result.

Compare to Last Year Line chart. chart xml MS CRM

As always, hope you enjoyed the post and found something useful. Please follow me on Twitter or sign up for the newsletter to be alerted on new posts.

 

 

 

, , , , ,

38 Comments