Dynamics CRM: On-demand or on-premises? Consider your reporting requirements

Companies willing to implement Dynamics CRM 2011 must decide whether to deploy it in their own infrastructure (on-premises) or to implement it at Microsoft’s dynamics.com cloud offering (on-demand). Normally people consider that both solutions go on-pair with each-other and that the bottom line decision should be based on cost.

There is however a tiny caveat that if not considered, can become quite a nuisance: Reports.

Dynamics CRM 2011 support reports based on two data query languages: SQL Query or FetchXML. All of the default out-of-the-box reports that comes with Dynamics CRM 2011 are based on SQL Query. However, when using Dynamics CRM 2011 on-demand, you can only upload reports based on FetchXML.

What does this means in practical terms? Suppose that you want to make some changes to the default Quote Report that comes with Dynamics CRM. Doesn’t matter what type of change, be it the background colour of a table cell or adding new fields to the report, once you try to upload your new report field into Dynamics CRM 2011 on-demand, you will get an error. In fact, if you simply download a report file from Dynamics CRM 2011 on-demand and attempt re-upload it with another name, you will get the same error.

The only solution in this case would be to change the WHOLE of the report data query from SQL to FetchXML. This task can take from hours to days depending on the complexity of the report and your requirements.

Note: Gareth Tucker wrote a nice article on how to get started with FetchXML for Dynamics CRM on-demand, which you can read here.
Update 1: Another article on how to get started with FetchXML-based reports, by the Microsoft Dynamics CRM Team, can be found here.
Update 2: Gonzalo Ruiz wrote a post in his blog comparing SQL vs. FetchXML reports, which you can read here.
Update 3: Microsoft’s Dynamics CRM team wrote a post in their blog explaining how to personalise reports for Dynamics CRM Online, which includes fetching user settings such as currency, language and date format. You can read the post here.