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.
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:
The following table summarizes some of the key differences between these two reporting options:
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.
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 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).
These reports can be scheduled, delivered by email and other mechanisms.
Must be executed on-demand.
CRM Online Support
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.
Developing Fetch XML Based SSRS Reports
Microsoft Premier Field Engineer
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?
Any command to get list of all Users (Both Full / read only). either FetchXML & SQL command is welcome