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?
2. How many appointments are CRM users included in?
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.
2. A FetchXML from Advanced Find which includes details on the attendees we want to include.
In Advanced Find, select the participation types you want to include.
Download the FetchXML.
Open the FetchXML and find the <link-entity> section. Highlighted below.
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.
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.
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.
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.
Modify the fetchcollection in your chart xml to include the added <link-entity> to the users.
- 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.
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.
- Aggregate is on activityid again as we are counting the number of activities.
- Groupby is on the partyid
- Order clause has been added to sort the chart
Here is the chart after import.
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 Follow @crmchartguy
9 thoughts on “Charting on Activity Parties in MS Dynamics CRM”
Once again thank you for letting me mooch on your work. My recent offer about coffee has now been expanded to include cake. There will be coffee and cake if you drop by my office in Denmark.
The charts really adds value and gives much better information about activity participation.
Oh, cake was included in my last offer as well. We’ll add chocolate then…
Thanks again for this post. I tried the same technique on other entity which is custom entity in our system. I copied the fetch xml and used it in chart XML but import gives me error “The specified field does not exist in Microsoft Dynamics CRM”. Is there any way to pinpoint which field is causing the error and why am I getting this error since I’m only using fields from fetch-xml?
I had the same issue. Not sure to what field it’s referring or how to fix it.
Did any of you find a solution to the “The specified field does not exist in Microsoft Dynamics CRM” error?
[…] Update 2016-02-20: For further reading on how to make use of the activity party data, head over to the CRM Chart Guy’s blog post Charting on Activity Parties in MS Dynamics CRM. […]
Thanks for this post. I did the same as you described. It’s working fine. However, it is not possible to select from the chart with “select field”. Is it due to the related relations? Is there a way to insert this possibility? Many thanks for your answer
This is an excellent post, thanks so much. In our company we also specify a meeting type on appointments (e.g. presentation, entertaining etc.). Do you know if it’s possible to create this chart as a stacked bar/column to also show the breakdown of appointments for each user by meeting type? I’ve tested several different amendments to the chart XML but getting a variety of different errors when trying to re-import.
This is an excellent post, as always.
What if you want to aggregate a specific field instead?
Instead of counting the number of meetings, I would like to have the sum of the scheduleddurationminutes field for each participant?
I played around with the XML file, but no luck so far.