Fetch Vs SQl Based Reports

FetchXML
Fetch is a proprietary query language that is used in Microsoft Dynamics CRM. The FetchXML language supports similar query capabilities as a SQL query expression. Within Microsoft Dynamics CRM Fetch is used to save a query as a user owned saved view or as an organization owned view in the - the functionality behind Advanced Find, also utilizing the out of the box security structure. Fetch XML queries can actually be generated from within the Advanced Find tool within CRM as a great starting point for your reports data set.
When utilizing FetchXML to create SSRS reports in CRM, the query is sent to the web server to retrieve data from CRM database, permitting SSL only connections to the web server will protect data over the wire in the case of IFD and CRM Online deployments, additionally, this is the only option for creating custom SSRS reports in CRM Online.
SQL Database Filtered Views
Microsoft Dynamics CRM includes SQL database filtered views that are used for business data access, when these tables are queried the data in filtered views is restricted at three levels: the organization, the business unit, and the owner, therefore utilizing the out of the box security structure to limit the data that is returned. Filtered views exist for all Microsoft Dynamics CRM business objects (entities). They key difference between using the two to create your report is compatibility with CRM Online vs. CRM On-Premise deployments:
CRM On-Premise Deployments
If you are using Microsoft Dynamics CRM On-Premise version, the preferable option would be the CRM Database Filtered Views to create reports as using a T-SQL Query, making very complex reporting scenarios easier to handle and also offering a much more flexible development experience as there are no limitations. . That being said, FetchXML can be preferred, even in an On-Premise environment due to its optimal performance. When utilizing Fetch reports in CRM they use FetchXML queries instead of SQL queries, they do not need to use the filtered views in the Microsoft Dynamics CRM SQL database to retrieve data for reports. Since FetchXML reports no longer have to use filtered views in order to honor CRM security, the time that it takes to execute these FetchXML queries should decrease. Therefore if the custom reports you are looking to build fit within the limitations below, it may be worth evaluation.
CRM Online Deployments
The first thing to note is that you cannot access filtered views in Microsoft Dynamics CRM Online because access to the SQL database is not supported, therefore the only option is to use Fetch. One big win for report writers though is the ability to generate your FetchXML queries from Advanced Find and as mentioned performance is a key benefit to using Fetch reports. In regards to the limitations, Fetch introductions a few potential road blocks in development flexibility and complexity, those limitations include:

1. Fetch does not support RIGHT OUTER JOIN and FULL OUTER JOIN
2. Fetch does not support EXISTS/IN condition with sub-query/expression
3. An amount of 5000 returned records maximum
4. No “UNION” selects
5. You cannot specify group by / sum queries – You can only select the records in detail and then perform the aggregation in your report. 
6. Number of entity join (link) limitations
7. FetchXML reports cannot use non-CRM online data sources
8. Learning curve – for report writers that are not familiar with FetchXML the syntax is quite different from SQL.

Summary
The following table summarizes some of the key differences between these two reporting options:

	SQL Reports	FetxhXML Reports
Development experience	Requires a separate program for designing the report such as SQL Business Intelligence Development Studio (BIDS) or Report Builder.	These reports are also designed using BIDS (must download the CRM Report Authoring Extension).
Report writer skill set	Building SQL reports requires SQL Server skills and development experience.	Building SQL reports requires SQL Server skills and development experience, as well as FetchXML query knowledge. The Advanced Find tool can also be used to build FetchXML queries.
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 or Custom FetchXML can support, limiting the ability to return complex queries.
Data 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

 Ready to get started with custom reporting in Microsoft Dynamics CRM? As noted above, I will be posting a series of additional report related blogs, I have also added a few resources below for more information on Fetch vs. Filtered Views.