CRM 2011 SSRS report - using non-FetchXML data source in sub-report

Question

I have a SSRS sub-report that uses an XML datasource which works fine in BIDS or when deployed and run from the SSRS server, however once deployed as a report in Microsoft Dynamics CRM 2011 it fails to run when called from CRM as SSRS throws the following exception:

Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. --->
Microsoft.ReportingServices.DataExtensions.XmlDP.XmlDPException:
The data extension supports Windows Integrated Security and No Credentials (anonymous request) only.

The parent report uses FetchXML to load some data which is then passed into the sub-report as parameters. CRM automatically passes through UserID/OrganizationID are credentials to any reports, which is correct since the credentials are required in order to call the FetchXML in the parent report, however judging from the exception it would appear to me that the sub-report is also (incorrectly?) inheriting the data source credential settings from the parent reports, despite the sub-report using an entirely different type of data source and credential setting.

If sub-report is published in CRM and is run by itself then it also works fine, but this doesn't meet business needs. The sub-report must be wrapped in the parent report in order to pass user selectable parameters through.

Are there any methods of overriding this behavior, perhaps some custom code in the sub-report that can force the XML data source to use the 'Do not use credentials' setting?

well, definitely a very interesting question. I think that the sub-report always inherits the main report credentials. There is something to stop you to create a standalone report that includes both parent and sub-report functionalities? — Guido Preite
I think I'd have the same issue if I had both XML and CRM data sources within the same standalone report, but I'll give it a try. — Alex Marshall

Alex Marshall Alex Marshall · Accepted Answer · 2013-10-08T14:56:53

After pulling my hair out for hours over this issue I eventually discovered that it seems to be an issue with CRM caching the initial data source credential settings (which must have been initially incorrect) from when the report is first uploaded. If you re-upload the RDL on top of an existing report it mustn't update some cache that it must maintain of credential settings.

Deleting the published reports then republishing the sub-report first (XML datasource), then the parent report (decided to go for SQL datasource rather than FetchXML to try and eliminate FetchXML as the issue) works as expected.

Lesson to learn when using CRM SSRS reports: try deleting the reports and trying again!

This is on CRM 2011 UR11.

CRM 2011 SSRS report - using non-FetchXML data source in sub-report

1 Answers