CRM 2011 provides users with the ability to define custom reports using the CRM interface.

These reports can then be saved and surfaced through CRM itself- a feature that was also available in a previous version of CRM.

The reports are SQL Server Reporting Services 2008 reports but the user interface for defining the reports while being user-friendly can sometimes be limiting and more complex reports may not be possible. Also the colour scheme and general layout of these reports are set by CRM.

An alternative to defining the reports through the CRM interface is to create the reports externally in Visual Studio and upload them into CRM. This provides the possibility to define more complex queries and layouts in the reports but a number of guidelines must be followed when developing reports in this manner.

Filtered Views

Traditionally when developing SSRS reports the dataset where your SQL query is defined either retrieves data directly from one or more SQL Server tables or calls a stored function/procedure which in turn returns the data.

With CRM it is unsupported to directly access the underlying tables where the data is stored.

CRM stores the structures and relationships for each entity in two tables – a base and an extension table. For example the out of the box Account entity is stored in the SQL Server database as:

• AccountBase

• AccountExtensionBase

The AccountBase table holds the out of the box definition and the AccountExtensionBase table holds any custom attributes that are added to the account entity. CRM exposes a filtered view for each entity in the system. These SQL Server views are managed by the CRM platform and should not be changed manually. However, they can be directly used in SQL queries from your custom SSRS reports.

Creating Joins Between Filtered Views

Joins between filtered views can be defined as you would traditionally define them. CRM uses GUIDs (Global Unique Identifiers) as the datatype for any of its ID fields. These are stored in the SQL Server database as a Unique Identifier datatype. So for example to query the contact entity and find the parent account for these contacts we could define the following SQL query in our report:

select fc.contactid, fc.firstname, fc.lastname, fa.name

from FilteredContact fc

inner join FilteredAccount fa on fc.parentcustomerid = fa.accountid

This returns a table similar to the following:

Creating The Custom SSRS Report

In order to create your custom SSRS report and surface it within CRM you must use either Visual Studio 2008 or the Business Intelligence Development Studio that is bundled with SQL Server 2008.

• Start by creating a new Reporting Services project

• Create a new report within your project

• Define a shared datasource in the project – The name of the shared datasource must be MSCRM_DataSource. This is because when you install CRM it installs the SSRS Reporting components and defines its own shared datasource called MSCRM_DataSource. You can use this datasource in your reports. 

• Set the connection string of your shared datasource to be something like Data Source=<SERVER NAME>;Initial Catalog=<CRM Organisation Name> - This will be just for testing as your report will use the shared datasource in CRM when you upload your completed report.

• Define your dataset by using SQL queries and reference the filtered views as mentioned earlier.

• Build and test your report.

• Finally, upload your report into CRM as follows:

In CRM go to “Workplace” ? “Reports”

Click “New” on the ribbon bar

Instead of defining a new report within CRM change the Report Type to “Existing File”

Click “Browse” and select the .rdl file from the bin folder of your Reporting Services project

Give your report a name and click “Save and Close” to upload the report

The new report should now appear in the list of reports in your CRM instance

Security Roles

As the filtered views also enforce the security model of CRM you must ensure the users that will be running the reports have sufficient read privileges to the entities used in the SSRS reports. These are defined in the security roles in CRM. Each CRM user will have one or more security roles granted to them.

So for example if a custom SSRS report queries data from the Contact and Account entities (via the filtered views called FilteredContact and FilteredAccount) the user that will be running the report must be granted read access to the Contact and Account entities in their security role in CRM.