Dynamics CRM ActivityPointer Master / Detail Report

In the following post I am going to explain how I created a Master / Detail Report Using Report Builder 3.0 and Dynamics CRM 2011.  This report is also known as a Header and Detail Report.  I am going to use the FilteredActivityPointer (ActivityPointer) table to display all activities for each Incident (Case).

1)      Start by creating a blank report in Report Builder.  Create your Data Source and then your Data Set

I have used the SQL following for my dataset

SELECT top 100

FilteredActivityPointer.activitytypecodename,
FilteredActivityPointer.subject,
FilteredActivityPointer.description,
FilteredActivityPointer.statuscodename,
FilteredActivityPointer.createdbyname,
FilteredIncident.ticketnumber,
FilteredIncident.title,
FilteredIncident.customeridname,
FilteredIncident.responsiblecontactidname,
FilteredIncident.createdon
FROM         FilteredActivityPointer INNER JOIN
                      FilteredIncident ON FilteredActivityPointer.regardingobjectid = FilteredIncident.incidentid
where activitytypecodename != 'Case Resolution'

2)      Add a List to the report

3)      In the Tablix Properties page.. select the dataset to be used

4)      At the bottom of the Report Builder Applciation window, Right Click Details and Select Group Properties…

5)      On the Group Properties window, choose to Add a Group Expression on the general tab.

I have chosen to add ticketnumber

6)      You are now free to add your Text Fields and your corresponding Tablix.

The Name, Date, Case Number and Case Title are Text Boxes.

The table was created using a Matrix.

The final result should be similar to the following.  Here you can see the Activities for each Case where the Case details are the Master and the Activities are the Slave / Detail information.

 If anybody wants to know how to do the Activity Icons – Leave a comment or contact me on twitter and I will be more than happy to oblige.

Tony