Archive for category Filter in chart XML

Crowdsourcing the ultimate Sales Dashboard for Dynamics 365/CRM

When setting up a new Dynamics 365/CRM trial a few standard dashboards are included. However, I feel they lack something, and those who follow me on Twitter know I occasionally like to make fun of particularly the funnel chart. So here’s a hopefully improved version, that you can use, either in your organization or simply to spice up a demo environment.

Well, why not do something about it then. Plus, I’ve toying with this idea for a while anyway.

Crowdsourcing?

I did have the audacity to use the term “ultimate” in the title and the dashboard I provided is clearly not. However, with lots of input, we can make this much better.

Therefore; I would love to hear from you if you have suggestions how to make these dashboards better.

Suggestions could include, but are certainly not limited to:

  • Ideas for new KPIs
  • Ideas for new Charts
  • Ideas to improve formatting and make the charts easier to decode
  • Using charts contextually on record forms
  • Better use of colors between the different charts

My only request is that you elaborate on your suggestion, and I will try to incorporate as much as possible and hopefully have this grow into something very useful.

At the bottom of this post, I’ve included a zip file containing a solution with all the components for the dashboards in this post. The zip file contains both a managed and unmanaged solution that can be imported to Dynamics 365/CRM, and all the chart xml files individually.

Assumptions

It should be possible to import the charts on Dynamics 365/CRM organizations, so I will try to adhere to the following rules and assumptions:

  • Only use the charts within Dynamics 365/CRM*
  • Only use system fields
  • Only use system relationships
  • Avoid using calculated fields for KPIs – unless it is a system field
  • Take as much advantage of the existing setup as possible

*Why am I limiting myself to only using the charts within Dynamics 365/CRM and not including Power BI and Customer Insights. While Power BI and Customer Insights are powerful features, they are both tools external to Dynamics 365/CRM. You cannot as easily, share the charts and KPIs within CRM itself. Charts also have some significant advantages too. They can be contextual to the user logged in, respects the security roles, and they are real-time. “I just won an opportunity. I’ll happily wait an hour and then look at the sales leaderboard to see my position.” said no sales person ever!

 

The Sales Dashboard

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

The Sales Dashboard consists of the following charts:

1. Sales Funnel

Somehow it is not a sales dashboard if there isn’t a funnel on it. It is the universal indicator of a sales process regardless of how wrong it may be. I have done my best to overcome some of those areas.

2. Sales Leaderboard

The Sales Leaderboard is also a standard component on a sales dashboard. This one includes both won and in progress revenue. Users are ranked per the amount won. Current user is emphasized in a stronger color so it is easier to see your own position.

3. Sales KPIs about the open opportunities

KPIs include est. revenue, average value, number of opportunties in the pipeline, next est. close date and date the last opportunity was created. On the dashboard, the user can flip views between “Open Opportunities” vs. “My Open Opportunities” to get both group and personal KPIs.

4. Phase Loss Rate on Est. Revenue

This chart gives you the percentage of revenue lost per phase in the sales pipeline. For example, in the qualify stage, if opportunities est. 100 million entered the qualify stage, but only 45 million of est. revenue made it to the develop phase, then the loss rate for qualify is 65%. The same calculation for the develop phase, but of course excluding all the opportunities that never made it that far. In an ideal world, you would lose more opportunities in the initial stages and have a much higher win percentage towards the end.

Additional charts with variations on how the calculations are made are included in the zip file, including using the number of opportunities rather than the est. revenue.

5. Sales KPIs for Won Opportunities

Like the other KPI chart, but this one focusing on KPIs for the opportunities that were won.

Competitor Strength

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

Competitor Strength for Open Opportunities in Dynamics 365/CRM

The competitor strength chart lists all your competitors, ranked by how many opportunities you have lost to them.

On top of the lost bar, you can see how many opportunities are currently in progress.

Finally, in another bar, you have the est. revenue of the open opportunities where this competitor is a threat.

Sales Pipeline Analysis

Sales Pipeline Analysis in six charts in Dynamics 365/CRM

As mentioned earlier, I have included all the pipeline analysis charts in one dashboard for review. They all work in the same manner, although the calculations have a different focus as described by their names.

Download

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

Sales Dashboard by CRM Chart Guy Version 0.0.0.1

Update Notes November 20, 2016.

Pipeline Charts now only uses the beginning number of the step name, so as long as your phases are 1 through 4, then you would not need to make changes. Won opportunities will also be filtered correctly and do not necessarily need to be moved to the close stage. Regardless of the stage on a won opportunity, it is assumed that it has made it through all the stages.

Added some of the pipeline charts to a separate User form to analyze an individual’s performance against how the company KPIs.

Zip file includes all the chart xml files individually and a managed and unmanaged version of the solution file for Dynamics 365.

Solution files for pre-Dynamics 365 environments are no longer included. However, all the chart xml files can be imported separately into previous versions. Presumably, all the way back to CRM2011 although I have not been able to test that.

Added a chart for the Opportunity Products to see what products are in the pipeline. Products less than 10% of the total are grouped into “Other”.

Original Solutions Notes

All the charts that use stages or the pipeline rely on the Opportunity field called Pipeline Phase. Most trial instances have data in this already. Otherwise, it is populated either manually or by a workflow. The chart also assumes that the naming of the phases is kept from the trial so they are called 1-Qualify, 2-Develop etc.

The assumption I made in the calculations is that won opportunities are in the 4-Close phase. Therefore, make sure that the process that updates the pipeline phase puts won opportunities 4-Close. They really should be in that stage anyway if won.

The solution files with Dyn365 in the name are specifically for new trials or environments that have already been upgraded. This solution also includes the Competitor Chart.

Solution files with CRM_8.0 in the name are for CRM2016 and earlier. This version does not include the competitor strength chart as there’s a bug in CRM that does not allow the distinct=true property to be imported in a solution file. This was fixed with Dynamics 365.

All the chart xml files are in the zip file as well, so you can import them separately, make modifications etc.

Thanks for reading. Hope you enjoy and please share and let me know of any good suggestions.

And don’t forget to sign up for my newsletter on this page and follow me on Twitter

, , , , , , , ,

12 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.

 

 

 

, , , , ,

40 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

Include records with no value in charts!!!

A major limitation, or so I thought, with MS CRM 2011 charts, is that they can’t display data points that are not included in the dataset. Non-existing data could be just as important to the chart and overview as the data that’s actually present.

A common example, especially here at the beginning of the New Year, are charts showing User activity for a given time period. At the beginning of the period, not all Users have created new activities yet, and are therefore not included in the chart at all.

Rep missing from chart

The example above show the number of activties for the current week for Sales Rep 1 and 3. Sales Rep 2 have no activities yet for the current week, so the chart does not include that User. In order to better overview our organization, we would want to include Sales Rep 2 with a count zero activities. Doesn’t sound like too much to ask, right? In the example with 3 reps, it might not be as important, but even with just 7-8 or more Users, a missing person or item could easily get lost. Besides, a good chart shows the necessary data immediately. Slowing down to count items to see who’s missing is not the approach we want.

The trick to include the zero data lies in a combination of adding a filter on a single attribute within the chart xml file, and having a larger set of records filtered by the View attached to the chart.

Or to put it another way; to create the chart we’ll have a View showing All Activties, but then we’ll add a filter to the chart, so it only counts the values that meet our criteria. In this case; Activities with a Start Date in This Week.

I’ll use the system chart “Activities by Owner” as a base and make modifications to that.

Activities by Owner - Fetch Collection Original

Above is the original. We want to replace the highlighted attribute in the fetchcollection of the xml file, so it includes a filter for our criteria. Currently it just counts everything.

Here’s the new version with the filter. Everything between the two highlighted lines replaces the attribute mentioned.
New fetchcollection in chart xml

IMPORTANT: We can’t just add the filter to the attribute in line with the rest of the fetchcollection. If we did that, the filter would apply to the whole data set and we would be back where we started. We only want the filter to apply to the data we are counting, but make sure all the other data is still included. To achieve that we have to make a link-entity to the same entity that we are already on. The process is similar as if we wanted to create a link to a different entity, except it’s self-referential.

We add the link-entity information along with the attribute we are counting and add the filter below it.

Also note I changed the aggregate from “=count” to “=countcolumn”. If left at “=count”, it would count all the records in the dataset and not just the ones we want filtered.

Note: The easiest way to create the filter expression in xml is by making them in Advanced Find and then downloading the FetchXML file from there.

Remember also to match the alias in the measurecollection with what we named it when aggregating it.

Change in measurecollection to match fetch

When imported back into CRM I now get this chart

Chart with all Users - final

Now it is showing all my Sales Reps, incl. the one that does not have any data matching the criteria. Now I can easily see who have not created any activities yet this week.

It’s a little work to add zeros to a chart, but that piece of data can be just as important as everything else.

I’ve experienced that MS CRM 2011 implementations can get very long lists of Views over time, especially as charts can need their own View to go with it. This approach could signifcantly reduce the need for addtional Views and keep the filtering within the charts themselves.

I’ve just started experimenting with these self-referential link-entities in the chart xml, but it seems there is some interesting potential hidden there, starting with the ability to include the value zero in charts like the one above.

, , , , ,

26 Comments