Advanced Reporting with SSRS
More often than not, enterprises require custom reports built on CRM data. You can build custom reports using a SQL Server Reporting Service (SSRS) report editor such as Visual Studio.
|Author's Note: As a best practice and to save time, Microsoft recommends copying and modifying an existing report rather than creating new reports from scratch.
Although you can create custom SSRS reports by directly querying the CRM database, that's not recommended. Instead, CRM Database exposes a set of views that are pre-filtered for report generation purpose. Using CRM Database views, you don't have to understand the CRM entity model completely; you can leverage the views (see Figure 3
) to generate reports quickly.
|Figure 3. Filtered Views: Use existing CRM Database views to simplify report generation.|
Follow these steps to create a report from an existing one:
- In Workspace → Reports, click on the report that you want to extend.
- Click Edit Report.
- In the report dialog, under Actions, click Download Report.
- Download and save the report, then rename it to a meaningful name.
- Create a Report Server project in Visual Studio, and add the saved report to the project.
- Modify the connection string information in the data source appropriately for your environment.
Now you can start customizing the report. As an example, the following query returns the list of Accounts in the CRM database.
select [name] from FilteredAccount
To store the newly created report in CRM, click New from Workspace → Reports and set the Report Type to "Existing File." Specify the location of the report in the File Location field, and save the report.
Now you are ready to run the custom report.
|Author's Note: As a best practice, Microsoft recommends editing the RDL files to write custom queries against the CRM database because Visual Studio takes more time to refresh report items bound to the dataset. It saves time to bypass the query designer and write the query directly in the RDLs CommandText tag.
While modifying the existing report, note that the wizard defined a set of report parameters (in the report parameters dialog). CRM uses a set of special parameters for features such as drill through and filtering. Note that parameters prefixed with CRM_
are treated as hidden by default in CRM, and users will not be prompted to enter them. The wizard creates the following special parameters for the specific purposes defined below:
- CRM_FilterText: The default filter is the initial value for this parameter. The parameter value is displayed in the report summary text box present at the report header.
- CRM_URL: This parameter is used for drilling through to CRM from the report. For example a report on the Account entity can provide a link on the account name and clicking the link will take you to the details page of the record for editing.
- CRM_Filtered<Entity>: These parameters are displayed in the "filtering criteria" section of the report and you use them to specify pre-filtering conditions. You can use multiple CRM_Filtered<Entity> parameters such as CRM_FilteredContact and CRM_FilteredAccount in a report. It is important to note that the queries need to be suitably formatted for the pre-filtering criteria to work. For example, the query to return the Account names should be modified to:
DECLARE @SQL nvarchar(4000)
SET @SQL = '
SELECT [name] FROM ('+@CRM_FilteredAccount+') AS FA'
Alternatively, you could write the same query as:
SELECT [name] FROM FilteredAccount AS CRMAF_FilteredAccount
Filtered views aliased as CRMAF_FilteredEntity
are automatically picked up for pre-filtering and don't need the pre-filtering parameters defined.