Creating an activity report which includes the related people

In an activity CRM grid, it is not possible to add attributes from the activity type (letter, phonecall etc) itself. The fields to and from on the entities phonecall, letter, fax are therefore not eligable for addition on the CRM grid. It would be very useful to see those though. The same is valid for the to, cc and bcc in email and required and optional attendees in appointments. In this post I won't be giving a solution to show the attributes in the grid, instead I will give a workaround by using reports.

The only attributes which you can select in the grid are the attributes which are belonging to the entity activitypointer. These include the activityid, startdate, statecode, but also the regardingobjectid. So the question is, how to get the to, from, cc etc. For this you can use the function which I have posted in my previous post. This function accepts an ActivityID and an ActivityPartyType. So what is this type? Look at this page: ActivityPartyType. You will find a list of values mapped to what kind of field you want to add to your report.

By using that function you can create your query for the report. An example would be:

SELECT
activityid, activitytypecode, scheduledstart, subject, owneridname, statecodename,
regardingobjectidname,
(SELECT DBO.fn_PGGM_GetActivityPartyList(activityid, 1)) [to],
(SELECT DBO.fn_PGGM_GetActivityPartyList(activityid, 2)) [from],
(SELECT DBO.fn_PGGM_GetActivityPartyList(activityid, 5)) [required]
FROM
filteredactivitypointer

This query does select some default attributes and it adds the regarding, to, from and required fields. Add this query to the generation of a report and you'll be set to go.

Note: make sure that the function gets added to your database and assign the correct rights. See the post around the function for details.

Happy reporting!