Posts Tagged custom colors

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

, , , , , , , ,

14 Comments

Using Custom Colors in Charts

At times it can be nice to define you own colors in charts. You may need to use a specific a corporate color scheme, make it easier to tell certain items apart or simply make the chart easier for the user to understand quickly.

It has always puzzled me how they chose the colors for some of the out-of-the-box system charts in Microsoft Dynamics CRM 2011.

Take the “Cases by Priority” chart for example.

At first glace this chart seems alright. Except when you look at how the series are colored, cases with “Normal” priority are red. In a chart like this red stands out more and most users would associate the red color with some kind of warning sign. On the other hand, the cases with priority high has the less alarming color blue.

Before we get into changing this specific chart, let’s look at some of our options for changing and modifying specific colors.

See this post for changing the complete Palette of a chart.

Export the charts xml file, open it and find the PaletteCustomColors property.

All the colors are listed as RGB colors, separated by a semicolon.

While Dynamics CRM add RGB colors to its custom palette it should be noted that Dynamics CRM accepts

  • RGB Decimal (example: 97,142,206)
  • RGB HEX (example: #4169E1)
  • Named colors (example: Blue)
  • ARGB (127,97,142,206)

The named colors could prove a lot easier to work with.

So instead of PaletteCustomColors=”97,142,206; 209,98,96; 168,203,104; etc.
we could write PaletteCustomColors=”Blue; Red; Green; etc.

Using the color names also makes it a lot easier to check if you have added them in the correct order.

This page contains an overview of the named colors available to use in charts.
http://msdn.microsoft.com/en-us/library/aa358800.aspx
Bonus: There’s one color missing from this list and that is “Transparent”. Transparent could prove very useful when you need to add data to a chart, but actually don’t want to show it. For example you may only want to display the value on a chart, but not have the bar or column visible.

Back to the example where we wanted to change the color scheme so High Priority Cases are red and Normal Priority Cases are Blue.
Since Dynamics CRM lists the series in reverse order (right to left) we know that Blue is the first color and therefore goes to the series for High Priority.

We want to change the the order of the colors so Red gets the first High Priority series. Then Blue and then Green.

To achieve that we need to edit the xml files so the first the colors of the PaletteCustomColors look like this.

Importing it back into Dynamics CRM we will get this result for the Cases By Priority Chart.

Now the High priority cases are appropriately Red and the Normal and Low priority cases have less alarming colors.

On some charts it is also possible to add specific colors to specific series, but I’ll save that for a later blog post.

Bonus: ARGB

Charts also support ARGB, which is similar to RGB, but there’s four values instead of three. The first value indicates the color’s Alpha transparency. 0 equals completely transparent. 255 is no transparency. 127 is 50% transparent etc.

Alpha transparency values are normally a percentage, but it has to be written in the RGB format which is a value between 0 and 255.

, , , , , , , ,

12 Comments

Palettes

When creating charts in Dynamics CRM, CRM creates a custom palette. However, it is the same custom palette that is being used on all you charts. So when you have dashboards with charts displaying data from different entities or different types of data, it can be difficult for the user to tell the information apart when everything is red, green and blue.

Fortunately it is fairly easy to export the charts xml file and modify it to display different color schemes, or Palettes as they are called. The Palette settings can be found right in the beginning of the presentationdescription. A quick search for “Palette” in your favorite xml editor (mine is Notepad++) will help you find it.

To apply a Palette, delete the cusot colors and replace “None” with one of the available Palette names. Before we start changing things, there a couple of things that are good to know about Palettes

  • They contain 16 colors – if you have more than 16 series in a chart, the colors start repeating after 16
  • The custom palette that CRM adds to charts have 12 colors
  • If Palette is “None” and there are no custom colors, then the Palette “BrightPastel” is used
  • If Palette is used, it will take precedence over your palette custom colors

Palette Options

The 12 different palettes available can be found here http://msdn.microsoft.com/en-us/library/system.web.ui.datavisualization.charting.chartcolorpalette.aspx

The options available are

  • Bright
  • Grayscale
  • Excel
  • Light
  • Pastel
  • EarthTones
  • SemiTransparent
  • Berry
  • Chocolate
  • Fire
  • SeaGreen
  • BrightPastel (the default if set to “None”)

Unfortunately Microsoft doesn’t seem to provide a graphical overview of the colors included, so here’s some samples to give you an idea of the color schemes.

The series is numbered 1-9 and are sorted in the same order. Meaning series 1 is the first color awarded etc. I am using a stacked column chart which shows the series in reverese order. Not sure why that is the default setting for a stacked chart. I figured 9 colors was enough to get an idea of what the palette looks like.

Standard Dynamics CRM custom palette for column charts

You are probably familiar with this color scheme by now.

Bright

GrayScale

Interesting that this is the only Palette that features a scale that gradually increases from light to dark. This might have been useful with other colors too.

Excel

But WHY!!!!! – Well, if you really like the Excel color scheme.

Light

Pastel

EarthTones

SemiTransparent

If you look closely you can see the gridlines though the columns. This may be more interesting to use on area charts or radar charts.

Berry

Chocolate

Fire

SeaGreen

BrightPastel (the default if set to “None”)

, , , , ,

5 Comments