SSRS report in Dynamics CRM: Including required and optional attendees for appointment

Hi guys,

Here is another article for today where I want to highlight my learnings while creating an SSRS report in Dynamics CRM on the appointment entity. In particular, I want to show how we can fetch the required and optional attendees, those who are mentioned in the appointment record but are not readily obtainable from the underlying appointment table in SQL.

The report required the list of these attendees in a comma separated format as shown below:

The main issue was that required attendees and optional attendees are not directly present in the appointment table. The attribute type says it is a party list type. The fields are unique in that these lookups allow multi item selection and that too records of different types (users/contacts/companies, etc.). A bit of internet search and some figuring out later I understood that the required details are present in the activity party table. Each row in activity party has the corresponding activity id and details like the participation type mask, which indicates whether the party is required/optional (it has values for other types of parties as well like organizer, etc.) and the party object type code, which indicates the type of party (whether it is user/contact/account, etc.). The partyid would eventually be the ID of the contact/user record that is part of the activity. We need to obtain the contact full name from the contact table using this ID as the contact ID. Here is the query that I had used to get all the contacts in the required attendee list:

declare @requiredList nvarchar(max);

-- partyobjecttypecode 2 denotes contact and participationtypemask 5 denotes required attendee

 select @requiredList = coalesce(@requiredList + ', ', '') + fullname from filteredcontact where contactid in

(

select partyid from filteredactivityparty        

where partyobjecttypecode = 2

and participationtypemask = 5

and activityid = @appointmentId

)

The coalesce function aggregates the full name of the contacts and separates them with a comma.

In a similar manner, I aggregated the full name of all the required users, optional contacts and optional users. 

The whole SQL query used is pasted below:

declare @appointmentId nvarchar(40); 

-- Obtain the ID for the present appointment record in Dynamics CRM using the below code

select @appointmentId = activityid from filteredappointment as CRMAF_filteredappointment;

declare @requiredList nvarchar(max);

-- partyobjecttypecode 2 denotes contact and participationtypemask 5 denotes required attendee

select @requiredList = coalesce(@requiredList + ', ', '') + fullname from filteredcontact where contactid in

(

select partyid from filteredactivityparty        

where partyobjecttypecode = 2

and participationtypemask = 5

and activityid = @appointmentId

)

-- partyobjecttypecode 8 denotes user and participationtypemask 5 denotes required attendee

select @requiredList = coalesce(@requiredList + ', ', '') + fullname from FilteredSystemUser where systemuserid in

(

select partyid from filteredactivityparty

where partyobjecttypecode = 8

and participationtypemask = 5

and activityid = @appointmentId

)

declare @optionalList nvarchar(max);

-- partyobjecttypecode 2 denotes contact and participationtypemask 6 denotes optional attendee

select @optionalList = coalesce(@optionalList + ', ', '') + fullname from filteredcontact where contactid in

(

select partyid from filteredactivityparty

where partyobjecttypecode = 2

and participationtypemask = 6

and activityid = @appointmentId

)

-- partyobjecttypecode 8 denotes user and participationtypemask 6 denotes optional attendee

select @optionalList = coalesce(@optionalList + ', ', '') + fullname from FilteredSystemUser where systemuserid in

(

select partyid from filteredactivityparty

where partyobjecttypecode = 8

and participationtypemask = 6

and activityid = @appointmentId

)

select @requiredList as [Required attendees], @optionalList as [Optional attendees], ownerid, actualstart, scheduledstart, subject, location, description from FilteredAppointment

where activityid = @appointmentId

The last select query uses the results of the previous operations to return the required details for the report (for the present appointment record). They are then mapped to the fields in the SSRS reports which are then mapped to the report UI.

I would love to hear from you if you have a better way of achieving the above functionality. Thanks and happy SSRS reporting!!