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 |
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.
ReplyDeleteThe 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
Good point Matt, thanks for sharing!
ReplyDeleteGood Info..
ReplyDeleteOne 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.
ReplyDeleteI 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.
ReplyDelete-Joe
Hi Gonzalo,
ReplyDeleteI 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
Hi Sara,
ReplyDeleteI 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.
Hi Gonzalo and Matt,
ReplyDeleteAnother reason for poor performance of filtered views is we cannot apply indexes to that tables...