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
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.