Posts Tagged crm2013 chart

Display only complete weeks or months in charts

Here’s an issue that can be rather annoying. You want to display activities by week. You set up your chart and have a view that includes data from the last 5 weeks.

MS CRM chart with incomplete week highlighted

Incomplete week highlighted in chart

The only problem is, that a view showing the last 5 weeks doesn’t show the last 5 weeks. It is showing you the last 5 x 7 days, or 35 days. If those 35 days end on a Thursday, your chart, showing you information by week, will only have data from two days of that week. In other words, the chart will (inaccurately) present you as a slacker that week. We all know that is not the case, so we will need to make sure that those charts have an accurate representation of weekly data.

To correct this, I will create a view with 6 weeks of data, but limit the chart to show only the last 5 weeks. That will ensure that all columns in the chart represent a complete week’s data.

Note: This approach also works for months. However, if your fiscal periods are aligned with months, a View with the last 5 fiscal periods, will actually be the last 5 full months. A view with the last 5 months, will be the last 5 x 30 days = 150 days.

First, I’ll create the weekly chart in the editor and then export the chart xml.

I’ll start by adding an order clause, and reverse the standard order. The descending=”true”, means the most recent week will now be the first item, rather than the last.

Add order clause to MS CRM chart xml. order alias="start" descending="true"

To remove the sixth straggler week, I’ll add Maximum=”5.75” to the Axis X. This will ensure that I am only displaying the first 5 items and not the 6th. This is why I had to change the order of the weeks.

Maximum added to X axis in the MS CRM Chart xml

Why set the maximum at 5.75 and not just 5. If set to 5, the maximum property would cut the chart exactly at 5, which would be the middle of the 5th column at the interval mark, highlighted below. If maximum was 5, only half a column would be displayed.

MS CRM Chart activities by week, displaying only complete weeks by modifying the chart xml.

Now we have a chart where all the weeks have a complete set of data. The exception of course, is the current week, which depending on the day might not be complete. That however, makes much more sense to the user.

If you want the oldest week to the left, add IsReversed=”True” to the X axis properties.

X Axis is reverased by adding IsReversed="true" to the xml for the MS Dynamics CRM chart.

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

, , , , ,

4 Comments

Is your “Regarding” field on activities set correctly?

MS Dynamics CRM gives you a plethora of options for setting your activities regarding a specific record. Most companies have guidelines on which entity certain activities should be set to. For example, an often recommended approach is to set activities regarding the opportunity it is about. However, if a sales person simply clicks on the track button in Outlook, rather than “Set Regarding,” and go through the process of selecting the right record, the email will be associated with the contact, not the opportunity. In the defense of sales people, clicking the track button does seem like the obvious thing to do.

In order to follow up on whether or not activities are set regarding to the correct entity, we can create a chart that show the count of activities per type, and which entity they have associated them with.

MS Dynamics CRM chart - Activities by regarding entity.

The chart tells us which type of entities the users are focusing on. There should be a healthy balance between activities on leads and opportunities depending on how you are using MS CRM.

Create an “Activities View” with the needed entities

First, open up Advanced Find and select the Activities entity and open Edit Columns.

Open the Record Type option set and scroll down to the section that begins with Regarding.

Add Columns with Regarding entity to MS Dynamics CRM for chart customization.

This is a list of all the entities that activities can refer to.

Add a column for each of the entities that you would like to include in your chart.

After you select a regarding entity, you have to select a field. Choose a field that you know always is populated. I like to use the field “Created On”.

View with Regarding: Created on field for each if the chosen entities in MS Dynamics CRM. CRM Chart.

To this view I have added the “Created On” field for Leads, Accounts, Contacts and Opportunities. Each column will be populated with the “Created On” date. The view in itself, is not very helpful, but the important part is that there is only data in the Account column, if the Activity is related to an Account. I cannot see which is which, but that is OK for now.

View with data Regarding Created On in MS Dynamics CRM.

Save your view and navigate back to the activities section in MS Dynamics CRM and open your new view.

Use chart editor to create the base

Open the chart editor and start adding the “Regarding” fields to your legend entries. If we had not added the fields to the view first, they would not have been accessible in the chart editor.

Chart editor with Regarding fields added to prepare for xml chart customizations in MS Dynamics CRM. CRM Charts.

Make sure that the aggregate is set to Count:Non-empty, so we only count the records that have a value in that field. This means the chart only counts for the field when the activity is regarding an entity of that type.

Set the category to either Activity Type or Owner depending on your needs.

Chart from editor before xml edits. Activity by entity and type in MS Dynamics CRM. CRM chart customizations.

Now we have a chart that shows us the amount of activities per type and the regarding entity.

Clean up chart xml

Unfortunately, not even the person who made it can decipher which series belongs to which entity, so we’ll export the CRM chart xml and do some clean up.

First, we now have the almost mandatory step of removing the secondary Y axis.

Find this sucker Y Axis Type Secondary MS Dynamics CRM chart customizations.in one or more of the series and delete it.

For good measure, although not required, remove the AxisY2 section.

AxisY2 section to be removed from MS Dynamics CRM chart. Customization in the chart XML.

Luckily we do not have to remember the order of how we added the different entities. That information is present in the <fetchcollection>.

fetchcollection for MS Dynamics CRM chart. Adjusted alias for each entity for CRM chart customization.

I’ve renamed the aliases in the example above so it is easier to read. If you do that, remember to update them in the <measurecollection> as well.

measurecollection for MS CRM chart xml. Alias renamed according to entity names.

Finally, in the series, add the LegendText so it will make sense to the user. The series follow the same order as the <measurecollection>.

Series with LegendText. Added to MS CRM Chart xml.

Import the chart back in to MS Dynamics CRM.

Final Chart after chart xml modifications. MS Dynamics CRM activities per entity and per type chart.

 

While the example used Activity Type as the category, this can easily be replaced with Owner, to see which entities users focus their activities on. It is also a very good indicator if users set the Regarding field according to the company guidelines.

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

, , , ,

3 Comments

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

Create a Gantt Chart in MS CRM

Until recently I thought that Gantt charts couldn’t be done in MS Dynamics CRM without turning to some serious development, which I found unfortunate because this is a popular type of chart and users find them easy to read. Turns out you can, and it’s not that difficult to create a simple Gantt Chart in MS CRM.

EDIT regarding CRM2011: This post relies on changing the aggregate of a date field to “min”, which seems to be exclusive for CRM2013. CRM2011 will give you an import error when you make that change to the xml. However, a reader has been kind enough to send me the details of how make it work for CRM2011. In the fetchxml you will need to remove the aggregate=”min” from each of the attributes, and also remove the groupby=”true” along with its corresponding alias in the categorycollection. Thank you Nils for sharing.

In this post, I’ll create a simple Gantt Chart for the opportunity entity, that shows when the opportunity was created and when the Est. Close Date is. It is a good visual indicator for how long the opportunity is planned to be open, rather than looking at the dates. After that, I’ll share some ideas for more advanced versions. Everything of course, is made with only xml modifications for standard MS CRM charts.

Here’s the simple version which I’ll create step by step in this blog post, showing Created On and Est. Close Date.

Gantt Chart in MS Dynamics CRM

Chart Editor – Create the base for the Gantt Chart

Since there is no Gantt Chart type in MS CRM, we have to use the standard bar chart instead and make some modifications to the xml so it looks like a Gantt Chart. What I am really doing is creating two bar charts, one bar to reach Est. Close Date, and then one bar to “cover” a part of the bar up till the Created On date. This post uses a custom Created On date field to use in place of the system Created On field, just so it’s easier to create demo data.

As always, I’ll use the chart editor to do most of the work.

Chart Editor set up for creating Gantt chart in MS Dynamics CRM

Note that the order of dates here are important. The first date on the chart, in this case Created On, should be on the bottom. The chart type is Bar, not StackedBar.

Let’s have a look at the result.

Chart based on setup in chart editor - step 1 for creating MS CRM Gantt chart

Basically this looks nothing like a Gantt chart. The chart is just counting the number of dates. That is because count aggregates are the only available options in the chart editor.

Chart XML edits

Let’s export the xml and make some edits.

  1. Change the aggregates to “min” (this is the secret sauce)
    aggregate min
  2. Add the custom property DrawSideBySide and set it to False
    DrawSideBySide false
  3. Remove the secondary Y axis
    3 Secondary Y axis delete Count Distinct

Let’s import it back into CRM and have a look.

Chart after first modifications - Gantt chart in MS CRM

Much better. We still need to do some xml modifications, but let’s take a look at what happened.

Changing the aggregates to “min” changed the Y axis to a date format, and the values relative positioning are correct in comparison to the dates. Very unlike a date grouping which only includes the values present in the data set on the axis. You get the same result using the “max” aggregate, so you can pick either.

The DrawSideBySide=False ensures that the two bars are drawn on top of each other, rather than next to each other. Looking at the image above, the blue bar, Est. Close Date goes all the way back to the X Axis, but a part of it is covered by the orange “Created On” bar. If a DrawSideBySide property is added to one series, it is automatically applied to all series of the same type, so we only need to add it on one of them.

Finally, we need to remove the secondary Y axis, because it’s not needed in this scenario (as usual).

Let’s make the next set of modifications to the chart xml. Change the color for the Created On series to white, by adding Color=”White” to the series.

Color White

White is also the background color and border color. This will make it appear as if there’s no second bar on the chart, and we only see that start and Est. Close Date for the opportunity. At the same time I’d recommend removing the series from the legend and/or modify the legend text to something more suitable.

Gantt chart in MS CRM 2013

Final Gantt chart in MS CRM 2013 showing Created On dates and Est. Close Dates for open opportunities.

Notes

If you need to force a specific start date on the Y axis, then this has be inserted as a number. The chart is using the same date number system as Excel where Jan 1 2014 is 41,640. So if we wanted the Y axis to begin on January 1 2014, we would insert Minimum=”41640″ in the AxisY properties. These numbers are easy to get by entering the date in Excel and then formatting the cell as a number.

The chart type RangeBar, originally seemed like a more suitable option to create a Gantt chart with, since it has two Y-values for start and finish. Unfortunately, the RangeBar chart does not allow a secondary date aggregate of “min” or “max”.

Advanced Options

Color code the whole bar

It is possible to add more series to the bar chart so you can color the bars differently depending on which phase in the pipeline they are, or you can group the estimated revenue into different categories. See this post for how to create and work with multiple filtered series in bar and column charts.

Gantt Chart for Opportunities color coded according to value

Gantt Chart for opportunities color coded according to estimated value

This chart was made creating 3 series, filtering the opportunities into different categories based on their estimated value, but could for example also be color coded according to current pipeline phase.

Divide the bar into multiple sections

We could also add more dates to the Gantt chart. Since it consists of a layered bar chart, we can have up to 9 different dates, which is the most allowed by the fetchxml. The fetchxml allows up to 10 attributes, one of which needs to be the groupby.

A Gantt chart for opportunities, where the end date for each stage in the sales process is registered in a separate field, could look like this.

Gantt Chart Sales Stages

Gantt Chart showing how much time was spent in each sales stage. Note that this was made only to show the capability of adding multiple date fields to the Gantt chart so each bar can have multiple sections. Further refinement on the chart above is certainly needed.

The user can then overview the progress on opportunities and how much time is actually spent in each phase and compare it to other opportunities. The tricky part is ensuring the dates are set in the right order in the xml, as the bars overlap each other.

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

IMPORTANT UPDATE: A lot of people are experiencing issues when creating this chart. This is due to MS Dynamics CRM, for whatever reason, no longer supports dates on the Y axis. You can get around it by adding this to the Y axis in your chart xml.

Format

And then optionally you can make the labels transparent. It forces Dynamics CRM to not render a date on the Y axis, which is where it fails. Now it just writes some text instead. That dates are no longer supported on the Y axis is very unfortunate. The Y axis is basically just blank. The best option is to keep the date on some of the labels so you at least can see them inside the chart. Hopefully they’ll come back soon, but who knows. Please write a comment if you see it working with dates again.

, , , , , , ,

69 Comments