For basic activity reporting, such as activity lists or viewing a count of
activities by type by user, consider using the out of the box views and charts.
For example, if I want to see the number of activities by type and priority,
I can just go to the Activity view in CRM and select the “Activities by Type and
If you are not familiar with creating charts in CRM 2011, check out Kevin
Wessels’ Introduction to charts in CRM 2011.
I recommend using the views and charts whenever possible, because it is fast,
it is dynamic, and it can easily be added to your CRM 2011 dashboard.
However, if you find you want a report that requires more advanced formatting
or that can be published to additional formats, such as Word or PDF documents,
another option is to create a SQL Server Reporting Services (SSRS) report.
The point of this post is not to introduce you to SSRS. If you don’t have
experience creating SSRS reporting, I recommend checking out resources like the
report development section of the CRM 2011 SDK.
The point of this post is to discuss the unique challenges of working with
activity data in reports. To understand how to work with activities in SQL
queries, you need to first understand how activity data is stored.
In the Microsoft CRM SQL databases, activity records are stored in an anchor
entity view called filteredactivitypointer. This view contains the fields that
are common to all activity types. This includes fields such as activityid,
scheduled start, scheduled end, actual end, actualduration, subject, and
description (the “body”). This entity also includes a field
called Activitytypecode. This indicates what type of activity the record
Any people, leads, or accounts linked to an activity will be stored in the
filteredactivityparty view. This is a related entity that enables you to add
multiple recipients/attendees/customers to activities. When you create an
appointment, for example, and have 3 required attendees, 2 optional attendees, a
regarding company, an owner, and a organizer, a total of 8 activity party
records are created. Activity party includes fields like partyid (the GUID of
the record related to the actity), partyobjecttypecode (the entity type code of
the related record), and activity id. The filteredactivityparty view also
includes some of the fields from the filteredactivitypointer view, such as
One important thing to understand about activities and activity parties is
that some activity parties are stored in both filteredactivitypointer and
filteredactivityparty. The ownerid and the regarding objectid are fields in
filteredactivitypointer, but they also are stored as activity parties related to
This is important to understand, because many people overcomplicate their
activity reports and views because they misunderstand this—if you want to see
any activities where you are the owner, the regarding object, or a
recipient/attendee, don’t do something like this:
select distinct a .activityid from filteredactivitypointer a inner join
filteredactivityparty p on a.activityid = p.activityid where a.ownerid = @user
or a.regardingobjectid = @user or p.partyid = @user
instead, do this:
select distinct activityid from filteredactivityparty where partyid =
Since all people or accounts related to an activity are stored in the
filteredactivityparty view, you can limit your query person filter to the
activityparty entity, and make your query more efficient. This is why the
default “My Activity” views in CRM filter the activity list by the related
activity parties, and they get not only the records owned by the selected user,
but also the records where that user is an attendee or recipient or regarding
As we discussed earlier, the fields that are common to all activity types are
stored in the FilteredActivityPointer Anchor entity view. These fields are also
available from the specific entity type views, such as FilteredEmail, Filtered
PhoneCall, Filtered Task, Filtered Appointment (and the poor lonely FilteredFax
These views also contain the fields that are unique to a specific activity
type, such as custom fields you might have added to the appointment entity, and
status reasons that are unique to the activity type (for example, there is a
“sent” status on e-mails, but not appointments).
So as you begin to design your report, there are a couple of questions you
1. What records do I want to return? Is this a report covering all activity
types, just one type of activity? Should this show all activity statuses, just
open/scheduled, just closed, or both?
2. How should this data be filtered? Do I want to see it for a single user,
a group of users, a set date range, or a date range selectable by user?
3. How will this data be consumed? Is somebody going to look at it on the
screen, in a dashboard, export to a spreadsheet, or print out a pdf?
1. Over complicating the query. If you just want to have a count of
activities by person, there is no reason to join activity party to activity
pointer and union together the filteredemail, filteredtask, filteredphonecall,
and filteredappointment views. Overcomplicated queries are the main reason why
activity reports are frequently slow or time out.
2. Misunderstanding how dates in activities work. There are a number of
date fields in filteredactivitypointer—scheduledstart, scheduledend,
actualstart, and actualend. No one of these date fields are consistently
populated on all activity types. For example, tracked emails will typically
just have an actualend date, and not a scheduledend or scheduledstart, and only
closed appointments have an actualend date. This is one of the main reasons why
dates in activity reports and views do not work as expected.
To account for this, I recommend using a case in your sql query to
accommodate various date fields being null. For example
(case when actualend is not null then
when actualend is null and
scheduledend is not null then scheduledend
end) as date
puts priority on the actual close date, so if the activity is closed, it will
count that as the date of the activity, but if not, will use one of the
It is also very important to understand what your audience expects to see as
the date of an activity—if they expect the date of an activity to be what it was
scheduled as on their calendar, but they close the activity two weeks after the
appointment, if you select actualend as the date of the activity in the report,
the results won’t match their expectation.
3. Misunderstanding how dates in SSRS report parameters in CRM work
Consider this common scenario—the user wants to enter a start date and an end
date and show all activities that fall between @start and @end. So, at first
glance, you assume that the report query should say something like this:
“where date between @start and
Not so fast. You first need to understand that when you populate a date
parameter in an SSRS report in CRM, it assumes the time of day when you are
running the report. So, for example, if you run the report at 3:30 PM on
January 5, 2011 and say start date is January 2 and end date is January 4, you
are really saying between January 2 at 3:30 PM and January 4 at 3:30 PM. Any
activities earlier in the day on the second and the end of the day on the fourth
will not be returned.
The right way to do it is to adjust your parameter values so the start date
is the beginning of the day and your end date is the end of the day. For
“Where date BETWEEN DATEADD(dd,
DATEDIFF(dd, 0, @start), 0) AND DATEADD(minute, 1438, DATEADD(dd, DATEDIFF(dd,
0, @end), 0)))”
In this example I am using dateadd to
set the @start parameter to the beginning of the day specified, and using
dateadd to add 1,438 minutes to the start time of the @end parameter. All
activities between these dates will be returned.
4. Lacking a clear focus
Once you start building your report, it can sometimes grow beyond the
original scope and purpose of the report. Users will want to add additional
fields and be able to group or sort by different fields to use the report to
answer different questions. This is fine, but be careful that in doing that you
don’t overcomplicate the report.
The best reports serve a specific purpose and are consumed in a specific
way. Reports that are exported to PDF should not have more fields than will fit
in one page width. If you keep adding fields to the report, your query will run
more slowly, and you may exceed the number of rows that will fit on one page.
Formatting of reports is driven by the way the report will be used. Column
layouts that look good in PDF don’t often look good when exported to Excel, and
Excel optimized layouts may not look good when exported to Word. If you try to
make the report exportable in all formats, you frequently will wind up with a
report that doesn’t look very good in any format
Also, many times the users want the report to handle every niche scenario,
so, for example, if there is a custom field that just exists on appointment
records, they will want to include it in an activity report. This small change
can make a big difference in report execution performance, as it means having to
join or union in the filteredappointment view in your query. If you have
additional custom fields on other specific entities, that makes it even slower.
Best practice is to limit your fields in the report to just the minimum
necessary for the main use case for the report, and if there are any entity
specific fields needed for other scenarios, you may want to build additional
reports that just focus on those activity types.