How do I deploy SSRS reports to CRM 2011 Online that contain SQL Server DataSources and T-SQL Queries?

Question

I'm creating custom SSRS reports for a client that uses CRM Online. However, I'm not able to deploy reports that have SQL DataSource connections to them. I get the following error:

"Invalid Data Source. This report type is not supported. Microsoft Dynamics CRM Online supports only reports that use Fetch XML data sources."

However, this error is somewhat untrue. Reason: When I download other custom reports from their CRM, the DataSources are all SQL connection strings with T-SQL queries.

So, there MUST be a way to deploy these reports. From within the client's network perhaps? I've not been able to find any details online thus far. Help?

Answer 1

I'm afraid there is no solution. CRM Online will only allow you to upload fetchXml based reports. There is no workaround (that I have heard of at least).

Are you sure that the other reports you downloaded are definitely custom (CRM allows some system reports to be SQL based) or if custom do they actually work (nevermind how they got there in the first place!).

Answer 2

Yes it's possible with couple of work around.

You need to create your data source for SQL and upload it on your CRM server. Do you have SSRS services running on your CRM server ? if yes then there would be 2 data sources available. One for CRM data source (fetch XML) and another for SQL server.if it's not there you need to create it .

Once you have those data source you are good to get your data in your SSRS report. Make sure your data source name should match with your SSRS report data source. (I have on-promise environment for my CRM 2011)