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.

  1. Using the built-in wizard – which is great for basic reports, but isn’t very flexible.
  2. 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:

  1. 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.


     
  2. 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. 
  3. Once you’ve chosen your starting point, you select the columns to appear in your query from the “Edit Columns” dialog.


     
  4. Then with your columns laid out you can add filters under the “Select” statement.
  5. 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. 

     
  6. 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.

What’s Next?

Now you’re ready to use this FetchXML and configure your SSRS report.  Here’s part 2 – if you’re ready to move on.


Share this:
 
 

Comments:

Add your comment: