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.

Example:

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