Multiple entities in one MS CRM chart

For a complete overview of records owned by a user, it can be beneficial to see the count of leads, opportunities, accounts and contacts next to each other. For example, you can then ask the question; are the amount of opportunities owned appropriate compared to the amount of leads and accounts owned?

Out-of the-box, MS CRM does not provide an option to compare different entities in one chart. The solution is often to make one chart for leads, one chart for opportunities etc. and put those charts next to each other in a dashboard. However, since the Y-axis is updated dynamically, eyeballing the relative difference can be challenging.

With some XML modifications to the MS CRM chart, we can fortunately add multiple entities to one chart and get that instant overview of records owned per user.

But wait a minute. MS CRM does not support charting on multiple entities! That’s right, it doesn’t. The chart above is actually based on the user entity, checking the amount of records owned by each user.

This chart modification is possible in both CRM2011 and CRM2013.

User overview of Leads, Opportunities, Accounts and Contacts

Let’s get started. First create an xml file we can work with. Bring up a user view and open the chart editor. Add one series as a placeholder for each of the entities you want to chart for the users.

I’ve added four series (leads, opportunities, accounts and contacts), and selected Count:Non-empty as the aggregate. I have selected the user field in this example, but it’s just a placeholder for now.

Save the chart, export the xml and open it in your favorite editor. I still use Notepad++.

Locate the attributes in the fetchcollection that has the countcolumn aggregate. These are the lines that must be replaced.

I will start with the adding a count of leads owned by the user. Replace the first of countcolumn aggregates, the whole line, with the following code.

<link-entity name="lead" from="owninguser" to="systemuserid" link-type="outer">
	<link-entity name="lead" from="leadid" to="leadid" link-type="outer">
		<attribute alias="OpenLeads" name="leadid" aggregate="countcolumn" distinct="true" />
		<filter type="and">
		  <condition attribute="statecode" operator="eq" value="0" />
		</filter>
	</link-entity>
</link-entity>

This is the part that goes from the user record, to the lead entity and counts all the leads the user own.

Notes on the XML

• First <link-entity> finds the lead entity via the “owninguser” field.
• Second <link-entity> is self-referential which enables us to add a filter and only count records meeting a specific criteria.
• The aggregate must be “countcolumn”. Using the “count” aggregate will return an inaccurate amount.
• The distinct=”true” must be added.  Leaving out distinct=”true” will return an inaccurate amount.
• Filter and condition can be taken directly from an exported fetchXML, so use advanced find to create them for you.
• The use of link-type=”outer” is required. If link-type=”outer” is removed from the first line, the chart will only include users that have a lead record. A user with no leads would then not appear in the chart at all. This can be an alternate method to ensure that users with no records still appear in charts.

Repeat this step for each of the entities you want included, but modify the chart xml to point to the correct entity and field. Also check for any YAxisType=”Secondary” in the series and remove these.

Note that if you rename the alias, the same alias must be used in the measurecollection. In the example above the alias has been changed to “OpenLeads”.

And that’s it. I have included a full sample chart xml at the bottom of the post for reference.

A reader has previously pointed out that during a solution import, MS CRM will throw an error when using distinct=”true”. However, there are no issues importing the chart directly. Thanks for the heads up Martin.

Campaigns

The same method can be applied to campaigns, so we can have leads and opportunities generated by the campaign on the same chart.

We are not limited to the “countcolumn” aggregate. We can also use “sum”, so we can include actual and estimated revenue.

A chart of campaigns could include

• Count of active leads
• Count of open opportunities
• Sum of estimated revenue on open opportunities
• Sum of actual revenue on won opportunities

Here’s how the <link-entity> and aggregate looks when starting from the campaign. The sum aggregate refers to the estimated value field on the opportunity. The filter ensures only open opportunities are summed.

<link-entity name="opportunity" from="campaignid" to="campaignid" link-type="outer">
	<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
		  <attribute alias="EstValue" name="estimatedvalue" aggregate="sum" distinct="true" />
		  <filter type="and">
			<condition attribute="statecode" operator="eq" value="0" />
		  </filter>
	</link-entity>
</link-entity>

Since we are mixing countcolumn and sum on this chart, we must manually make sure that the currency fields are all on the secondary axis, by adding YAxisType=”Secondary” to each of those series. The chart editor has probably already added a secondary Y-axis when you added the multiple series, so just make sure the correct series go on the correct axis.

Note: While I am showing the use of SUM in this example, this is generally bad idea in combination with the distinct=”true” property. Since only unique values are included, two opportunities with an est. revenue of 10,000, would be summed as 10,000 instead of 20,000. Make sure your values are unique if you use this approach.

The chart type StackedBar can also be used instead of Bar for all of the series. That way we can see the full potential of the campaign.

Campaign chart showing the combined amount of leads and opportunities generated. Actual and estimated revenue are stacked to indicate full potential of campaign. Notice that campaigns with no leads or opportunities generated still appear in the chart.

Some considerations

There are no drill-down charts modified in this manner. On a user chart, any attempt at drilling down, will only show the user records. Not the leads or opportunities, etc.
It is fairly easy to hit the max record limit of 50,000 since all related records count. A user with 49,999 inactive leads, and 2 active, will go over the limit even if the chart is filtered to only show active leads. This is because the inactive leads are still processed, so only include the users you really need or divide them into smaller groups if you hit the max limit.

Hope you liked the post. Please sign up for the email newsletter and follow me on Twitter  Follow @crmchartguy

Sample chart

Sample is provided for test purposes only. Do not use in a production environment without proper testing.

<visualization>
  <visualizationid></visualizationid>
  <name>CRMChartGuy's Leads, Opportunities, Contacts and Accounts by User</name>
  <!--Sample chart provided for demonstration purposes only. For more details see www.crmchartguy.wordpress.com -->
  <primaryentitytypecode>systemuser</primaryentitytypecode>
  <datadescription>
    <datadefinition>
      <fetchcollection>
        <fetch mapping="logical" aggregate="true">
          <entity name="systemuser">
            <attribute name="systemuserid" groupby="true" alias="User" />
		<link-entity name="lead" from="owninguser" to="systemuserid" link-type="outer">
		  <link-entity name="lead" from="leadid" to="leadid" link-type="outer">
		  <attribute alias="OpenLeads" name="leadid" aggregate="countcolumn" distinct="true" />
			<filter type="and">
			  <condition attribute="statecode" operator="eq" value="0" />
			</filter>
		  </link-entity>
		</link-entity>          
		<link-entity name="opportunity" from="owninguser" to="systemuserid" link-type="outer">
			<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
				  <attribute alias="OpenOpportunities" name="opportunityid" aggregate="countcolumn" distinct="true" />
				  <filter type="and">
					<condition attribute="statecode" operator="eq" value="0" />
				  </filter>
			</link-entity>
		</link-entity>
		<link-entity name="account" from="owninguser" to="systemuserid" link-type="outer">
			<link-entity name="account" from="accountid" to="accountid" link-type="outer">
				<attribute alias="Accounts" name="accountid" aggregate="countcolumn" distinct="true" />
				<filter type="and">
				  <condition attribute="statecode" operator="eq" value="0" />
				</filter>
			</link-entity>
		</link-entity>		
		<link-entity name="contact" from="owninguser" to="systemuserid" link-type="outer">
			<link-entity name="contact" from="contactid" to="contactid" link-type="outer">
				  <attribute alias="Contacts" name="contactid" aggregate="countcolumn" distinct="true" />
				  <filter type="and">
					<condition attribute="statecode" operator="eq" value="0" />
				  </filter>
			</link-entity>
		</link-entity>				
          </entity>
        </fetch>
      </fetchcollection>
      <categorycollection>
        <category alias="User">
          <measurecollection>
            <measure alias="OpenLeads" />
          </measurecollection>
          <measurecollection>
            <measure alias="OpenOpportunities" />
          </measurecollection>
          <measurecollection>
            <measure alias="Accounts" />
          </measurecollection>		
          <measurecollection>
            <measure alias="Contacts" />
          </measurecollection>			  
        </category>
      </categorycollection>
    </datadefinition>
  </datadescription>
  <presentationdescription>
    <Chart Palette="None" PaletteCustomColors="55,118,193; 197,56,52; 149,189,66; 117,82,160; 49,171,204; 255,136,35; 97,142,206; 209,98,96; 168,203,104; 142,116,178; 93,186,215; 255,155,83">
      <Series>
        <Series ChartType="Bar" IsValueShownAsLabel="true" LegendText="Open Leads" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40" />
        <Series ChartType="Bar" IsValueShownAsLabel="true" LegendText="Open Opportunities" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40" />
        <Series ChartType="Bar" IsValueShownAsLabel="true" LegendText="Accounts" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40" />	
        <Series ChartType="Bar" IsValueShownAsLabel="true" LegendText="Contacts" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40" />			
      </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" 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" />
      </Titles>
      <Legends>
        <Legend Alignment="Center" LegendItemOrder="ReversedSeriesOrder" 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>