Reporting on Activities

THIS IS WIP

Activity Entities

Microsoft Dynamics CRM has several entities for recording activities as shown in the table. It is possible to create custom activity entities. Each activity type has a code.

Activity	Type Code	Notes
Appointment	4201
Email	4202
Fax	4204
Case Resolution*	4206	Created when a case is closed
Letter	4207
Order Close*	4209	Created when an order is closed
Phone Call	4210
Quote Close*	4211	Created when a quote is closed
Task	4212
Service Activity	4214
Social Activity	4216	Tracks social posts, can only be created programmatically (not via the user interface)
Recurring Appointment	4251
Campaign Response	4401
Campaign Activity	4402
Bulk Operation*	4406	System operation for distributing campaign activities

*Special activities which are created automatically when another event occurs such as resolving a case

Underlying Tables

Although there are tables in the SQL database for each activity type, the only column in an uncustomized system is activityid.

Data for all activities is stored in the ActivityPointerBase table. Some columns in this table are only relevant to certain activity types.

Filtered Views

Each activity has a filtered view which only has columns that relevant to the activity type. So a filtered activity view has fewer columns than the ActivityPointerBase table.

There is also a filtered view that returns data for all activities, FilteredActivityPointer.

Custom fields added to an activity are shown in the filtered view for the activity but are not shown in FilteredActivityPointer.

To join FilteredActivityPointer with Filteredentity use the activityid field.

Dates

All activity types share the same set of date fields, but the name shown in the user interface varies by entity.

Field	Appointment	Task	... for other entities
Created On
Modified On
Actual Start	Defaults to creation time
Actual End	Defaults to completion or cancellation time
Scheduled Start	Start Time
Scheduled End	End Time	Due Date