Archive for category Colors in charts

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>

 

 

, ,

14 Comments

Aggregate Total on top of Stacked Column Charts or Bar Charts in MS CRM 2011

Adding an aggregated total to the top of a stacked column chart sounds simple enough. It’s a couple of clicks in Excel. In MS CRM 2011 however, it is a different story. You would have to add an additional series to the chart, which then calculates and displays the total. But Stacked Column charts in MS CRM 2011 do not support multiple series.

CRM 2011 Stacked Column Chart with aggregate total on top

But that does not mean it is not possible. This CRM chart show us the aggregated total value of each of our pipelines, stacked by the pipeline phases, for each of the types of purchase processes.

So how did I create this chart? First, I need a chart with a series for each of the 4 pipeline stages, and one for the total. Then I display the series in the chart so they look like a standard stacked column chart. The total on top is a point chart type. The drawback here, is of course that all the possible series needs to be known so we can include them in the chart. In this case, all the series are each phase in the pipeline.

Since the user interface in MS CRM 2011 for creating charts does not support multiple series for stacked column charts, we need to create something else as our chart XML base and work off that.

I will use the UI to create a normal column chart and add all the series I need.

In this case a column chart with 5 series.

Chart Designer with all the series and category - CRM 2011 CRM Chart

1 for each of the 4 stages in the pipeline (Develop, Qualify, Propose, Close) plus 1 extra series for the total.

I want to see how my pipelines looks between the different types of Purchases Processes, so I’ll pick “Purchase Process” as the category so I get that on the X axis.

All the series I have added as a standard column chart.

Now I’ll export the chart xml and get to work. (Full chart XML samples are available at the bottom of this post)

CRM 2011 might add some references to a secondary Y axis in your XML. If that is the case, remove these first.

Rename the series in the Chart XML

I’ll start out by renaming all the aliases to something more sensible and easier to work with.

Original XML

Original Chart XML datadescription with aliases highlighted

Chart XML with renamed aliases

Chart XML with new aliases

Now it’s much easier for me to see which alias represents what data. I have “Purchase Process” as the category, the name for each pipeline phase, and the aggregated total at the end.

Change the Series Chart Types

Next I’ll change the first 4 series for the pipeline phases from ChartType=”Column” to ChartType=”StackedColumn”.

The last series for the aggregated total will need the following changes;

  • ChartType=”Point”
  • IsVisibleInLegend=”False” – don’t need to see “Total” in the legend
  • IsValueShownAsLabel=”True” – YES, this is the whole point of this chart
  • LabelFormat=”#,#,#” – no need to see decimals on the total
  • MarkerColor & MarkerBorderColor=”Transparent” – we just want the total amount – no need to see a marker also
  • The font I increased to 14px so it stands out more

Changing the series charttypes in the xml

Let’s do a quick import of the chart xml just to check what it now looks like in CRM.

Chart after intial modifications - no filters, all totals

We can see the total amount in the larger font, but the different pipeline phases are also showing the aggregate amount.

Let’s add a filter to each of the series in the fetchcollection, so they only show the correct amount for each phase.

Filter the series for the Pipeline Phases

Here’s the original fetchcollection.

Chart XML Fetchcollection with new aliases

I’ll add a filter to each of the series by creating a self-referential <link-entity>. This will allow me to make sure each series only sum up the value of the Opportunities, specific to its phase.  The easiest way to get the filter properties is by creating them in Advanced Find and export the FetchXML.

Each attribute will get a <link-entity> and <filter> added as follows.


<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
  <attribute alias="qualify" name="estimatedvalue" aggregate="sum" />
    <filter>
      <condition attribute="stepname" operator="eq" value="1-Qualify" />
    </filter>
</link-entity>

Here’s a part of the fetchcollection with filters added for each attribute for the pipeline phases. The last attribute I’ll leave without a filter as we do want the total on that one.

Chart XML Fetchcollection with Filters for each phase matcing the alias

Time to import the XML and have a look.

Chart with Total on Top - Filtered, but with Orignial colors and settings

Chart is now filtered and stacked properly for each series and there’s an aggregated total on top.

Adjust Chart Axis, Legend and Colors

It works, but we can make the chart look a lot easier to read and understand.

I’ll make the following changes:

  • IsValueShownAsLabel=”false” on all series except the total.
  • Add LegendText to all the series except the total
  • Add Color to each series – I’m going with increasingly darker shades of blue in this case
  • Add Format=”$#,#,k” to the LabelStyle for a shorter label on the Y axis

Last, I’ll reverse the series in the legend, because MS CRM 2011 automatically reverses it on stackedcolumn charts. In other words, I reverse the reversed order so it reads 1-2-3-4 instead of 4-3-2-1.

Cleaning up the Chart by adding colors ot the series etc.

One final import of the CRM chart XML and here’s the result.

CRM 2011 Chart - Stacked Column with Aggregate Total on Top

A couple of notes:

  • Bonus: Even though no Opportunities are in phase “4-Close”  we still see it in the legend
  • Filtering in the XML can be tricky – it’s your job to make sure everything relevant get’s accounted for
  • This approach allow you to add colors directly in the Series, which can be a lot easier than using the PaletteCustomColors
  • The Series have to be listed in the exact same order in the presentationdescription as they do in the measurecollection
  • The User can still hover the mouse over the chart to get the exact value of each pipeline phase in the tooltip

How about an Aggregate Total on top of a Stacked Bar Chart?

The same approach can be used for bar charts. All the chart types need to be a Stacked Bar for the pipeline phases and then a regular Bar type for the total, which then have to be made transparent. (XML sample included at the bottom of post)

CRM Chart 2011 Stacked Bar with Aggregate Total on top

Hope you enjoyed this post.

If you did, please follow me on Twitter for CRM Chart updates

Chart XML Samples

These chart xml samples are for reference only and not intended for use in a live environment without thorough testing.

They were created on a Microsoft Dynamics CRM 2011 trial with only the sample data added. The only addition I made to the sample data, was adding values to the Purchase Process field on the Opportunities.

Stacked Column Chart with aggregated total

<visualization>
<visualizationid>{C618533F-3784-E211-8B16-78E3B5114607}</visualizationid>
<name>Aggregated Total on top of StackColumn chart</name>
<primaryentitytypecode>opportunity</primaryentitytypecode>
<datadescription>
<datadefinition>
<fetchcollection>
<fetch mapping="logical" aggregate="true">
<entity name="opportunity">
<attribute groupby="true" alias="purchase_process" name="purchaseprocess" />
<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
<attribute alias="qualify" name="estimatedvalue" aggregate="sum" />
<filter>
<condition attribute="stepname" operator="eq" value="1-Qualify" />
</filter>
</link-entity>
<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
<attribute alias="develop" name="estimatedvalue" aggregate="sum" />
<filter>
<condition attribute="stepname" operator="eq" value="2-Develop" />
</filter>
</link-entity>
<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
<attribute alias="propose" name="estimatedvalue" aggregate="sum" />
<filter>
<condition attribute="stepname" operator="eq" value="3-Propose" />
</filter>
</link-entity>
<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
<attribute alias="close" name="estimatedvalue" aggregate="sum" />
<filter>
<condition attribute="stepname" operator="eq" value="4-Close" />
</filter>
</link-entity>
<attribute alias="aggregated_total" name="estimatedvalue" aggregate="sum" />
</entity>
</fetch>
</fetchcollection>
<categorycollection>
<category alias="purchase_process">
<measurecollection>
<measure alias="qualify" />
</measurecollection>
<measurecollection>
<measure alias="develop" />
</measurecollection>
<measurecollection>
<measure alias="propose" />
</measurecollection>
<measurecollection>
<measure alias="close" />
</measurecollection>
<measurecollection>
<measure alias="aggregated_total" />
</measurecollection>
</category>
</categorycollection>
</datadefinition>
</datadescription>
<presentationdescription>
<Chart Palette="None" PaletteCustomColors="55,118,193; 197,56,52; 149,189,66; 117,82,160; 49,171,204; 255,136,35; 97,142,206; 209,98,96; 168,203,104; 142,116,178; 93,186,215; 255,155,83">
<Series>
<Series ChartType="Stackedcolumn" LegendText="1-Qualify" Color="LightSteelBlue" IsValueShownAsLabel="False" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series>
<Series ChartType="Stackedcolumn" LegendText="2-Develop" Color="CornflowerBlue" IsValueShownAsLabel="False" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series>
<Series ChartType="Stackedcolumn" LegendText="3-Propose" Color="RoyalBlue" IsValueShownAsLabel="False" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series>
<Series ChartType="Stackedcolumn" LegendText="4-Close" Color="DarkBlue" IsValueShownAsLabel="False" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series>
<Series ChartType="Point" IsVisibleInLegend="False" IsValueShownAsLabel="True" LabelFormat="$#,#,#" MarkerBorderColor="Transparent" MarkerColor="Transparent" Font="{0}, 14px" LabelForeColor="59, 59, 59"></Series>
</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 Font="{0}, 10.5px" Format="$#,#,k" ForeColor="59, 59, 59" />
</AxisY>
<AxisX 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" LegendItemOrder="ReversedSeriesOrder" Font="{0}, 11px" ShadowColor="0, 0, 0, 0" ForeColor="59, 59, 59" />
</Legends>
</Chart>
</presentationdescription>
<isdefault>false</isdefault>
</visualization>

Stacked Bar Chart with aggregated total

 <visualization>
<visualizationid>{C618533F-3784-E211-8B16-78E3B5114607}</visualizationid>
<name>Aggregated Total on top of Stackedbar Chart</name>
<primaryentitytypecode>opportunity</primaryentitytypecode>
<datadescription>
<datadefinition>
<fetchcollection>
<fetch mapping="logical" aggregate="true">
<entity name="opportunity">
<attribute groupby="true" alias="purchase_process" name="purchaseprocess" />
<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
<attribute alias="qualify" name="estimatedvalue" aggregate="sum" />
<filter>
<condition attribute="stepname" operator="eq" value="1-Qualify" />
</filter>
</link-entity>
<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
<attribute alias="develop" name="estimatedvalue" aggregate="sum" />
<filter>
<condition attribute="stepname" operator="eq" value="2-Develop" />
</filter>
</link-entity>
<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
<attribute alias="propose" name="estimatedvalue" aggregate="sum" />
<filter>
<condition attribute="stepname" operator="eq" value="3-Propose" />
</filter>
</link-entity>
<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
<attribute alias="close" name="estimatedvalue" aggregate="sum" />
<filter>
<condition attribute="stepname" operator="eq" value="4-Close" />
</filter>
</link-entity>
<attribute alias="aggregated_total" name="estimatedvalue" aggregate="sum" />
</entity>
</fetch>
</fetchcollection>
<categorycollection>
<category alias="purchase_process">
<measurecollection>
<measure alias="qualify" />
</measurecollection>
<measurecollection>
<measure alias="develop" />
</measurecollection>
<measurecollection>
<measure alias="propose" />
</measurecollection>
<measurecollection>
<measure alias="close" />
</measurecollection>
<measurecollection>
<measure alias="aggregated_total" />
</measurecollection>
</category>
</categorycollection>
</datadefinition>
</datadescription>
<presentationdescription>
<Chart Palette="None" PaletteCustomColors="55,118,193; 197,56,52; 149,189,66; 117,82,160; 49,171,204; 255,136,35; 97,142,206; 209,98,96; 168,203,104; 142,116,178; 93,186,215; 255,155,83">
<Series>
<Series ChartType="Stackedbar" LegendText="1-Qualify" Color="LightSteelBlue" IsValueShownAsLabel="False" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series>
<Series ChartType="Stackedbar" LegendText="2-Develop" Color="CornflowerBlue" IsValueShownAsLabel="False" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series>
<Series ChartType="Stackedbar" LegendText="3-Propose" Color="RoyalBlue" IsValueShownAsLabel="False" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series>
<Series ChartType="Stackedbar" LegendText="4-Close" Color="DarkBlue" IsValueShownAsLabel="False" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series>
<Series ChartType="Bar" IsVisibleInLegend="False" IsValueShownAsLabel="True" LabelFormat="$#,#,#" Color="Transparent" Font="{0}, 14px" LabelForeColor="59, 59, 59"></Series>
</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 Font="{0}, 10.5px" Format="$#,#,k" ForeColor="59, 59, 59" />
</AxisY>
<AxisX 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" LegendItemOrder="ReversedSeriesOrder" Font="{0}, 11px" ShadowColor="0, 0, 0, 0" ForeColor="59, 59, 59" />
</Legends>
</Chart>
</presentationdescription>
<isdefault>false</isdefault>
</visualization>

, , , , , , ,

61 Comments

Add insight to your pipeline with Advanced Goal Charts

The goal charts in CRM are already quite complex and hold a lot of information. The goal charts are combination of the Column and Point charts and display 4 different series (or Legend Entries). So why would I possibly want to make that even more complex. Well, some selling scenarios do require a more nuanced look at your pipeline.

First lets have a look at the built-in “Goal Progress (Money)” chart.

  • Target – the blue bull’s eye shows what we are aiming for
  • Today’s Target – the triangle is a calculated measure of where we should be today
  • In-Progress is the current value of our pipeline
  • Actual is the value of Won Opportunities

Once you have gotten used to seeing that many legends on one chart, this chart is actually very good and easy to read. I scan it and see if the green bar meets the triangle and if the grey bar is higher than the blue dot.

In business terms that would sound more like “Is my actual revenue for this sales period on track and do I have enough Opportunities of high enough value to meet my sales goal for the period”.

Where this chart is lacking information, is that the In-Progress series only show you the combined value of your pipeline. You actually have no idea if these are new Opportunities early in their development phase, or if contracts are almost ready to be signed. Maybe that’s why its the color grey.

“Weighted Revenue” (Est. Revenue * Probability of Winning%) is often used to calculate how much you reasonably can espect to close. Whether or not your probability is based on a defined gated sales process, or an estimate entered by the User, is really up to what works best for your business. For this specific scenario I prefer the gated sales process as it not only tells you the likelihood of winning, but also more importantly, gives you an indication of how far in the sales process you actually are.

You can read more about sales processes and weighted revenue on Richard Knudson’s very fine www.dynamicscrmtrickbag.com. I won’t go into much detail here on exactly how to add Weighted Revenue to an Opportunity using Processes.

Back to the chart. It would be fairly easy to just change the goal so it rolls up weighted revenue instead of the Est. Revenue. However, that would only give us a view of how much our weighted revenue is. That might be equally as confusing as just having the whole pipeline. We also want to add the rest of the pipeline so we know what our full pipeline value is.

Enter “Floating Revenue”

When calculating the Weighted Revenue, as a part of the process, make sure you also have a field called Floating Revenue where you insert the remaining amount (Est. Revenue – Weighted Revenue = Floating Revenue). In the Process you would add Est. Revenue to your new field and in the next step decrement it by the value in Weighted Revenue.

We also need to add the Floating Revenue as a Roll Up field for the Goal. Luckily, up to three Roll-Up fields on one Goal Metric is supported. I’d suggest using a new Goal Metric rather than to edit the existing one.

For our Goal Metric for the Advanced Goal Chart we have:

  • inprogressmoney – Weighted Revenue (originally for Est. Revenue)
  • actualmoney – Actual Revenue (same as the orginal Goal Metric)
  • customrollupfieldmoney – Floating Revenue (the new roll-up field)

Now that we have the data being rolled into the Goal, we need to modify the chart to display the extra data. Export the chart “Goal Progress (Money)” and use it as a base.

Notice the actualmoney and inprogressmoney fields. We will need to add the Floating Revenue to the list of attributes as well as for the measurecollection and the Series. We will get into that later. Note that the order here is relevant as we want to keep the stacked column chart with Actual Revenue on the bottom, then Weighted Revenue and finally topping up with the Floating Revenue.

Here are the additions to the xml file.

In the fetch collection I’ve added Floating Revenue – schema name on the Goal entity is customrollupfieldmoney.

The new field also needs to be added in the measurecollection

Finally we can add the extra Series. Since the Floating Revenue is fairly similar to Weighted Revenue from a charting perspective, we can just copy that series and make modifications to it.

In addition to copying the series I modified the color property for the both the Weighted and Floating Revenue. I gave Weighted Rev. the DarkGray color, and Floating Rev. the LightGray color. Using the color names make it easier to not get them mixed up, rather than modifying the RGB values.

Let’s import it back into CRM and see what happens. And remember to use the Advanced Goal Chart with the Advanced Goal Metric.

So far, so good. The data is now being displayed as intended with the full pipeline broken into two, giving us an idea of where we are in the sales process for those Opportunities. However, there are still some items we can clean up before it’s really good.

First let’s fix the legend texts to something better than “Custom Rollup Field (Money)” etc.

Adding the “LegendText=”XYZ” will allow you to modify the legends to something more sensible and utilize space better. In this case there’s really no reason to add the “(Money)” to the end of every description.

It’s getting better, but we can stil do some improvements.

Let’s remove the “Name”. We do not need to display that the title of the Series is “Name”. In this case referring to the Goal Name. We can’t actually remove it, but we can make it transparent by changing the color.

The Y-axis can be improved a little by writing $100k instead of 100,000. That’s a lot of zeros to repeat on this chart.

We do that by adding “Format” property and setting it to $#,#,k.

I would like to do the same for my Actual Revenue by adding the figure to the chart.

I’ve changed the IsValueShowAsLabel to “True” and added the same format to the label.

NOTE: Here on the Series the property is called “LabelFormat”. On the Axis it is just “Format”.

Let’s have a look at how we are doing.

Starting to look like the finished product. Now this chart has one drawback that I want to fix. While I can easily hover my mouse over the grey parts of the stacked column to get the value, I still only get the value of either the Weighted or Floating revenue. The chart will not give me the full value of the pipeline. I can’t just look at the full height of the column either, as that includes the Won revenue. I would need to do some extra math here in order to get the full pipeline value. That is something I’d expect the chart to do for me.

To get the full pipeline value on the chart we need to add it as a field to the Goal Record. We can’t just add an extra rollup field as only 3 are allowed. However, all we need is a process that aggregates the Weighted and Floating Revenue to a new money field every time the Goal Record is recalculated.

I’ll add the new Series to the xml file in the same manner I added Floating Revenue to the fetch collection, measurecollection and Series.

For the Series I copied the “Target” series.

I added the IsValueShownAsLabel property, set the LabelFormat and increased the font to 12px. I also changed all the marker colors to “Transparent” so we do not see a Marker on the chart. Just the full value of the pipeline. Note that I added the IsVisibleInLegend=”False” as I do not want it to show up on the legend. I also removed the name of the series that I copied from.

Now, let’s have one more look at the chart.

Now we have a Goal Chart displaying the Won revenue and the pipeline divided into how much its weighted displayed against our goal and how far we should be today.

When reaching towards the end of a goal period, this chart makes it easy for Sales Managers to see how close their sales people are to reaching their goals. If your pipeline is mostly LightGrey, it means you are probably working with mostly new Opportunities making it unlikely that you will reach your goal. On the other hand, if the pipeline is mostly DarkGrey, you are far in your sales process and all the more likely to close within the goal’s period.

Note: The term “Floating Revenue” is something I invented for this chart. Not sure if there’s an official term for it, but “Floating Revenue” seems to make sense and works well opposite the term “Weighted Revenue”. “Floating Revenue”, you read it here first 🙂

Now that we have gotten this far, why not try and over-engineer this thing a little.

Since Weighted and Floating Revenue isn’t an exact science, we can add some approximation to how the chart is viewed. To do this, we will change the grading in the pipeline so it changes according how much of the revenue is weighted, without giving the exact amount.

Gradient colors xml

Click to enlarge

We wil add a TopBottom grading on the colors and make sure the gradients meet each other, where the Floating Revenue turns into Weighted Revenue. In the example above I’m using the grey color (RGB 100,100,100) but I’m using it in the ARGB format so I can make the gradient go from very transparent to almost solid.

ARGB goes from 0, completely transparent, to 255 completely solid. More on that in this blog post.

For Floating Revenue I’ll start the transparency 20 and go to 120. For Weighted Revenue I’ll continue from 120 and go to 220.

In the example below, it should be clear which of the two goals is further in their sales process, and thus more likely to reach their target. The gradient also makes it clearer that the 475k is the value of the full pipeline.

GradientHighGradientLow

The colors can be modified to make the transition more or less apparent depending on how much emphasis you want to give it. You could also make a 10 or 20 point difference in the color grading so you have subtle indicator as to exactly where Weighted and Floating Revenue meet. I could also remove either “Floating” or “Weighted” in the Legend and just have the In-Progress value.

Enjoy.

, , , , ,

22 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