A guide to help you determine the best solutions for you.
Activity Reporting Gotchas in Dynamics CRM
One of the most common types of SSRS reports written for Dynamics CRM are activity reports. Given that many users track emails, tasks, phone calls, and tasks in CRM, activities are one of the best ways to track user adoption of CRM and verify how users are interacting with your customer (and other) relationships.
Activity reporting can be simple or complicated, depending on what you want to see in your report. You need to understand the structure of how activity data is stored in CRM, and how it differs based on different types of activity records. The following are the five most common gotchas of activity reporting in Dynamics CRM:
1. ActivityPointer vs. individual activity entities:
All activity data is stored in a common entity called Activity Pointer. This stores the fields like subject, description, regardingobjectid, and other fields that are common between all activities. However, it does not include all activity fields. If you want a report that includes all activity types, the easiest approach is to report form Activity Pointer; however, if you need some fields that are specific to one type of activity, you will need to join the individual activity entity views, like email, phone call, or appointment.
2. Status/Status Reasons
One of the complexities of #1 is that the Status and Status Reason values are different in Activity Pointer and the individual activity entities. If you need to show the status of the activities in your report, you will need to join to the individual activity entities.
3. Regardingobject vs.ActivityParty:
When creating activities it is best practice for users to “set regarding” on the activity to the primary related record. However, reality tells me that many users don’t do this. Do an Advanced Find of the Activity entity and display the regarding field. Chances are that 50% or more of your Activity records don’t contain a regarding object.
The more accurate way to report on your activities is to use the related activity parties rather than the regarding object. Instead of doing a select of activitypointer where regardingobjectid = @contact, do a select of activitypointer where related activityparty partyid = @contact. This will include all activities where the contact is the regarding record, as well as where the contact is a recipient or sender on an activity that does not have a regarding object set.
A very common requirement in activity reporting is to have the results filtered by a date range. Sounds easy, right? it can be, but you need to define exactly what that means. The dates populated on activities differs based on the type of activity. Some activities like Email are created on the day they happen, other activities, like appointment, are created and scheduled for a future date (scheduled start date). So using the same data field for your filter with all activity types will not give you consistent results. Run an Advanced Find of activities and include the scheduled, actual, created, and modified date fields. Observe what fields are completed for each type of activity and plan what type of dates to base your report filter on.
5. Regarding Related Rollups
So you want to show all activities related to an account—easy, right? Just get activities where regardingobjectid = accountid.. However, when the user compares the result with the activity view in CRM, she notices that not all activities appear in the report.
This is because the activity pane in CRM not only shows the records directly related to the account, it also include related regarding records, such as activities regarding a child contact, opportunity, case, or subaccount. To duplicate this in a report is probably the most complex type of activity report to write. You will need to join or union together all these entities and include the parent account id, then filter on where the regardingobjectid = account id or parentaccountid.
The point of this post is to document the most common hurdles that new report writers face when building activity reports for Dynamics CRM. Now that you are aware of these potential issues, you can plan for the report, and set proper expectations around what will be included in your report.