Writing Customer Reports using FetchXML in Microsoft Dynamics 365

12/09/2017

Whether you’re new to Microsoft Dynamics 365, or are a long-term user of the product and its previous versions. A major benefit and selling point of Dynamics 365’s CRM system has always been its ability to produce SSRS reports. Microsoft Dynamics has incorporated a built-in Report Wizard ever since Microsoft launched Dynamics CRM 2013. However, with Dynamics 365 the introduction of custom SSRS report generation is set to change the way in which we write and utilise our reports.

But what are the real benefits of this new customisation feature for SSRS reports over the long-standing wizard we’re used to? Well, firstly, the reports it’s used to create are compatible in both on premise and cloud based applications without needs for changes between the two. As well as this, the highly customisable nature of the SSRS report generator allows for far greater flexibility when managing the data within these reports. Additional filters, data control and entity customisation make for a far more comprehensive and powerful generation tool.

You may think this all sounds great, but have no idea about how to use this new and exciting tool. Fortunately for you, we’ve had the chance to work with the new SSRS reports internally, as well as with our clients and we’re here to give you a brief run through of how to get the most out of this great new feature:

Getting Started

To use FetchXML as a data source in your SSRS reports, you first need to set up your local environment. This usually involves installing the correct “Microsoft SQL Server Data Tools – Business Intelligence” version for your Visual Studio instance, which allow you to choose “Microsoft Dynamics CRM Fetch” as your data source type and enter your CRM Online credentials for authentication.

Creating FetchXML Queries

FetchXML queries can be created from scratch, but it is much easier to prepare your query in an Advanced Find window, add all the columns you want to report on, set the filtering conditions for your query, and then click the useful “Download Fetch XML” ribbon button. This will generate the FetchXML ready to use in a report. Once you have the FetchXML in SSRS, you can modify the query or continue designing your SSRS report.

Pre-Filtering Records with FetchXML

Pre-Filtering CRM reports is very useful when you need to run reports on a specific record, or a set of records selected by a user. It is very easy to enable the pre-filtering functionality by adding a small snippet into your XML. When a report is pre-filtered, CRM will automatically filter your result set.

Change the “entity” XML element to enable pre-filtering. E.g. for Accounts:

<entity name=”account“>

should become:

<entity name=”account” enableprefiltering=”1“>

A report parameter will automatically be created if you need to do further analysis on records in the current context.

FetchXML Considerations

When designing a CRM report, consider the performance impact and try to minimise how much data is being retrieved. Only retrieve columns that you need on the report, and make use of filters to reduce the amount of data being returned. There are some limitations to FetchXML queries, including: only 10 linked entities can be used, two fields cannot be directly compared and there is a maximum number of 50,000 records that can be retrieved.

In summary, FetchXML is the Microsoft query language that custom SSRS reports must use. It is one of many ways to query CRM data, along with charts, dashboards, Excel reports and Power BI. If you have complex reporting requirements, you may not be able to achieve these with FetchXML and need to consider alternatives – such as synchronising CRM data to a data warehouse, allowing more advanced reporting capabilities including breaking the 50,000 record limit.

Get in touch if you need any assistance or training for Dynamics CRM Online (CRMOL) reporting.

Get In Touch

Provide your details below and we'll be in touch soon, we'll also send you some occasional updates and amazing City Dynamics news. You can unsubscribe at any time.

City Dynamics . 30 City Road . London . EC1Y 2AY