Archive for August, 2014

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 

, , , , ,


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

, , , ,