Reporting from Dynamics CRM Online: An Introduction to FetchXML
Posted by Mike Giuffre on November 14, 2016
There are basically 2 options for reporting on data in Microsoft Dynamics CRM Online.
- Using the built-in wizard – which is great for basic reports, but isn’t very flexible.
- SQL Reporting services – using FetchXML to retrieve your Dynamics CRM Online data.
What is FetchXML?
FetchXML is a proprietary query language developed by Microsoft to query data out of Dynamics CRM Online. If you need to develop reports from data contained in Dynamics CRM Online you’re probably using SQL Reporting Services (SSRS) as your development platform, and FetchXML to retrieve the data for the report. SSRS is the native reporting tool used for CRM, on-premise or Online.
The key to writing reports for CRM Online is mastering the FetchXML schema. For some this may be easy, but for others it’s like a foreign language. The trick to mastering this tool lays in a CRM feature called Advanced Find. If you haven’t conquered Dynamics CRM Advanced Find, I would recommend you start there first. We’ve included the CRM Advanced Find basics below to get you started.
Advanced Find Feature
Since the Dynamics CRM Advanced Find is incredibly important to developing your report, I’ll spend some time introducing it. But there are some great online resources to help you learn more.
Here’s a couple we recommend:
- Microsoft Dynamics 365 Help & Training Website
- Download the Dynamics CRM 2016 User Guide (738 pages) – refer to page 31 on Advanced Finds.
- Microsoft Virtual Academy – Introduction to Microsoft Dynamics CRM 2013 – Working with Data Section
- CRM Walkthrough: Using Advanced Find to Create Views
- To start your Dynamics CRM Advanced Find, click on the icon on the CRM menu bar.
Clicking this will open the Advanced Find window from which you can create queries to your Dynamics CRM data and download the related FetchXML.
- If you want to make queries easy, spend some time learning the Dynamics CRM entity structure. When you really understand the entities that store your data, you’ll be able to choose the correct starting point for you queries every time. The “Look for:” drop-down lists the entities you can start your query from.
- Once you’ve chosen your starting point, you select the columns to appear in your query from the “Edit Columns” dialog.
- Then with your columns laid out you can add filters under the “Select” statement.
- You can add filters and apply And/Or conditions as needed. Once you have all the data you need, click the magic “Download FetchXML” button and your browser should ask if you want to open or save your FetchXML.xml document.
- This download should give you a good starting point for your SSRS report. Here’s a sample of what you will see with the Download Fetch XML button.
Now you’re ready to use this FetchXML and configure your SSRS report. Here’s part 2 – if you’re ready to move on.