SQL based Report in CRM 2011 using SSRS - Sample Report User Summary - Part 2

Please refer the following link for the first part.

SQL based Report in CRM 2011 using SSRS - Sample Repoort User Summary - Part 1

http://crmdm.blogspot.com/2013/09/sql-based-report-in-crm-2011-using-ssrs.html

In the first part we examined the User Summary Report provided in CRM 2011. The next part is to modify the report based on a scenario.

Scenario:

We would like to see the User summary report with user's details and corresponding activities owned by the user. Activities should be categorized based on the activity type. A count of activities should be displayed against each user.

So we have FilteredSystemuser -- Filtered view which holds user's profile

FilteredActivityPointer -- Filtered view which holds a user's activity or task details.

Relation:

SystemUser  to ActivityPointer ( One to Many )

So lets see how could we do this.

From the downloaded report we have the following query. And we need to alter this query based on our scenario.

Declare @SQL nVarchar(4000)

SET @SQL = 'SELECT role.name, 
    cast(systemuser.systemuserid as nvarchar(50)) as systemuserid, 
    fullname, title, internalemailaddress, address1_telephone1, systemuser.businessunitidname, systemuser.businessunitid

FROM FilteredSystemUser AS systemuser
    LEFT JOIN FilteredSystemUserRoles AS userroles on systemuser.systemuserid = userroles.systemuserid
    LEFT JOIN FilteredRole AS role on role.roleid = userroles.roleid
  where  domainname is not null and domainname <> '''' and accessmode <> 3
ORDER BY systemuser.businessunitidname, fullname '


EXEC(@SQL)

In this scenario we do not need any role information. So we could remove that part and add the query bits to include activity details.

Our New Query would be

SELECT COUNT(Activity.activityid) AS ActivityCOUNT,Activity.activitytypecodename AS ACTIVITYTYPE, 
cast(systemuser.systemuserid as nvarchar(50)) as systemuserid, 
    fullname, title, internalemailaddress, address1_telephone1, systemuser.businessunitidname, 
    systemuser.businessunitid

FROM FilteredSystemUser AS systemuser
    LEFT JOIN FilteredActivityPointer AS Activity on systemuser.systemuserid = Activity.ownerid 
where  domainname is not null and domainname <> '''' and accessmode <> 3
GROUP BY Activity.activitytypecodename,systemuser.systemuserid,fullname, 
title, internalemailaddress, address1_telephone1, systemuser.businessunitidname, 
systemuser.businessunitid

ORDER BY systemuser.businessunitidname, fullname 


"The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns" ( Ref: http://www.w3schools.com/sql/sql_groupby.asp )

In our case we need to have a count of activities based on the activitytypecodename ( For instance, E-mail, Task, Fax etc). We are looking for activities owned by each user in our CRM.
The reason for LEFT JOIN -- Some users may not have any activities, but still we need to list the user profile.


1. We need to apply this query to our core data set of User summary report, DSSystemusers as shown below.

2. The next part is to alter the report design. We need to remove the existing column grouping ( based on roles ) .And  need to bring the column grouping based on activity type

Say 'Okay' and press 'Okay' button

3.  In the Data area of the matrix, we need to display the activity count. So Choose the ActivityCount as shown below.

4. Now lets add the new Column group. Right click on the Data section and Add Column group as shown below.

5.  Choose the Activity Type as show below. We don't prefer a group header in this case as it could display dynamic values.

6. Lets preview the report. We could see some symbols. Symbol were used in the downloaded report. So we need to change it.

7.  Choose the Text box properties as shown below.

8. We could see something like this.

We need to change to some fonts, say Arial

We need to do the same change for Data section as well.

9. Now let's preview the report.

10. To make it more readable we could make the Activity type font to align vertical. Its a property called Writing mode

11.  Lets preview the report.  

12. The last recommended change is if there are no activities, lets display the Activity type as ' No activities'. We could easily identify the users who doesn't have any activities. This change could be done in Activity Type Text box with an expression.

Expression used:


=IIf(Fields!ACTIVITYTYPE.Value ="","No Activities",Fields!ACTIVITYTYPE.Value)

13. And our final Doughnut is here.