Scenario:

“I want to move to CRM Online, but I wrote all my SQL Server Reporting Services (SSRS) using the SQL Data Source.  How do I convert them to use FetchXML?”

Below I have three reports:

All three reports will look identical to the end user in the end.

facilityFetch.rdl – Written using the “Microsoft Dynamics CRM Fetch” Data Source available after you install the Microsoft Dynamics CRM 2011 Report Authoring Extension.

facilitySql.rdl – Written using the “Microsoft SQL Server” Data Source.

facilityConvertedSqlToFetch.rdl – Identical to facilitySql.rdl to start, but will be identical to facilityFetch.rdl once converted.

The key to a quick conversion is getting the results of the fetch query to look IDENTICAL to the sql query.  Basically, what you need to do is go through all your Datasets and update them with a matching fetch query.  In my example, facilityConvertedSqlToFetch.rdl DataSet1 uses a local Data Source called sqlds:

First step is to create a new fetch data source:

I tend to develop locally for developer productivity so I am pointing a local CRM 2011 deployment in my VM.  The format for the connection string is [root];[orgname].  Now that we have the fetch Data Source created, we need to create a matching fetch query.  My favorite way of going about this is here.  So let’s update the Dataset:

Make sure you update the Data Source to use the new fetch data source and replace the query with the right FetchXML query:

Again, the KEY here is that the shape of the Dataset is the same AND the column names are the same.  Save & Preview your new report.  It should “just work.”  For more advanced FetchXML scenarios, see Build Queries with FetchXML.

@devkeydet