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.

Reports Not Working After Upgrade to CRM 2011

I have worked on various upgrades and none have really had issues with the reports not working. I make sure that the SRS Data Connector is installed without fail. 


When you install CRM with the SQL server being separate from the CRM server, the setup does not automatically prompt you to install Reporting Services Extensions. You need to manually copy the  SrsDataConnector folder found in the CRM install directory to your SQL server. Run SetupSRSDataConnector.exe from there. This will prompt you to select the SQL server and the Reporting Server instance and go ahead and install all the reports for you.


In my case this was already done and yet the Sub-Reports did not work. The main reports would work but the moment we clicked on Show More Details that would require the sub-report to be executed, it would throw an error "Details could not be displayed as the sub-report could not be found". The Event viewer recorded the following error


Report render failure. Error: The item '/SharedReports/5.0.xxxx/{6177ee54-f42e-e011-a793-001ec9bbc670}' cannot be found. (rsItemNotFound)


Checking the Report Server using http://server/Reports showed that though the sub-report was present in that folder it had a different Guid than the one shown in the event viewer.


Since this was an upgrade, I thought it might still be using CRM 4 Guids and publishing the reports again would probably fix the issue. I found "PublishReports.exe" in the tools folder of CRM Installation directory. 


Executed that exe from command prompt using publishreports.exe uniqueorgname


It showed that it published some reports but that did not resolve my problem :(


I then came across this post that helped me reset the setting in MSCRM_Config database .


There is a setting in MSCRM_Config --> Organization table "AreReportsPublished". You need to set the value of this attribute to 0/False. This will be get the PublishReports to publish the reports once again.


After making this change, executing the PublishReports.exe tool helped in get my reports working again!


No matter how many upgrades you work on.... each one is a different case :)

UR8 released and UR9 "Beta" now available!

UR8 for Dynamics CRM 2011 was released a couple of weeks back. Note UR8 does not include the Cross-Browser and other features. UR8 is only a release of patches to the existing CRM 2011 application that includes hotfixes.

You can download UR8 from http://support.microsoft.com/kb/2600644

Within a few weeks from the release of UR8, UR9 has been made available for public beta. You can check Girish Raja article for more details on how to subscribe to the beta. Go get your beta copy right now!

Read Optimized Form made available through UR7

After installing UR7, there are chances you may not find any changes with respect to the much talked about Read Optimized form. That is because the default setting for RO is switched off and by default all forms are expected to open in Edit mode.

You need to make the change at Organization level using the Settings Dialog.

You can either have the forms open by default in Read Optimized (RO) mode or Edit mode. If you choose RO, all forms for all users would open in RO unless individual users have overridden the default to set their own preferences using the individual options available to each user.

Note if you uncheck the Allow users to select a mode for viewing forms, you can force the users to follow the organization level setting enforced by the administrator. The above option will then not be available on individual user settings form.

The individual user settings always take preference over Organization settings.

So what is a Read-Optimized form?
This form will display the record in read-only mode this means that user will not be able to edit the details on the form. The idea behind this being, most often users access existing records for viewing the details rather than editing them.

The benefit of RO form is that it loads much faster than an Edit form. It is much like the Print Preview Option that was always present in CRM. When you did a print preview it would show up a window with all the details laid out just as it is on the form.

The trade-off however has been that it would now not include or support the following
1. Left navigation links missing
2. Ribbon Missing
3. Web Resources cannot be included on the form, if included the form will not open in RO even if user preference is set to RO form for viewing.
4. If you have scripts included on the Onload event then again the forms would not open in RO view until the scripts are removed from the onload event handler.

Here is what the url now looks for Read Optimized Form. A new flag &rof has been provided that lets you provide the RO setting. True would open the form in RO view if it is possible i.e it does not include scripts or web resources that prevent the form from opening in RO view.

http://crm5:5555/orgname/main.aspx?etn=account&pagetype=entityrecord&id=%7b78D41C71-C62F-E111-974F-00155D000F03%7d&rof=true

The above link would open the account form in RO mode even if the users individual setting was set to Edit mode.

If the &rof flag is not included in the URL then it will open based on the default View settings for the user.

What if you would want to check the activities that were earlier available in left nav links on the form? Add them as a sub-grid on your form and the RO view would list them out



You can open the activities to view the details and then these would also open in RO mode. But you are not allowed to create new activities. The buttons are missing.

If you notice the notes pane, it lists the notes but you cannot download the attachments or add new notes.

If you had Webresources that you wanted to include on the form even in RO mode? You include that instead as an IFRAME. In the example below... we have picked up the web resource URL and copied that in the IFRAME.


The URL to the Webresource should include the Organization name if you are providing relative url.
/orgname/WebResources/new_/Maps.html

You can design multiple role based forms in CRM and the Read-Optimized mode will apply to all of the forms defined. If a user has access to more than one forms, just like before it would by default open the last viewed form by the user in RO view.

There might be probably more that comes up once we start looking into this deeper and we will make sure to come back and update this based on the new understanding of the RO concept...

IFD Configuration

While setting up IFD, we had issues in enabling the claims for CRM 2011. After trying to enable claims for CRM 2011 through Microsoft Dynamics CRM 2011 Deployment Manager when we trid to browse the URL: https://XYZ.com/FederationMetadata/2007-06/FederationMetadata.xml we were getting one of the following errors:

- HTTP 503 Service Unavailable error.
- HTTP 404 Not Found error.

When "FederationMetadata/2007-06/FederationMetadata.xml" is requested through the browser CRM website, it has the rule that rewrites the URL to handlers/Federation Metadata.ashx. So when one gets such types of error then it means IIS URL Rewrite module is not rewriting the URL correctly.

The solution for this issue is to re install the IIS URL Rewrite.

If the reinstallation of IIS URL Rewrite does not resolve the issue. The Issue could be due to Reserved URL. To find the Reserved URL follow the below given steps.

a) Open Command Prompt.
b) Type the Command NETSH HTTP SHOW URLACL.
c) This command will show all the reserved URL's.
d) If you get any reserved URL with error as given below.

Reserved URL : https://ABC:443/
Can't lookup sid, Error: 1332
SDDL: D:(A;;GA;;;S-1-5-80-2246541699-21809830-3603976364-117610243- 975697593)

e) Then we have to delete each URL for which we get such type of error as given above using the below command. Please provide the full URL for which you get the error in the command Prompt.

netsh http delete urlacl url=https://ABC:443/

f) After that again run the command to check if the reserved URL is still having an error message through command NETSH HTTP SHOW URLACL.

g) Repeat the step (e) for each URL for which you are getting the error.

h) After deleting all the Reserved URL through the Command,do IIS RESET on CRM server.

i) Configure CLAIMS again using Deployment Manager on CRM 2011.

j) Now try to access the federation Url “FederationMetadata/2007-06/FederationMetadata.xml"again.

We have refereed the blog http://blogs.msdn.com/b/emeadcrmsupport/archive/2011/05/13/we-receive-http-errors-while-accessing-the-crm-federationmetadata-url.aspx to resolve the issue.

The above listed steps should resolve the issue.