Archive for category Activities chart

Charting on Activity Parties in MS Dynamics CRM

The Activity Party entity surfaced a little in the MS CRM blogosphere late last year.

First Jukkan wrote this excellent piece “Your Interaction Network in Dynamics CRM”

And then Joel Lindstrom and George Doubinski talked a little about the challenges of reporting on the activity parties in episode 14 of the CRM Audio podcast called “SSRS is Dead to George”.

While you can chart on activities with the chart editor and Excel, there are two questions you cannot answer without some xml customizations. I will use appointments as an example throughout this blog post although you can apply this to any of the activity types.

The two questions are:

1. How many people are included in each appointment?
Count of Attendees per Appointment - MS Dynamics CRM

2. How many appointments are CRM users included in?
Count of Appointments per User - MS Dynamics CRM Chart

This information is stored within the activity party table, which keeps track of all the contacts, users etc. that you add to the required and optional fields on the appointment form. It also keeps track of the owner and organizer.

A user can create their own appointments, but they can also be required attendees in other users’ appointments. That means you can have two or more users involved in a sales meeting, but in a normal chart, only the owner of the appointment would get “points” for the sales activity. If you are having regular sales meetings with multiple participants or if you use appointments to keep track of who attended what event, it can be useful to know how many people were actually involved in each of the appointments.

101 on Activity Parties

While I do urge you to read Jukkan’s blog on activity parties, here’s an ultra-short 101 in case you didn’t.

The Activity Party works as a manual N:N relationship between any activity and the people participating and what their participation role is.

If user John Smith is the owner of an appointment, and a required attendee, he will have two activity party records between his one user record and the one appointment record.

The official documentation on activity parties can be found here. ActivityParty entity details on MSDN

How many people per appointment chart

We need to create and download two pieces of xml before we open up Notepad ++ and start our chart xml editing.

1. A base chart we can build on.

Attendees Base Chart in MS CRM

2. A FetchXML from Advanced Find which includes details on the attendees we want to include.

Advanced Find link to activity party in MS Dynamics CRM for chart customization

In Advanced Find, select the participation types you want to include.

Participation Types selection in Advanced Find

Download the FetchXML.

Download Fetchxml button in Advanced Find

Open the FetchXML and find the <link-entity> section. Highlighted below.

Fetch downloaded from Advanced Find in MS Dynamics CRM for chart customization

Export and open the chart xml from the base chart and get ready for some editing.

In the xml for the base chart, locate the line with the aggregate in the fetchcollection. As usual I have renamed the aliases to something sensible.

Original fetch from the base chart with aggregate line highlighted

Now we need to edit the fetch, so that the <link-entity> section downloaded from Advanced Find goes inside the fetchcollection and that the aggregate ends up inside that section.

MS CRM Chart fetchcollection modified with link to Activity Party entity.

Note I did some other changes to the aggregate too.

  • Replaced name=”statecode” with name=”partyid” which refers to the attendee record. The statecode came from the field selected in the chart editor.
  • Added distinct=”true”. We only want to count people once.
  • Aggregate was changed to countcolumn so we only include records with a value.

Import the fetch and have a look.

Count of Attendees by Appointment - Final chart after customizing the chart xml in MS Dynamics CRM


How many appointments per user chart

Not all sales people are lone wolves. Some are pack hunters and go to meetings together. However, in standard CRM charts, only the owner of the appointment will get “points” for attending. We need to make sure all users are accounted for, and of course make sure that each user is only counted once. No double dipping by putting yourself in both the owner and the attendees’ fields.

The process is very similar as above, but we will need to create an extra <link-entity> from the activity party to the user entity so we can group by it.

Again, build this in Advanced Find and download the FetchXML. Include a lookup to the users to ensure only users are counted.

Advanced Find - by User - additional link to User entity

Modify the fetchcollection in your chart xml to include the added <link-entity> to the users.

Appointments per User fetchxml modified. Dynamics CRM chart customization

Other changes:

  • Aggregate is activityid rather than partyid since we are now counting appointments, not participants
  • Groupby was moved to the new link-entity section and is now on the user
  • Aliases updated to fit this scenario better

Import the chart and you’ll see a count of appointments per user.

Count of Appointments per User chart in Dynamics CRM - Chart customization XML


Bonus Chart – Count by any Party

Since any party is worth counting, we can skip the additional link-entity and group directly on the partyid. By doing this you get a mix of record types involved in the appointments so you can compare participation between Accounts, Leads, Contacts and Users.

Bonus chart for coounting the number of appointments for all party types. Fetchxml modified for import to a Dynamics CRM Chart.

Here is the chart after import.

Bonus Chart - Count of Appointments by all party types. MS Dynamics CRM Chart customization.

This is clearly candidate for a Top X chart. Read more about those here.

Thanks for reading. Please sign up for my newsletter 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 

, , , ,