In this blog, we highlight a lesson learned when we used a Dynamics 365 Customer Engagement online data source connector, called Instance Web API, for Power BI reports.
Most users are familiar with the out of the box reporting tools (Advanced Find, Report Wizard and CRM Dashboards leveraging system views) in Microsoft Dynamics CRM, but there are other custom reporting tools available for when the out of the box methods don’t provide the results you want. This post will describe your CRM 2011 Reporting Options, the differences between them, and the environments for which they are best suited.
Reporting options for CRM 2011: CRM 2011 On-line now supports custom reports using FetchXML based reports. CRM 2011 On-Premise continues to provide custom reporting capabilities using SQL based reports and filtered views.
Reports extend the capabilities of the built-in features such as Advanced Find and Charts as well as more advanced calculations, grouping, and visualizations such as gauges and spark lines.
SQL Reporting Services Reports are the standard reporting for CRM 2011 as they have been since version 3.0. The development environment utilizes traditional SQL reporting tools however with CRM 2011 on-line we can now access the data using FetchXML and the Report Authoring Extension.
Fetch is a proprietary query language that is used in CRM and supports similar query capabilities as query expression. FetchXL supports all the features of QueryExpression plus aggregates and grouping. Queries are built as XML statements that conform to the schema definition for the FetchXML language. Fetch has always been used in Microsoft Dynamics CRM but with 2011 support is provided for reporting using the Report Authoring Extension.
The Development tools
Challenges and Limitations of CRM On-line Reporting
There are challenges with FetchXML reporting including direct SQL access and accessing other data sources. For report writers new to the FetchXML syntax there will be a learning curve to adjust to the elements required and differences from SQL functionality. The best starting point is to create an advanced find and download the Fetch XML created by the advanced find query. This can then be used as the query to begin the report with further refinements for filtering, parameters, and aggregations.
In addition to Data Access requiring FetchXML, potential limitations of using CRM on-line reporting include:
Everything you ever wanted to know about CRM 2011 reporting options and more. Feel free to reach out with any of your CRM reporting needs.