Archive for category LabelFormat

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

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

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.

 

 

 

, , , , ,

43 Comments

Add Key Figures to Dashboards in MS Dynamics CRM

One of the most common questions for sales dashboards in MS CRM is “can I have a total on that Sales Pipeline funnel chart?”

Seems like a fair question, but somehow this not possible. At least not in a manner where the number can be formatted in a way that can be read properly.

My preferred method, is to create a chart that shows nothing but the key figures. Total sum of opportunities, the average amount of the estimated revenue, how many opportunities there are, next estimated close date, etc.

The chart itself can look like this.

MS Dynamics CRM key figures total, sum, average, next est. close date. CRM chart custom xml.

And here, put in context on a sales dashboard on top of the pipeline funnel.

Key Figures in Context of Sales Dashboard MS CRM. Chart component with custom chart xml.

How to create the Key Figures Chart in MS Dynamics CRM

As usual, start the chart editor so it can do the majority of the work.

1 CRM Chart editor to set up key figures. MS Dynamics CRM chart editor. Create base chart for furhter xml customization.

I have added a series for each of the key figures I want the chart to show.

These are all of the chart type bar. For the dates, Est. Close Date and Created On, only the count aggregates are available. We will need to replace that later in the chart xml for minimum and maximum date values.

For the category I selected Status. My view for this only includes active opportunities, and for this type of chart I need all the opportunities to fall in to the same category. In this case it is the active statuses.

Edit the chart XML

Export the xml and open it in your editor. Notepad++ is still my go to xml editor.

In the fetchxml section, the date values will need to be replaced from the countcolumn aggregates.

2 Fetchxml edit the aggregates for the date to min and max. MS Dynamics CRM charts xml.

In the example I have also renamed all of the aliases, so it is easier to see which attribute is what. If you do this in your xml too, remember to update the aliases in the categorycollection as well.

The series that displays the next Est. Close Date is set to “min”, giving us the lowest value of the dates which will be the next date an opportunity is supposed to close.
The series for the date of the latest opportunity that was created is set to “max”, which will give us the highest value in the set.

If you import the xml now, it should look like this.

3 first import of chart xml - Have all key figures, prior to editing layout in the MS CRM chart xml.

Good. All the values we want are now in the chart including dates on the bottom two bars.

Now for some major clean up as we do not need the axes, titles or legends for this. They do more harm than good in this case.

4 Remove unwanted sections from the chart xml in MS Dynamics CRM.

Click to enlarge. Yellow sections removed. Green section added.

 

Remove

  • AxisY2 section
    • And YAxisType=”Secondary” from one of the series
  • Titles section
  • Legends section

Add

  • Enabled=”False” to AxisY
  • Maximum=”1″ to AxisY – this is to help with alignment of the text
  • Enabled=”False” to AxisX

Edit the presentationdescription xml

We are now ready to work on the presentationdescription of the xml. This is where we set the labels, formatting and colors.

We will need to edit both the normal properties and the CustomProperties for this chart.

First we will edit the colors and labels.

Standard properties in the presentation description modified in hte chart xml in MS CRM.

Click to enlarge. Yellow sections modified.

 

  • Added Color=”Transparent” to each series to make the bar invisible. We only want the label.
  • IsValueShownAsLabel=”True” for sum, average and count as these are numbers.
  • IsValueShownAsLabel=”False” for the date values as we need to use keywords to display these better.
  • Font size has been increased for each series.
  • LabelFormat for sum, average and count values to add description.
  • Label for each of the date values with the #VALY keyword to insert the date without time.
  • Notice there are some extra spaces in front of the date values. It takes a little tinkering with these to get the alignment right on the final chart.

You can read more on label formatting in this post LabelFormat Cheat Sheet

The CustomProperties

Edit custom properties in the chart xml for MS Dynamics CRM

Click to enlarge. Yellow sections modified.

 

  • Added BarLabelStyle=Left to each series to help with alignment.
  • Adjusted PixelPointWidth for each series to accommodate the larger font sizes set earlier.
  • Adjusted MaxPixelPointWidth for each series due to the larger font sizes.

Import and here’s the final chart again.

Microsoft Dynamics CRM chart with important key figures for Opportunities.

One chart with all four key figures inside one chart component to put on a dashboard.

If you need more, the MS CRM chart xml will support up to nine key figures added in this manner.

Thank you for reading. Please sign up for the newsletter to stay up to date on new blog posts and follow me on Twitter

Here’s the xml file. As usual, provided for fun and games only.

<visualization>
  <visualizationid></visualizationid>
  <name>Key Figures - by CRM Chart Guy</name>
  <primaryentitytypecode>opportunity</primaryentitytypecode>
  <datadescription>
    <datadefinition>
      <fetchcollection>
        <fetch mapping="logical" aggregate="true">
          <entity name="opportunity">
            <attribute groupby="true" alias="statecode" name="statecode" />
            <attribute alias="SumTotal" name="estimatedvalue" aggregate="sum" />
            <attribute alias="Average" name="estimatedvalue" aggregate="avg" />
            <attribute alias="CountOfOpp" name="estimatedvalue" aggregate="count" />
            <attribute alias="NextCloseDate" name="estimatedclosedate" aggregate="min" />
            <attribute alias="LastCreated" name="createdon" aggregate="max" />
          </entity>
        </fetch>
      </fetchcollection>
      <categorycollection>
        <category alias="statecode">
          <measurecollection>
            <measure alias="SumTotal" />
          </measurecollection>
          <measurecollection>
            <measure alias="Average" />
          </measurecollection>
          <measurecollection>
            <measure alias="CountOfOpp" />
          </measurecollection>
          <measurecollection>
            <measure alias="NextCloseDate" />
          </measurecollection>
          <measurecollection>
            <measure alias="LastCreated" />
          </measurecollection>
        </category>
      </categorycollection>
    </datadefinition>
  </datadescription>
  <presentationdescription>
    <Chart Palette="None" PaletteCustomColors="91,151,213; 237,125,49; 160,116,166; 255,192,0; 68,114,196; 112,173,71; 37,94,145; 158,72,14; 117,55,125; 153,115,0; 38,68,120; 67,104,43; 124,175,221; 241,151,90; 186,144,192; 255,205,51; 105,142,208; 140,193,104; 50,125,194; 210,96,18; 150,83,159; 204,154,0; 51,90,161; 90,138,57;">
      <Series>
        <Series ChartType="Bar" Color="Transparent" IsValueShownAsLabel="True" Font="{0}, 30px" LabelFormat="Sum of Opportunities #,#,k" LabelForeColor="59, 59, 59" CustomProperties="BarLabelStyle=Left, PixelPointWidth=150, MaxPixelPointWidth=5000"></Series>
        <Series ChartType="Bar" Color="Transparent" IsValueShownAsLabel="True" Font="{0}, 28px" LabelFormat="Average amount #,#,k" LabelForeColor="59, 59, 59" CustomProperties="BarLabelStyle=Left, PixelPointWidth=150, MaxPixelPointWidth=5000"></Series>
        <Series ChartType="Bar" Color="Transparent" IsValueShownAsLabel="True" Font="{0}, 26px" LabelFormat="Number of Opportunities #,#" LabelForeColor="59, 59, 59" CustomProperties="BarLabelStyle=Left, PixelPointWidth=140, MaxPixelPointWidth=4000"></Series>
        <Series ChartType="Bar" Color="Transparent" IsValueShownAsLabel="False" Font="{0}, 20px" Label=" Next Est. Close Date #VALY" LabelForeColor="59, 59, 59" CustomProperties="BarLabelStyle=Left, PixelPointWidth=140, MaxPixelPointWidth=4000"></Series>
        <Series ChartType="Bar" Color="Transparent" IsValueShownAsLabel="False" Font="{0}, 15px" Label="  Latest Opportunity Created #VALY" LabelForeColor="59, 59, 59" CustomProperties="BarLabelStyle=Left, PixelPointWidth=140, MaxPixelPointWidth=4000"></Series>
      </Series>
      <ChartAreas>
        <ChartArea BorderColor="White" BorderDashStyle="Solid">
          <AxisY Enabled="False" Maximum="1" LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">
            <MajorGrid LineColor="239, 242, 246" />
            <MajorTickMark LineColor="165, 172, 181" />
            <LabelStyle Font="{0}, 10.5px" ForeColor="59, 59, 59" />
          </AxisY>
          <AxisX Enabled="False" LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">
            <MajorTickMark LineColor="165, 172, 181" />
            <MajorGrid LineColor="Transparent" />
            <LabelStyle Font="{0}, 10.5px" ForeColor="59, 59, 59" />
          </AxisX>
        </ChartArea>
      </ChartAreas>
    </Chart>
  </presentationdescription>
  <isdefault>false</isdefault>
</visualization>

Thanks for reading!

, , , , ,

27 Comments

N:N Relationship Charts in MS Dynamics CRM

N:N Relationships, or Many-to-Many Relationships, can be a little tricky to get information out of.

As an example competitors out-of-the-box have an N:N relationship with opportunities. Let’s say we wanted to look at a list of competitors and see how many active opportunities they are currently engaged in. With the chart editor and views, the best bet is to open each competitor record and count how many active opportunities it is related to. We can make that much easier with a chart like this.

Count and sum of estimated revenue for opportunities, per competitor chart in MS Dynamics CRM.

Count and sum of estimated revenue for opportunities, per competitor.

Before I get started on how to build the chart, let’s first have a look at how N:N relationships are structured in MS Dynamics CRM.

I will use the competitor opportunity relationship as an example, but this applies to all system and custom N:N relationships.

While it is not apparent in the solution designer, the relationship is controlled by an intersect table, in this case called “opportunitycompetitors”.

Table structure for competitor to opportunity N:N relationship in MS Dynamics CRM.

The intersect table is a system entity.

We will need this exact name of the intersect table for the fetchcollection in our chart xml.

To find the name of the intersect table, open up competitors for customization in the solution editor.

3 Solution Editor See NN relationships

Click N:N Relationships and open the relationship to opportunity.

4 Relationship Entity Name

Here, find the “Relationship Entity Name” towards the bottom of the form and make a note of the exact name for later.

Now that we have the name of the intersect table, on to building the chart. A full sample of the chart xml can be downloaded at the bottom of this post.

Build The Base Chart

As always, I start by creating a base chart I can use for editing the chart xml. I am starting on the competitor entity.

2 Create Base Chart

A simple bar chart counting competitors, grouped by competitor. Not very useful yet.

Simple – Count Opportunities Per Competitor

Export the xml and open it in your xml editor (Notepad++ perhaps).

Here’s the fecthcollection. I have renamed the obscure aliases to something more sensible. This is where we will do most of the work. If you change the aliases too, remember to also change them in the categorycollection.

      <fetchcollection>
        <fetch mapping="logical" aggregate="true">
          <entity name="competitor">
            <attribute groupby="true" alias="GroupBy" name="name" />
            <attribute alias="CountOfOpportunities" name="name" aggregate="count" />
          </entity>
        </fetch>
      </fetchcollection>

Now I want to change the chart xml, so I get a link to the intersect table and can count the number of relationships the competitor has to opportunities.

      <fetchcollection>
        <fetch mapping="logical" aggregate="true">
          <entity name="competitor">
			<link-entity name="opportunitycompetitors" from="competitorid" to="competitorid">
				<attribute alias="CountOfOpportunities" name="competitorid" aggregate="count" />
			</link-entity>
            <attribute groupby="true" alias="GroupBy" name="competitorid" />
          </entity>
        </fetch>
      </fetchcollection>

I have now created a link to the intersect table and placed the count aggregate inside of it. The groupby is still directly on the competitor entity. I also changed the name of the count attribute to “competitorid”. This is because the intersect table only holds the id fields.

Import to MS Dynamics CRM and look at the result.

Count of opportunities per competitor via the intersect table in MS Dynamics CRM.

Count of opportunities per competitor.

Now we have a list of competitors including a count of how many opportunities they are competing on.

On import you may run into this error message.

Error

If that happens, just change the width of the chart or refresh the page and it will work.

Advanced – Count and Sum of Estimated Revenue from Opportunities

That was charting on N:N relationships in its simplest form. However, as always we would like to do more. How about including the sum of the estimated revenue of those opportunities, and also filter it to only include active opportunities.

In order to achieve that, we need to add an extra link-entity to get all the way to the opportunity entity, so we can access fields there directly. In the first example we stopped on the intersect table.

Sample of fetchcollection which links from competitor, via the intersect table, to the opportunity entity.

      <fetchcollection>
        <fetch mapping="logical" aggregate="true">
          <entity name="competitor">
            <link-entity name="opportunitycompetitors" from="competitorid" to="competitorid">
				  <link-entity name="opportunity" from="opportunityid" to="opportunityid">
						<filter type="and">
						  <condition attribute="statecode" operator="eq" value="0" />
						</filter>
						<attribute alias="CountOfOpportunities" name="opportunityid" aggregate="count" />
						<attribute alias="EstimatedRevenue" name="estimatedvalue" aggregate="sum" />
				  </link-entity>
            </link-entity>
            <attribute groupby="true" alias="GroupBy" name="competitorid" />
          </entity>
        </fetch>
      </fetchcollection>

We now have two sets of <link-entity>, linking through the intersect table to the opportunity entity.

Notice the pattern in the links are name=”intersectable” from=”field with id from intersect table” to=”field with id from starting entity” and in the next line it is name=”the other entity” from=”field with id on other entity” to=”field with id on intersect table”. Since the id fields are mostly the same, once you have the name of the intersect table, these are fairly straight forward. Marketing lists are an exception to this.

Inside the <link-entity> to the opportunity entity, I have added a filter so we only include active opportunities. As usual, I just created a view in Advanced Find, downloaded the fetchxml, and copied the part with the filter.

I also added an extra attribute to include the sum of the estimated revenue for the opportunities.

Here’s what the final chart looks like.

N:N relationship chart in MS Dynamics CRM using only standard xml customizations in the chart xml.

Opportunities and sum of estimated revenue per competitor.

I also made these optimizations to the chart, just to touch it up a little.

  • In the Series
    • LabelFormat=”C0″  – so values in the chart have no decimals – that’s the letter “C” followed by a zero
    • DrawingStyle=Cylinder – in the custom properties just add some shading
  • In the Axes
    • Format=”C0″ – so dollar values on axis have no decimals
    • YAxisType=”Secondary” – on the series containing the sum to get count and sum on different axes
    • IsReversed=”True” – on the X axis so it is alphabetical from the top, not the bottom

A full sample can be found below with these changes included.

As a note, charting on N:N relationships also works on Marketing Lists (static only). That means you can create a list of users with a chart of how many marketing lists they are associated with. However, you will need to use a metadata browser to get the names of the intersect table (listmember) and its id field (entityid), which is different in this case.

Thank you for reading. Please sign up for the newsletter to stay up to date on new blog posts and follow me on Twitter

Sample chart xml for count and sum of opportunities per competitor

Here’s a sample chart xml ready for import to the competitor entity. It is made in an environment with no customizations so it should be easy to import. As always, samples are provided for fun only. Do not use in a production environment without proper testing.

<visualization>
  <visualizationid></visualizationid>
  <name>Opportunities per Competitor - by CRM Chart Guy</name>
  <primaryentitytypecode>competitor</primaryentitytypecode>
  <datadescription>
    <datadefinition>
      <fetchcollection>
        <fetch mapping="logical" aggregate="true">
          <entity name="competitor">
            <link-entity name="opportunitycompetitors" from="competitorid" to="competitorid">
              <link-entity name="opportunity" from="opportunityid" to="opportunityid">
                <filter type="and">
                  <condition attribute="statecode" operator="eq" value="0" />
                </filter>
                <attribute alias="CountOfOpportunities" name="opportunityid" aggregate="count" />
                <attribute alias="EstimatedRevenue" name="estimatedvalue" aggregate="sum" />
              </link-entity>
            </link-entity>
            <attribute groupby="true" alias="GroupBy" name="competitorid" />
          </entity>
        </fetch>
      </fetchcollection>
      <categorycollection>
        <category alias="GroupBy">
          <measurecollection>
            <measure alias="CountOfOpportunities" />
          </measurecollection>
          <measurecollection>
            <measure alias="EstimatedRevenue" />
          </measurecollection>
        </category>
      </categorycollection>
    </datadefinition>
  </datadescription>
  <presentationdescription>
    <Chart Palette="None" PaletteCustomColors="91,151,213; 237,125,49; 160,116,166; 255,192,0; 68,114,196; 112,173,71; 37,94,145; 158,72,14; 117,55,125; 153,115,0; 38,68,120; 67,104,43; 124,175,221; 241,151,90; 186,144,192; 255,205,51; 105,142,208; 140,193,104; 50,125,194; 210,96,18; 150,83,159; 204,154,0; 51,90,161; 90,138,57;">
      <Series>
        <Series ChartType="Bar" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="DrawingStyle=Cylinder, PointWidth=0.75, MaxPixelPointWidth=40">
          <SmartLabelStyle Enabled="True" />
        </Series>
        <Series ChartType="Bar" IsValueShownAsLabel="True" LabelFormat="C0" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="DrawingStyle=Cylinder, PointWidth=0.75, MaxPixelPointWidth=40" YAxisType="Secondary">
          <SmartLabelStyle Enabled="True" />
        </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" ForeColor="59, 59, 59" />
          </AxisY>
          <AxisX LabelAutoFitMinFontSize="8" IsReversed="True" 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" Format="C0" ForeColor="59, 59, 59" />
          </AxisY2>
        </ChartArea>
      </ChartAreas>
      <Titles>
        <Title Alignment="TopLeft" DockingOffset="-3" Font="{0}, 13px" ForeColor="59, 59, 59"></Title>
      </Titles>
    </Chart>
  </presentationdescription>
  <isdefault>false</isdefault>
</visualization>

If you made it this far. You’re awesome! Seriously. Tweet about it!

9 Comments

Add Percentage Labels to a 100% Stacked Bar chart in MS Dynamics CRM

The 100% stacked bar chart is great to display the relative amounts within a series.

However, for some reason the 100% stacked bar chart comes without labels to indicate what the percentage is for each group. You can eyeball it, but that’s not very accurate.

Original StackedBar100 chart

A 100% stacked bar chart made in the MS Dynamics CRM chart editor. No labels.

It is a little tricky to get to the accurate result, so I will go through the process of getting there.

The obvious thing to try first, is to export the chart xml and add IsValueShownAsLabel=”True” to the series.

        <Series ChartType="StackedBar100" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40">
          <SmartLabelStyle Enabled="True" />
        </Series>

 

IsvalueShownAsLabel True on a StackedBar100 chart

IsValueShownAsLabel=”True” added. Labels are the sum of the group.

This gives us the sum of each individual phase. This could be useful in its own right, but I really want the percentage on the label.

The next obvious thing to try, is to put in Label=”#PERCENT” and set IsValueShownAsLabel=”False”. Why not? It works great for pie charts.

        <Series ChartType="StackedBar100" IsValueShownAsLabel="False" Label="#PERCENT" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40">
          <SmartLabelStyle Enabled="True" />
        </Series>

 

Keyword PERCENT on StackedBar100 chart

Keyword Label=”#PERCENT” added. The percentages run across the bars rather than within a single bar.

Now we get some great looking percentages, but the values add up to 100 on the second series. Not across like we would expect.

The magic combo to add to the series xml for percentage labels on a 100% stacked bar chart in MS Dynamics CRM is:

IsValueShownAsLabel=”True” LabelFormat=”#.00′ %'”

        <Series ChartType="StackedBar100" IsValueShownAsLabel="True" LabelFormat="#.00' %'" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40">
          <SmartLabelStyle Enabled="True" />
        </Series>

 

StackedBar100 chart with correct percentages as labels in Microsoft Dynamics CRM charts.

With IsValueShownAsLabel=”True” and LabelFormat added, the percentages on the labels now display as intended.

Finally we have proper percentages across on the bar chart as we would expect.

 

A couple of other optimizations for a percentage bar chart.

LabelFormat=”#.00 ‘%'” in the series to show the percentage sign and always have 2 decimals.

Format=”0’%'” on the Y-axis to remove decimals and add percentage sign.

More on where and how to add Format and LabelFormat in MS Dynamics CRM charts here.

Thank you for reading. Please sign up for the newsletter to stay up to date on new blog posts and follow me on Twitter

, ,

9 Comments

LabelFormat Cheat Sheet

Formatting the labels on CRM charts can be a little tricky at times.

That’s why I’ve created a little cheat sheet, so I can always find a custom formatting option that fits, and copy it in to my CRM chart xml. Jump to the bottom of this post, if you want to go straight to the cheat sheet.

LabelFormat, or Format, can be used in two places. Either for the values displayed inside the chart, i.e. to indicate the exact value of a column, or the values on the axes, usually the Y axis. The formatting controls how values are displayed in charts. See examples below.

Before LabelFormat is applied on MS CRM Charts for MS Dynamics CRM 2011 in the chart xml

Standard labels for currency values in MS CRM Charts

After LabelFormat is applied in the chart xml for MS CRM 2011 Dynamics charts

With a custom format added to the chart xml, the values shorter and easier to read.

At the end of this post, I will explain where to insert the formatting and which property to use, depending on whether you want to format the values on the axis, or the values inside the chart.

I’ve tried to “format” my examples as code, so they can be cut and pasted directly into a crm chart xml. Hopefully I succeeded.

Disclaimer: This post contains a ton of commas and decimal points, and I’m sure I misplaced some of them. As Murphy’s Law will have it, it will be in the one you might want to copy. So always check that the results are what you want.

First I’ll go through how the formats are structured.

Important LabelFormat Characters

0     – Zero placeholder. Shows all digits incl. zeros.
#     – Digit placeholder. Shows all digits except zeros, unless the zero is significant.
.      – Decimal point
,      – Thousand separator and number scaling
;      – Section separator

The difference between # and 0 is how zeros are handled. Note the difference in the following two formats when displaying the value “0.30”

LabelFormat="#.##"        = .3
LabelFormat="0.00"        = 0.30

Or for example, the zeros in 30 and 105 are significant, but they are not significant in 0.50 and 11.00 (and 0 itself) and therefore not displayed.

Number Scaling – The Thousand Separator and Decimal Point

The thousand separator, or number scaler, is helpful if you want to shorten large numbers. Such as showing numbers in millions. Adjusting the amount of thousand separators allow you to adjust how much the number is scaled. Adding a decimal point and a placeholder, allow you to control the amount of decimals after the number has been scaled. See the following examples for the value 3,456,852.50

LabelFormat="#,#,,"        //  = 3
LabelFormat="#,#,,.##"     //  = 3.46
LabelFormat="#,#,,.###"    //  = 3.457
LabelFormat="#,#,.#"       //  = 3,456.9

Now the same formats for the value 456,852.5

LabelFormat="#,#,,"            // =  (nothing)
LabelFormat="#,#,,.##"         // = .46
LabelFormat-"#,0,,.###"        // = 0.457
LabelFormat-"#,0,.#"           // = 456.9

Note the # placeholder was changed to a zero in the last examples. That way the value can have a leading zero instead of just a dot.

Values are always rounded.

Add Text or Characters

You can add characters, like currency symbols, and spaces to the format so it is easier to read. Example value 3,456,852.50

LabelFormat="$#,#,,M"           //  = $3M
LabelFormat="$#,#,,.##M"        //  = $3.46M
LabelFormat="$ #,#,, M"         //  = $ 3 M
LabelFormat="United States Dollars #,#,,.## M"  //  = United States Dollars 3.46 M

Caution: There’s no check that you are adding the right number of thousand separators or adding the correct currency symbol to the format. In a multi-currency setup, you could easily create a chart that displays 500 Euros as “$ 500 k”.

Sections

The semi-colon can be used to add more sections to your format. You can have up to three section in your format string.

  • 1st section  –  Positive Values
  • 2nd section  –  Negative Values
  • 3rd section  –  Zero Values

This means you can format positive, negative and zero values individually and/or suppress some of them. So yes, that means it is possible to suppress zeroes in MS CRM charts.

If no extra sections are added, all values are displayed using the one format provided. Negative values are displayed with a minus sign -.

Example: Positive value is displayed with two decimals, negative values are in a parenthesis, and zero values are just a single zero.

LabelFormat="#,0.00;(#,0.00);0"

Value 24.5 shown as 24.50
Value -24.5 shown as (24.50)
Value 0.00 shown as 0

If you do not add a parenthesis or a minus sign, or something, to the negative value, it will display just like the positive values with no indication of being negative aside from its position on the axis.

Suppress Zeros

You can suppress zeros, or the negative or positive values depending on your needs. Just add two single quotes in the appropriate section.

Suppressing Zeros

LabelFormat="#,0.00;(#,0.00);''"

Suppressing Negative values and zeros

LabelFormat="#,0.00;'';''"

When adding text such as “$” and “k” to your format, it is good use all three sections and handle the zero separately. Otherwise zero values can appear as “$ k”.

Currency

Not really a setting, but there are a few things worth mentioning about labels and currency.

  • If no format is added, the chart automatically uses the correct currency symbol
  • When using the base currency field, it shows the value in the base currency
  • When using the normal currency field, the chart shows the values in the Users default currency, and properly calculated based on the exchange rates in the system.

If you do not need to scale the number, you can use “C” followed by a specifier to indicate the amount of decimals. If you do not add a specifier, decimals will be the same as the default in your CRM.

LabelFormat="C"      // Currency with default decimals
LabelFormat="C0"     // Currency with no decimals
LabelFormat="C3"     // Currency with 3 decimals forced

A negative value with “C” format is displayed in a parenthesis, unlike the default format which uses a minus sign.

These cannot be combined with the custom formats, so you can’t use the currency setting and scale the number at the same time. My guess is scaling is the main reason you are reading this post.

The Percentage Sign %

If you add a percentage sign % in your format, the number will automatically be multiplied with 100.

LabelFormat="#%"     // will display 0.5 as 50%

This is different from changing the label property to Label=”#PERCENT”. That is not really number formatting, so I will need to leave “#PERCENT” , “#AXISLABEL” and other keywords for a future blog post.

Cheat Sheet

LabelFormat="$#,0"           // No scaling, No decimals, leading zero
LabelFormat="$#,0,K"         // Thousands,  No decimals, leading zero
LabelFormat="$#,0,,M"        // Millions,   No decimals, leading zero
LabelFormat="$#,0,,,B"       // Billions,   No decimals, leading zero
LabelFormat="$#,0,,,,T"      // Trillions,  No decimals, leading zero

LabelFormat="$#,0.00"        // No scaling, Two decimals, leading zero
LabelFormat="$#,0,.00K"      // Thousands,  Two decimals, leading zero
LabelFormat="$#,0,,.00M"     // Millions,   Two decimals, leading zero
LabelFormat="$#,0,,,.00B"    // Billions,   Two decimals, leading zero
LabelFormat="$#,0,,,,.00T"   // Trillions,  Two decimals, leading zero

LabelFormat="$#,0.##"        // No scaling, Up to two decimals, leading zero
LabelFormat="$#,0,.##K"      // Thousands,  Up to two decimals, leading zero
LabelFormat="$#,0,,.##M"     // Millions,   Up to two decimals, leading zero
LabelFormat="$#,0,,,.##B"    // Billions,   Up to two decimals, leading zero
LabelFormat="$#,0,,,,.##T"   // Trillions,  Up to two decimals, leading zero

LabelFormat="$#,#.##"        // No scaling, Up to two decimals, no leading zero
LabelFormat="$#,#,.##K"      // Thousands,  Up to two decimals, no leading zero
LabelFormat="$#,#,,.##M"     // Millions,   Up to two decimals, no leading zero
LabelFormat="$#,#,,,.##B"    // Billions,   Up to two decimals, no leading zero
LabelFormat="$#,#,,,,.##T"   // Trillions,  Up to two decimals, no leading zero

Inserting the LabelFormat in the chart XML

In a Series

If you want to change the format for the values inside the chart, you have to insert the LabelFormat property in the Series collection.
Series insert LabelFormat
If the IsValueShownAsLabel=”True” not present already, then insert it as well.
Note that this is done per series, so you can have different formatting for each series if you need to.

On an Axis

If you want to change the formatting along the axis, usually the Y axis, insert it in the LabelStyle section in the appropriate axis.
Axis Format Insert
Note that the property here is called Format and not LabelFormat, but the structure of the format string is the same.

Hope you liked the post. Feel free to sign up for the email notification or follow me on Twitter 

The reference for Custom Numeric Format String can be found on the Microsoft website here http://msdn.microsoft.com/en-us/library/aa719871(v=vs.71).aspx

, , , , , ,

49 Comments