Archive for category Related Entities
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:
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
In setups where both Users and Teams own records, it can sometimes be useful to group data by the owning Business Unit, rather than by the Users or Teams individually.
Most entities allow you to add an “Owning Business Unit” column in Advanced Find to take care of that, but some do not. Most notably the Activity entities, incl. Phone Calls, Appointments, etc. do not have this option. We can only get access to the “Owner”, “Owning User” and “Owning Team” from the UI. Regardless of which one we use, either we will get Teams separately from Users or we will only see the Business Unit related to either the Team or the User. This is also the case for the Opportunities entity.
Of course, the records still have an Owning Business Unit, and luckily, we can still call it in the chart xml directly.
Just copy the <link-entity> tags and the groupby attribute from a CRM chart xml that does support “Owning Business Unit” in the UI. Then use it to replace the attribute groupby line in your Activity chart xml.
Or just grab the code from here.
<link-entity name="businessunit" from="businessunitid" to="owningbusinessunit" link-type="outer"> <attribute groupby="true" alias="REPLACE ALIAS" name="name" /> </link-entity>
Just insert the alias from the original groupby attribute where it says “REPLACE ALIAS”.
Grouping directly by Business Unit allow you to see both User owned and Team owned records as one. That way you don’t have to create 2 charts or create multiple series that go through the “Owning User” and “Owning Team” fields to find the proper Business Unit.
Don’t forget to follow me on Twitter
Are we focusing on the right Accounts and Opportunities?
A common question in sales departments. Accounts are often classified in some kind of A, B, C categorization based on their potential value etc. Ideally, that means you sales activity should primarily be related to A accounts and some B accounts. However, a common setup in CRM, is to set your sales activities regarding an Opportunity.
So if I want to see if sales activities are focused on the right type of Accounts, I need a chart that show me all the Activities related to an Opportunity, but group the data by the A, B, C categorization on the Account. Something that is not possible in the MS CRM Chart Designer.
The tricky part here is that we now have two steps.
Activity -> Opportunity -> Account
Had we only had one step, it would have been fairly easy to do everything in the MS Dynamics CRM Chart Designer. For example, if you wanted an overview of your Est. Revenue in Opportunities based on the type of Account, all you need to do is add the field from the Account entity, to your Opportunities View in “Advanced Find”, save the View, and then use it when you open the chart editor. Thank you to Adam Vero for having brought that very helpful shortcut to my attention.
Since our scenario has two steps, we can’t use this trick. However, we can get Advanced Find to do the most of the dirty work.
In Advanced Find, from the Activities entity, create a filter that goes to the Regarding (Opportunity), to the Potential Customer (Account) and check if the desired field, Classification, contains data.
Save the View and download the FetchXML for later as it contains the needed link information in xml format.
Next I’ll create a chart in the chart editor that I can use as a base for my new chart xml. I want my final chart to be a pie chart grouped by the Account classification. Since I need to add that link in the chart xml, I’ll create a similar chart and use another field as a placeholder.
In this case the field “Priority” is the placeholder for the A, B, C classification.
I’ll export this chart and now I’ve got the pieces I need.
First I’ll take what I need from the FetchXML I downloaded from Advanced Find..
Highlighted are the needed lines with the <link-entity> information from Activity -> Opportunity -> Account, as well as the schema name for the Classification field, which we want to group by. We do not want to keep the filter information in this case.
Now that I have the link information, I’ll open the Chart XML and locate the groupby attribute line in the fetchcollection part.
I want the <link-entity> tags from the FetchXML to surround this groupby attribute line. Then the placeholder, the prioritycode, should be replaced with the “accountclassificationcode” from the Account entity.
Final version should look like this.
Import it back into CRM and use it on a View that only show Activities related to an Opportunity.
Hopefully that’ll get you a little closer to knowing if your Sales/Opportunity time is spent on Accounts that are worth it. In this case, more time is spent on B-Accounts than A-Accounts.
So, how many nested <link-entities> can CRM handle? Well, you can go at least 4 entities out. Beyond that, and probably sooner, you’ll hit a point there the chart just doesn’t make sense any longer.
Depending on the volume of records, this is an area where you can tell that these charts load a little slower. Only a few extra seconds, so enough to be noticeable, but it shouldn’t impact usability.
Thanks for reading and please follow me on Twitter
You are currently browsing the archives for the Related Entities category.
- Prefilter data for Power BI with OData URLs
- Setup the Data Export Service with a Power BI Solution Template
- Power BI and mixed Team/User Ownership in Dynamics 365
- Time to expand the scope of the CRM Chart Guy blog
- Crowdsourcing the ultimate Sales Dashboard for Dynamics 365/CRM
- Custom Date Formats in Charts in MS Dynamics CRM
- Charts on Forms, or User/Account Specific Dashboards
- Charting on Activity Parties in MS Dynamics CRM
- Bubble Charts in Dynamics CRM
- Funnel Charts Suck and You Shouldn’t Use Them
- Compare This Year to Last Year with a Dynamics CRM chart
- Annotations in MS CRM Charts
- Add Key Figures to Dashboards in MS Dynamics CRM
- N:N Relationship Charts in MS Dynamics CRM
- Add Percentage Labels to a 100% Stacked Bar chart in MS Dynamics CRM
- Display only complete weeks or months in charts
- Is your “Regarding” field on activities set correctly?
- All things Pie Chart in Dynamics CRM
- Multiple entities in one MS CRM chart
- Create a Gantt Chart in MS CRM
- Count Distinct or Unique items in MS CRM Charts
- Design options for bar charts and Top X bar charts
- Advanced Campaign Performance Chart in MS CRM
- LabelFormat Cheat Sheet
- Groupby Business Unit – even when not present (i.e. Activities/Opportunities)
- Groupby on a related entity’s related entity
- Everything ScaleBreak in MS CRM 2011 Charts
- Aggregate Total on top of Stacked Column Charts or Bar Charts in MS CRM 2011
- Removing Axis Titles, Legend and Axis Labels in CRM Charts for MS CRM 2011
- Specify Exact Colors for Each Priority in the Microsoft Dynamics CRM 2011 Case Chart
- Add insight to your pipeline with Advanced Goal Charts
- Include records with no value in charts!!!
- Using Custom Colors in Charts
- Order data based on a linked entity
- Order (or Sort) your charts
- Axes modifications – Part 1
- January 2017
- December 2016
- November 2016
- September 2016
- January 2016
- November 2015
- October 2015
- June 2015
- March 2015
- November 2014
- October 2014
- August 2014
- March 2014
- February 2014
- January 2014
- December 2013
- September 2013
- August 2013
- June 2013
- May 2013
- March 2013
- February 2013
- January 2013
- August 2012
- July 2012
- Activities chart
- Aggregate Total
- Bubble Chart
- Campaign Chart
- Case Chart
- Charts on Forms
- Colors in charts
- Count Distinct
- Display Zero Values
- Filter in chart XML
- Funnel Chart
- Gantt Chart
- Goal Chart
- Multi-entity charts
- Multiple Series
- N:N Relationships
- Order charts
- Pie Chart
- Power BI
- Related Entities
- Sales Charts
- Sales Dashboard
- Week Chart