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. 

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,

Custom Reports in CRM 2011 using SSRS- Tips

As you all know there are mainly two types of Custom reports could be developed by using SSRS ( SQL Server Reporting Services ) 

•  SQL- based ( Microsoft MSDN says "For security reasons, you cannot deploy custom SQL-based reports to Microsoft Dynamics CRM Online") 

•  Fetch - based. ( Works in both On-Premise and Online versions)

Limitations of Fetch- based Reports:

The limitations of fetch-based reports are well explained in the following MSDN Blog post.

Reference:

"http://blogs.msdn.com/b/crminthefield/archive/2012/11/27/custom-reporting-in-microsoft-dynamics-crm-fetch-vs-filtered-views.aspx"

"

1. Fetch does not support RIGHT OUTER JOIN and FULL OUTER JOIN
2. Fetch does not support EXISTS/IN condition with sub-query/expression
3. An amount of 5000 returned records maximum
4. No “UNION” selects
5. You cannot specify group by / sum queries – You can only select the records in detail and then perform the aggregation in your report. 
6. Number of entity join (link) limitations
7. FetchXML reports cannot use non-CRM online data sources
8. Learning curve – for report writers that are not familiar with FetchXML the syntax is quite different from SQL." (Ref:http://blogs.msdn.com/b/crminthefield/archive/2012/11/27/custom-reporting-in-microsoft-dynamics-crm-fetch-vs-filtered-views.aspx)

Now if you start developing SQL- based report, please never forget the following tips.

"Filtered views exist for all Microsoft Dynamics CRM entities, including custom entities. Your custom SQL-based reports cannot read data directly from the Microsoft Dynamics CRM database tables. Instead, you must use the filtered views to retrieve data for your custom SQL-based reports."(Ref:http://msdn.microsoft.com/en-us/library/gg328467.aspx)

"SQL-based reports in Microsoft Dynamics CRM use the filtered views provided for each entity to retrieve data for the reports. Filtered views are fully compliant with the Microsoft Dynamics CRM security model. When you run a report that obtains data from filtered views, the Microsoft Dynamics CRM security role determines what data you can view in the report. Data in filtered views is restricted at these levels: the organization, the business unit, the owner, and at the field level."(Ref: http://msdn.microsoft.com/en-us/library/gg328467.aspx)

In simple words, when we use Filtered views for SQL- based reporting, CRM implements the same security model into our report, which is really cool.

Custom Fetchxml based Grid Report ( With Matrix control ) in CRM 2011 using SSRS OR Custom Grid Report in CRM 2011 using SSRS

Please refer the following links for basic steps.

Custom Fetchxml based Report in 

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

How to Deploy a custom Fetchxml Report in CRM 2011

http://crmdm.blogspot.com/2013/09/how-to-deploy-custom-fetchxml-report-in.html

This post is regarding a custom fetchxml report which uses the report control ' matrix' OR a grid report in simple words.

"Matrix is a report tool component which displays aggregated data in a grid that has a variable number of columns and variable number of rows"

Lets understand grid report with a sample scenario.

Scenario 1: There are different kinds of system Jobs in CRM 2011 ( For instance Workflow, Bulk delete Bulk email etc ). The user would like to see an overview of these system jobs.  So System jobs  should be displayed in a grid. The grid should have the Column group as Year and System Job type as row group. The count of System jobs should be displayed in the grid for the corresponding system job type and year.

Scenario 2: There are different kinds of system Jobs in CRM 2011 ( For instance Workflow, Bulk delete Bulk email etc ). The user would like to see an overview of these system jobs.  So System jobs  should be displayed in a grid. The grid should have the Column group as Year and System Job type as row group. The grid should have a further column grouping based on the status of the system job. The count of System jobs should be displayed in the grid for the corresponding system job type,year and status of system job.

So lets get the ball rolling.

Scenario 1:

1. Here is the fetchxml Query. The System Job entity is  called 'asyncoperation'

It has an aggregate function to find the count of system jobs. It has multiple grouping. One grouping is based on System Job type and other one based on Year.

<fetch distinct='false' mapping='logical' aggregate='true'> 

   <entity name='asyncoperation'> 

       <attribute name='asyncoperationid' alias='COUNT' aggregate='count'/> 

       <attribute name='operationtype' alias='SystemJobType' groupby='true' /> 

       <attribute name='createdon' groupby='true' dategrouping='year' alias='year' />

   </entity> 

</fetch>

2. Dataset is shown here.

3. We could see the selected fields in the Data set as shown below.

4. Select the matrix control from the tool box and drag it to the Design area as shown below.

5. Its possible to re-size the matrix. And then double click on it.

6. Lets put the row group first. So in our case its System job type. This can be done in 2 ways.

Either we could drag System job type to the Rows OR select the Row cell of the matrix and then a tiny box will appear in the top right corner of the Rows as shown below. So click on it. Lets select System Job Type.

7. Secondly we could put the column group. In our case its Year. Lets drag year value to the Columns as shown below.

8. And the next item in the Matrix is Data. Lets put System Jobs count value into that.

9. Lets change the first cell description as System Job Type Vs Year. (Optional)

10. Here comes the end of fist scenario.

To deploy this report in CRM 2011 please refer the following.

How to Deploy a custom Fetchxml Report in CRM 2011

http://crmdm.blogspot.com/2013/09/how-to-deploy-custom-fetchxml-report-in.html

Scenario 2 :

1 Lets alter the fetchxml query as per the requirement in scenario 2.  It should have an additional column group for status of the system job. So we need one more grouping based on the status.

2.  So now we have status field added to our dataset as shown below.

3. We need to select the column group and then right click and then Add Group --> Child group.

And choose Status. In this case first level column grouping is based on Year and the next one is based on status.

4. So now our matrix includes the status. Please note that the status is added to column groups.

So first group it by year and then status on the column level.

5.  We could put a description for the status as shown below.

6.  And here is our apple pie.

7. To deploy this report in CRM 2011 please refer the following.

How to Deploy a custom Fetchxml Report in CRM 2011

http://crmdm.blogspot.com/2013/09/how-to-deploy-custom-fetchxml-report-in.html

How to Deploy a custom Fetchxml Report in CRM 2011

Please refer the following link for - Custom Fetchxml based Report in CRM 2011 using SSRS

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

Lets see how to deploy a simple custom fetch xml report in CRM 2011.

1. Navigate to the report section in CRM 2011. Click on New.

2. Choose the report type as Existing file.

Navigate to the file location where the report project was saved.

Choose the .rdl file, in this case its SystemJobCountBasedonType.rdl

Description: optional

Related Record Types : Choose this if the report is to be run on a particular entity type record. In this case its fine because its just an overview sample report. Click on Save and Close

3. Newly created report could be found in the reports sections. Choose the report and double click on it. We could see the report in the report viewer as shown below.