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.

Multiple entities in one MS CRM chart. Accounts, Leads, Opportunities, Contacts count for each User via chart xml modification.

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.

Chart Designer for creating User chart to count Accounts, Leads, Contact, Opportunities in one chart with multiple entities.

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.

fetchcollection from base xml - MS CRM chart for multiple entities in one chart.

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 with both Lead and Opportunity information in one chart.

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  

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>

28 thoughts on “Multiple entities in one MS CRM chart

  1. RE:”Second is self-referential which enables us to add a filter and only count records meeting a specific criteria.”

    Question: Why is the second embedded link needed to filter? Can’t we filter in the primary link section?

    • Good question Wendell. Generally you “always” need the extra self-referential link to make sure that your filter is not applied across the board, but only to the intended series. “Always” is in quotations, because this type of chart might be the one exception to that rule. However, for simplicity I like to state that it is needed when applying a filter to just one series in a multi-series chart. Makes cut and pasting from xml to xml easier too.

  2. First, i want to thank you for the quality of your posts.
    Can you post the XML of second example with StackedBar ?

  3. Thanks! It’s very useful! I have one question please… Ok for the user but how can I add team column next to the user in the same chart for the same purpose?

    Thanks!

  4. Hello,

    Thanks so much for this article, your site has a wealth of info keep it up !

    One question: How do we get this working for a subgrid?

    For example I open up a particular campaign and on the form I would like to see a graph for its related opportunity amount and related number of contacts

    Cheers,

    Stanley

  5. Your blogs are so helpful! Thanks so much for sharing your expertise with us. Regarding user views, I was successfully able to import contacts and open opportunities back into CRM as a user view. However, I was wondering if you have any advice to further narrow the search down in XML. For example, I might not want to pull in all of one user’s contacts, but perhaps all of the contacts that have an opportunity… or something of that nature. Is this possible to do within the XML? I should mention, I am not a developer, just an average user who organizes a lot of charts!! Thanks for any advice you have.

  6. I have the situation that I would like to sum some amounts, stored in a linked entity. I can not use the expression ” aggregate=”sum” distinct= “true” ” because some records have the same amount value, so they will be put together and not sum up separated. Any idea how I can come around this issue?

      • That is true, but as I know, you can not select a specific time period in a rollup field as you have the possibility in the advanced search regarding time selection (for example the records of the current year or something like that). Any thoughts on this?

      • True, you have to define the rollup filter around a specific date. There’s no “This Year” or similar, unfortunately. But you could do it after Jan 1 2015. Not ideal, but probably the best option at this time. Otherwise, the next best bet would be looking at creating the chart on the entity directly, so you can apply the filter, and then show it on subgrid, but that depends on your specific scenario.

      • Thanks for your answer. Probably I will use a custom field which identify if the record is in the current year or not. This one will be updated periodically so that I can assure the correct rollup selection.

  7. Is it possible to group by an attribute? For example on your campaigns example, grouping everything by month completion or due date or similar rather than the campaign itself?

  8. Hi,

    I’m pulling quotes and orders that meet certain criteria in filters on my chart XML. I’m using this chart with a user view that contains 3 users, and it’s telling me I’m over the record limit. I looked it up though, and these three users’ combined quotes and orders only add up to around 8,500 records. Any ideas on why I might be getting this message if the limit is 50,000?

    • Hi, I’m facing the same issue!
      I’ve got users in different territories and I only get this issue in one of the territories. The others get nice charts.
      I manage to get some data when using ‘inner’ instead of ‘outer’ but then ’empty’ users disappear from the chart (and I don’t want that).
      Any help on the topic is welcome 🙂

  9. I was created a chart between User and Case entities, Error message “the maximum record is exceeded. reduce the number of records.” appear to me.
    I know it is limitation in “AggregateQueryRecordLimit” and change it in MSCRM_CONFIG but this is Unsupported method.
    is there another way to resolve this problem?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s