Dynamics CRM in the Field

Information from the Microsoft Dynamics CRM PFE team working in the field

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

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

Rate This
  • Comments 5

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:

 

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.

Helpful Resources:

FetchXML

Filtered Views

Developing Fetch XML Based SSRS Reports

Thanks!

Sarah Champ

Microsoft Premier Field Engineer

 

  • Great Stuff

  • Looking forward to using FetchXML

  • Be carefull, FetchXML as well as Advanced Find has severe limitations when it comes into related entities. Example from your boss that will make you swet: List me all Contacts we should invite to theater (=belong to marketing list "likes theater", except for those who attended our golf tournament (=not belong to marketing list "attendee golf event 2012").

  • Good stuff, Sarah! Thanks!

  • Thanks for clarifying these matters. Unfortunately, the reporting limitations in CRM Online due to only FetchXML being available and SQL reporting not being allowed are a big deal and not at all obvious, in some cases until it's effectively too late (we got rid of our on-premises CRM and moved to cloud without being made aware of this severe shortcoming). Does MS have any plans for improving on this situation for future releases of CRM Online? Will FetchXML be extended to have greater feature parity with SQL reporting or could the database backend be re-architected to allow SQL reporting to be offered in future in a way that would not pose security and performance risks the avoidance of which are the apparent rationale for these limitations?

Page 1 of 1 (5 items)
Leave a Comment
  • Please add 7 and 5 and type the answer here:
  • Post