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.

ActivityType CodeNotes
Appointment4201 
Email4202 
Fax4204 
Case Resolution*4206Created when a case is closed
Letter4207 
Order Close*4209Created when an order is closed
Phone Call4210 
Quote Close*4211Created when a quote is closed
Task4212 
Service Activity4214 
Social Activity4216Tracks social posts, can only be created programmatically (not via the user interface)
Recurring Appointment4251 
Campaign Response4401 
Campaign Activity4402 
Bulk Operation*4406System 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.

FieldAppointmentTask… for other entities
Created On   
Modified On   
Actual StartDefaults to creation time  
Actual EndDefaults to completion or cancellation time  
Scheduled StartStart Time  
Scheduled EndEnd TimeDue Date 

Leave a Comment

Your email address will not be published.