Archive for category Campaign Chart

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>

, , ,

27 Comments

Advanced Campaign Performance Chart in MS CRM

The following out-of-the-box chart for viewing campaign performance in MS CRM is quite inadequate compared to the possibilities.

Out of box CRM Campaign Chart

It seems fine at first, but it is only showing the revenue generated by campaigns. However, campaigns in CRM are a lot more advanced and really lends itself to more advanced charts, which bring insight to campaign performance.

The campaign record includes the following fields of interest for campaign performance:

  • Estimated Revenue (the goal for the campaign)
  • Total Cost of Campaign

From the related Opportunities we get:

  • Won Revenue (or Actual Revenue)
  • In Progress Revenue (Est. Revenue from open Opportunities)

All put together in one chart, and using the same data records as in the OOB chart shown, it could look like this.

Custom Campaign Chart 2.0 in MS Dynamics CRM - custom xml

Similar to the standard goal chart, it has the familiar green and grey bars for “Actual Revenue” and “In Progress” values. Campaign cost and target revenue are shown behind it. Instantly the user can see, if the campaign has generated more revenue than its cost, how it is stacking up against its target, and how much more revenue might come. This should give the user a clear and instant view of campaign performance.

Note: In this blog post I am only using the bar chart and stacked bar chart types. Had I used column charts, we could have made something very similar to the standard goal charts. However, bar charts often utilize space better and are a lot more restrictive to work with, which means we have to get a little creative to get everything on one chart.

The chart contains four series; two Stacked Bar Charts and two regular Bar Charts.

To get started, I’ll create a View for the Opportunities and make sure to include the fields:

  • Total Cost of Campaign (from Source Campaign)
  • Estimated Revenue (from Source Campaign)
  • Name (from Source Campaign)

Save the View, select it, and open the chart editor.

Add the four series needed as Legend Entries and the name of the campaign as the Category.

Chart Designer for Custom Campaign Chart

Notice the values on the fields coming from the Source Campaign are averages and the fields from the Opportunity records are sum. The same Source Campaign record will appear multiple times, and using the average will ensure that we only get the value intended.

Time to save the chart and export the chart xml.

As with any multiple series chart, first check if CRM added any references to a secondary Y axis. If it did, remove these first.

NOTE: Full sample chart xml is available at the bottom of this post.

My first step is to rename all the aliases to something more sensible and easier to work with.

Second, I’ll add a filter on Actual and Est. Revenue to ensure that only Won Opportunities are counted towards the actual value, and only Open Opportunities count towards the Est. Revenue.

Campaign Performance Chart for MS CRM wiht new aliases and link entities and filter

New aliases are highlighted in the chart xml and the sections indicated are:

  1. Link to the campaign record with the cost and est. revenue fields
  2. Link to Opportunities and filtered to only include open opportunities
  3. Link to Opportunities and filtered to only include won opportunities

Now I have all the needed data in the chart, but it still looks like this.

Chart before XML is modified - CRM Chart XML Custom Campaign Chart

Now the real fun begins with editing the bars to match our needs.

I’ll make the following changes to the first two series for target and cost. These are the two bars I want in the background to indicate if the progress on sales is good or bad.

  • ChartType=”Bar”
  • IsValueShownAsLabel=”False” – values here would clutter the chart
  • LegendText=”Campaign Target” and “Campaign Cost” – so the legend is shorter and easier to read
  • Color, BackGradientStyle, BackSecondaryColor – to create the gradient on the bars
  • CustomProperties=”DrawSideBySide=False

The last item under the CustomProperties is important. It is the setting that draws the bars on top of each other, instead of next to one another. It may look like a Stacked Bar chart, however, it is not since the values do not build on each other as they do on a stacked bar. For example, if Cost is 20k and Target 100k, a stacked bar would be 120k high. Two bars with SideBySide=False would still only be 100k high.

For cosmetic reasons, I also decreased the PointWidth a little.

Here’s the xml for the series for target and cost.

Target and Cost series for the Campaign Performance Chart xml sample with CustomProperties

The Target is the first series, and the Cost is second. The bars are drawn in order, and since target is always higher, I want this one drawn first, and then Cost on top of that. Had it been in reverse, the Target bar would completely cover the Cost. I’ll make the safe assumption that the cost of the campaign is always lower than the revenue target.

The next two series, for actual revenue and in-progress revenue, I am more or less just copying the color settings found in the goal charts. Consistency in charts is important and the green and grey color shades are easily identifiable to the user.

In addition to some color and gradient settings, custom LegendText and some label formatting, I also made the following changes:

  • Set chart type to StackedBar   –  because here we do want the values to build upon one another
  • Reduced PointWidth and MaxPixelPointWidth under CustomProperties

By reducing the PointWidth and MaxPixelPointWidth I create a stacked bar that is thinner than the other two bars behind it. That enables me to have the cost and goal in the background, but wider, so you can still see them even if the revenue bar has surpassed the two.

Here’s the xml for the last two series. Actual Revenue and In-Progress Revenue.

Actual and In Progress series for Campaign Performance Chart in MS CRM

Here’s the final chart again.

Custom Campaign Chart for MS Dynamics CRM by CRM Chart Guy

Couple of ideas for changes or additions:

  • Add lost revenue on top of In-Progress, but make it even more transparent.
  • Gradually shade the grey In-Progress bar according to the stage in the sales process

Resources and associated posts

Hope you liked the post. Please sign up for the email newsletter or follow me on Twitter 

Sample Chart XML for the custom Campaign Chart

Here’s the full XML and it should work with a current CRM trial and sample data. Just add some data to the Estimated Revenue fields on the Campaign Records.

<visualization>
 <visualizationid>{A9B55234-37FD-E211-9BBB-B4B52F67E642}</visualizationid>
 <name>Campaign Performance Chart by CRM Chart Guy</name>
 <primaryentitytypecode>opportunity</primaryentitytypecode>
 <datadescription>
 <datadefinition>
 <fetchcollection>
 <fetch mapping="logical" aggregate="true">
 <entity name="opportunity">
 <link-entity name="campaign" from="campaignid" to="campaignid" link-type="outer">
 <attribute name="name" groupby="true" alias="campaign" />
 <attribute name="totalactualcost" aggregate="avg" alias="totalcost" />
 <attribute name="expectedrevenue_base" aggregate="avg" alias="goal" />
 </link-entity>
 <link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
 <attribute alias="open" name="estimatedvalue" aggregate="sum"></attribute>
 <filter>
 <condition attribute="statecode" operator="eq" value="0" />
 </filter>
 </link-entity>
 <link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
 <attribute alias="won" name="actualvalue" aggregate="sum"></attribute>
 <filter>
 <condition attribute="statecode" operator="eq" value="1" />
 </filter>
 </link-entity>
 </entity>
 </fetch>
 </fetchcollection>
 <categorycollection>
 <category alias="campaign">
 <measurecollection>
 <measure alias="goal" />
 </measurecollection>
 <measurecollection>
 <measure alias="totalcost" />
 </measurecollection>
 <measurecollection>
 <measure alias="won" />
 </measurecollection>
 <measurecollection>
 <measure alias="open" />
 </measurecollection>
 </category>
 </categorycollection>
 </datadefinition>
 </datadescription>
 <presentationdescription>
 <Chart Palette="None" PaletteCustomColors="149,189,66; 197,56,52; 55,118,193; 117,82,160; 49,171,204; 255,136,35; 168,203,104; 209,98,96; 97,142,206; 142,116,178; 93,186,215; 255,155,83">
 <Series>
 <Series ChartType="Bar"
 IsValueShownAsLabel="False"
 LegendText="Campaign Target"
 Color="0,25,25,112" BackGradientStyle="LeftRight" BackSecondaryColor="60,25,25,112"
 Font="{0}, 9.5px" LabelForeColor="59, 59, 59"
 CustomProperties="DrawSideBySide=False, PointWidth=0.65, MaxPixelPointWidth=40">
 <SmartLabelStyle Enabled="True" />
 </Series>
 <Series ChartType="Bar"
 IsValueShownAsLabel="False"
 LegendText="Campaign Cost"
 Color="0,255,165,0" BackGradientStyle="LeftRight" BackSecondaryColor="120,255,165,0"
 Font="{0}, 9.5px" LabelForeColor="59, 59, 59"
 CustomProperties="DrawSideBySide=False, PointWidth=0.65, MaxPixelPointWidth=40">
 <SmartLabelStyle Enabled="True" />
 </Series>
 <Series ChartType="StackedBar"
 IsValueShownAsLabel="True"
 LegendText="Actual Revenue"
 Color="112, 142, 50" BackGradientStyle="LeftRight" BackSecondaryColor="149, 189, 66"
 LabelFormat="$#,0,.##K" Font="{0}, 9.5px" LabelForeColor="59, 59, 59"
 CustomProperties="PointWidth=0.35, MaxPixelPointWidth=20">
 <SmartLabelStyle Enabled="True" />
 </Series>
 <Series ChartType="StackedBar"
 IsValueShownAsLabel="True"
 LegendText="In Progress"
 Color="170, 100, 100, 100" BackGradientStyle="LeftRight" BackSecondaryColor="110, 100, 100, 100"
 LabelFormat="$#,0,.##K" Font="{0}, 9.5px" LabelForeColor="59, 59, 59"
 CustomProperties="PointWidth=0.35, MaxPixelPointWidth=20">
 <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" Format="$#,0,K" 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" 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>

, , , , , , ,

6 Comments