Sunday, July 24, 2011

SQL vs. FetchXML reports in CRM 2011

When it comes to building reports for CRM 2011, there are two options available: SQL reports or FetchXML reports. Both make use of SQL Reporting Services and require the SSRS Data Connector to be installed to interface with CRM. In the following table I am attempting to summarize the differences between these two reporting options:

SQL Reports FetxhXML Reports
Building experience Requires a separate program for designing the report such as SQL Business Intelligence Development Studio (BIDS) or Report Builder. CRM comes with a Report Wizard which can be used for building these reports. The report wizard is a web report designer integrated with CRM.
These reports can also be designed using BIDS (must download the Report Authoring Extension).
Skill set Building SQL reports requires SQL Server skills and development experience. When built using the Report Wizard, advanced CRM users can have the skills to build reports (or super-users) without requiring a developer.
Flexibility These reports can take data from CRM and present it in multiple ways. Reports can achieve complex requirements as you can use any feature from SQL Reporting Services. Functionality is restricted to what the Report Wizard can support which can be quite limiting at times.
Queries Data is queried using SQL statements that read the filtered views in the organization database. FetchXML queries are used for retrieving data for these reports (Advanced Find can be used to generate FetchXML queries).
Reporting mechanism These reports can be scheduled, delivered by email and other mechanisms. Must be executed on-demand.
CRM Online Support Not supported Supported

8 comments:

  1. One thing you don't mention is the performance difference between the 2. Fetch-based reports will actually perform better than queries against the filtered views.

    The queries in the FilteredViews that enforce security (out of necesity) are not very efficient as they must check for all of the different access levels (Global, BU, Parent:Child, User) on the entity.

    By contrast, the Fetch query generates a SQL query that only checks for the access level that user has on the entity resulting in a better optimization path. With large volumes of data, this difference can be significant.

    - Matt

    ReplyDelete
  2. Good point Matt, thanks for sharing!

    ReplyDelete
  3. One thing I found here is that FetchXML charts buillt using the UI also has a 50,000 record limit for aggregation whereas the report builder seemingly doesn't.

    ReplyDelete
  4. I use SQL Server Builder 2.0 and it's tough to modify Fetch XML from dynamics. I wish it was easier like straight SQL. I appreciate the article.

    -Joe

    ReplyDelete
  5. Hi Gonzalo,
    I have a question regarding making reports using ssrs 2008. I have a report with four parameters and one of them is linked to postcode table which has more than 7000 data. so when user chooses all of postcodes in parameter, report will fail. is there any solution for this problem? I use filtered views in report, should I use another views?
    Sara

    ReplyDelete
  6. Hi Sara,

    I suggest you use the CRM Forum to ask this question and provide more details on your problem, such as what is your query and the exact error message you get.

    ReplyDelete
  7. Hi Gonzalo and Matt,

    Another reason for poor performance of filtered views is we cannot apply indexes to that tables...

    ReplyDelete