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

This post is regarding SQL based report in CRM using SSRS.

It is worth to refer the following if you need the basics.

Custom Fetchxml based Report in CRM 2011 using SSRS

http://crmdm.blogspot.com/2013/09/custom-fetchxml-based-report-in-crm.html

Custom Reports in CRM 2011 using SSRS.
http://crmdm.blogspot.com/2013/09/custom-reports-in-crm-2011-using-ssrs.html


It would be good if we follow the principle "First things first". Lets try to understand the concept of SQL report from CRM itself. Dynamics CRM provided us some sample reports to learn. Lets start the base right from there.

Scenario: User Summary Report in CRM.

Also we could find : How to open a CRM record from the SSRS report ?, How could we pass parameter to a SSRS report?

We are going to download the provided User summary Report in CRM and trying to understand how its done and analyse the key things done in this report. We could use these tips when we develop our own custom reports. 

1. In CRM Main Application left navigation, choose Workplace -> Reports-->Select User Report and click on the Edit button found on the ribbon.

2. Actions --> Download Report as shown below.

3. Lets Create a new project called 'SQLBasedReports'

For basics please refer the following link:
http://crmdm.blogspot.com/2013/09/custom-fetchxml-based-report-in-crm.html

4. We could add the downloaded User Summary report as shown below.

5. And then open the report by double clicking on it.  The report design is shown below.

5. The first change we need to do is the Connection to CRM ( Datasource ). Lets change the name to 'MyCRM' and provide our connections.  

data source=localhost; // This represents the SQL Server name

initial catalog=Adventure_Works_Cycle_MSCRM // This is the CRM DB

So we need to change it accordingly.

6. Please note that there are 3 different data sets in this report. 

UserInfo-- This data set is used to retrieve the current user full name

DSSystemUsers -- Core part of the report. It retrieves the user details

DSNumandCurrency -- To retrieve the formats in CRM.

We are going to examine each of these.

7. UserInfo -- This query brings the fullname of the user by passing the GUID of the user as shown below.

select fullname 
from FilteredSystemUser 

where systemuserid = dbo.fn_FindUserGuid()

Query executed from SQL Server Management Studio

8. Now we are going to examine our core query.

DSSystemUsers

In SQL Server Management Studio

We could see a parameter here @CRM_FilteredSystemUser . Lets find out how this value is supplied.

In the Data set properties, go to Parameter section. There is a parameter defined for the query

There is no change required. We are trying to understand how the expression was defined.

The value will be - FilteredSystemUser -- FilteredView which stores user details.

Lets give a try with the query with the real value.

Test query looks like 


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)

Please note:

"The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match." ( Ref: http://www.w3schools.com/sql/sql_join_left.asp )

In our scenario, user may not have a role. This possibility is well managed in the query.

9. The last data set is DSNumandCurrency -- To retrieve the formats in CRM.

Query used --select * from dbo.fn_GetFormatStrings()

10. Now lets analyse the report design.

There are 4 Row groups 

• full name 
• title
• email
• phone

And one Column group based on

• name ( Here it is role name. For instance, System Administrator)

11. When a user clicks on the full name, the report opens the corresponding CRM user record.

In other words,

How to open a CRM record from the SSRS report ?

Open the fullname text box properties as shown below.

Text box properties --> Action and click on the expression as shown below.

We could see the following expression

=IIF(IsNothing(Parameters!CRM_URL.Value), System.DBNull.Value, Parameters!CRM_URL.Value & "?OTC=8&ID={"& Fields!systemuserid.Value.ToString() &"}")

This means that we are passing the GUID of the System user. Also there is a CRM_URL value which holds the CRM URL value. OTC- Object Type Code
Accounts -1
Contact -2
Systemuser - 8  etc.

So please keep in mind that in another scenario we need to modify this expression accordingly.

12.  Here is a sample run of the user summary report.

13. Its possible to change the colours of the report as per our preference.

For instance,