Solutions to issues with Reports in Dynamics CRM 2011

OK I am having a real hard time getting reports to work after installing CRM in recent times. This is an attempt to compile all SQL reports related errors and solutions that have worked for community in general. I have not checked each of the solutions but since I am experiencing issues with reports pretty often I am create this as a quick reference source. The solutions and errors have been picked up from queries posted on community forums and other blog posts.

1. Reports dont work after upgrade to CRM 2011 not even in the new organization added.

I have a separate post on that. But if that does not resolve your problem. It could be that the CRM 4 reports are still being referenced and the new CRM 2011 reports are not being published. The reports do not work in any of the CRM organizations. The Reports are stored in Shared Reports\5.0 folder on Report Server. You can go ahead and delete the Shared Reports folder and then execute the publishreports.exe to publish the reports.

2. Reports do not work with the following error recorded in the log files.

You can access the log files at Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\LogFiles

Cannot create a connection to data source 'CRM'. ---> Microsoft.Crm.Reporting.DataExtensionShim.Common.ReportExecutionException: Immediate caller DOMAIN\crm has insufficient privilege to run report as user S-1-5-21-1756635634-....

There are a couple of solutions available for this.

Open Reporting Services Configuration Manager and uncheck the credentials provided in the Execution Account

Another solution available on this blog.

3. Another common reason for reports not working is the user running the reports is not a part of the following groups

4. Check your Datasource settings in SSRS.

There are 2 datasource created within each CRM organization as well in the Shared Reports folder.

MSCRM_FetchDataSource - This is used by Fetch based Reports

MSCRM_DataSource - This is used by SQL queries based Reports

This is not a comprehensive list and suggestions to include other problems/solutions related to reports in this post are most welcome.

Note: These solutions should be used at your own risk.

Reporting through BIDS in CRM Online 2011

In the CRM 2011 online if you want to create your custom SSRS report then here is the trouble, you cannot create the report directly using the SQL Queries as you do not have access to SQL Server. For this reason MS has provided BIDS tool for Dynamics CRM 2011. You can download this tool from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=64a09b75-8376-4b9d-aea3-8a83a1837c4d

Using the BIDS tool, you can connect to Dynamics CRM Online service and use the CRM FetchXML queries to generate the queries required for the report.

To create the SSRS report for the CRM 2011 Online first you need to run the tool “CRM2011-Bids-ENU-i386” on the machine where you have installed the SQL Server Business Intelligence Development Studio.

After installing this, a new Data Source type gets added in the “Embedded connection type” option named “Microsoft Dynamics CRM Fetch”. To create the new report with fetch XML for the CRM 2011 you need to select this data type as shown in the below screenshot.

In the Connection string you need to provide your CRM 2011 online url and the org unique name, where the org unique name is optional and in the permissions section you need to provide the credentials of the CRM user.

In the SSRS report the main things are the Parameters, Query/Dataset and Design. Below is the explanation.

Parameters: You can simply defined the parameter as it is, but if you want to use the CRM filtered parameter and show this reports on the CRM grid or on the selected record, then you need to use the enableprefiltering in the fetchXML of this parameter. You can write the default fetchXML for this as follows.

Query/Dataset: Main thing in the Query is to pass the condition from the parameters. Generally in report the structure is that you have main-report and a sub-report, where you pass the parameter values of the main-report to the parameter of the sub-report.

Here we take an example to understand how it works in this scenario. For example: suppose we want to see all the contacts that is modified by a certain user, we want to run this report on the CRM user form and see the list of contacts.

For this purpose, we have created a Main report where we have a parameter named “@CRM_FilteredSystemuser” which is passed from CRM and it creates a dataset from this Parameter with three columns.
Fullname
Internamemailaddress
Systemuserid

The query for this dataset is shown in the above screenshot.
Note: If you have used the CRM parameter then it should be hidden.

Now we need to create the sub-report which contains the list of the contacts, in the sub-report we have a parameter named @Userid, and the value for this parameter is passed from the Main Report. Now the Query for sub-report with condition is as shown below.


Once you execute the Query, the dataset will be generated. From there on you can go ahead and design the report like any other normal SSRS report.

Once designed, you can upload this report to CRM and run it through the CRM User entity form.

Through BIDS it is now really easy to design reports for CRM Online 2011. Good knowledge of FetchXML is required. However there are tools available that would let you convert/design FetchXML queries through Query designer interface.