Archive for June, 2015

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

Annotations in MS CRM Charts

It might be a well-hidden secret, but it is actually fairly easy to add extra comments or annotations to your charts in MS Dynamics CRM.

This could be for extra descriptions or guidance on how to interpret the chart.

Here is an example of a chart with an annotation added.

Annotation

This example is simple, but the annotation can be customized with different fonts, sizes, positioning, have a tool-tip, etc.

Before moving on, I would highlight that a well-designed chart should not need any additional explanation ever. However, no rule without exceptions, so if you do need to add annotations to your chart, here’s how it is done.

Take any chart you want to add the annotation to, export the chart xml and add this piece of code towards the bottom between the </Legends> and </Chart> tags.
If you copy the code below, only include <Annotations> through </Annotations>.

      </Legends>
	  <Annotations>
		<TextAnnotation X="50" Y="0" Text="Annotation - CRM Chart Guy" TextStyle="Default" Font="Verdana, 15px" ForeColor="59, 59, 59" ToolTip="ToolTip for the Annotation"/>
	  </Annotations>
    </Chart>

The coordinates, X=”50″ and Y=”0″, sets the position of the annotation. They are percentages with 0,0 in the upper left corner and 100,100 in the bottom right. 50,50 would be right in the middle. In this sample I used x 50 and Y 0 to put the chart annotation centered on top.

Text is what you want the annotation to say.
TextStyle I have set to Default, but the options are Default, Embed, Emboss, Frame and Shadow.
Font=”Verdana, 15px” is the font type and size. Here is a good list of fonts if you want to switch it.
ForeColor sets the color of the font. See my previous post on colors in CRM charts for details.
Finally, you can add a tool-tip if you want to elaborate further.

You can find all the details on annotations on https://msdn.microsoft.com/en-us/library/system.web.ui.datavisualization.charting.textannotation%28v=vs.110%29.aspx

While annotations can be helpful in some cases, they unfortunately do not support keywords or any other dynamically inserted data. This means, for example, that you cannot add a total, average, or other keywords in the annotation.

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

4 Comments