Converting a SQL report to FecthXML


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:

image

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:

image

image

image

First step is to create a new fetch data source:

image

image

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:

image

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

image

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

Comments (3)

  1. Nick says:

    Cold beer to the person that shows me how to do a UNION statement in FetchXML…

  2. marcsc says:

    Can you describe the scenario that you want implement in a FetchXML based SSRS report?  There may be another way besides UNION to accomplish it.  However, I'm not sure I have an answer without more details.  As you pointed out, FetchXML does not have UNION functionality.

  3. John Klemetsrud says:

    You have to do your UNION per say, inside the report. Make your fetch return for one dataset, then make your fetch for another data set, create 2 grids, remove the header from the second one, match em up…basically a union…well a UNION ALL, ya, a union would be nice.