Custom Reporting in Microsoft Dynamics CRM – Fetch vs. Filtered Views

Frequently we see customers that require reporting out of Microsoft Dynamics CRM that cannot be handled using the out of the box report wizard or advanced find. This is the first post of a series of blogs I will be posting around both reporting options as well as technical issues and cases we have seen and resolved within our customers environments.

When it comes to building SSRS reports for Microsoft Dynamics CRM 2011 using Visual Studio (Business Intelligence Development Studio, a feature that can be installed as part of SQL Server), there are two options available that provide you the ability to query and format your CRM data into flexible dynamic reports. The options are SQL reports querying the CRM Database Filtered Views or using Fetch, a proprietary query language commonly referred to as FetchXML, this language utilizes the CRM Report Authoring Extension that is to be installed alongside Visual Studio’s Business Intelligence Development Studio. In this post I will define the capabilities of each option as well as explore which option makes the most sense for the different deployment options within Microsoft Dynamics CRM. I will begin by explaining the differences between querying the CRM filtered views and FetchXML and discuss the different options available for the deployment options.

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:

 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.

Helpful Resources:

FetchXML

Filtered Views

Developing Fetch XML Based SSRS Reports

Thanks!

Sarah Champ

Microsoft Premier Field Engineer