How to create SSRS Reports for Dynamics 365 Using FetchXML

To be able to create SSRS reports for Dynamics 365 using Visual Studio, you need to perform the following actions:

Pre-Requisites

• Install Visual Studio 2015. For more information click here.
• Install Microsoft .NET Framework 3.5 Service Pack 1. Click here to download and install.
• Microsoft Dynamics 365
  Report Authoring Extension is required to author Fetch-based reports used with Microsoft Dynamics 365 by using SQL Server Data Tools. Click here to download and install.
• Microsoft SQL Server Reporting Services is required for reporting features in Microsoft Dynamics 365. Click here to download and install.

Once you have completed these steps, you are ready to start creating SSRS reports for Dynamics 365. The following sections explains how you can create SSRS reports using FetchXML from Dynamics 365.

Download Fetch XML from Dynamics 365:

• In Dynamics 365, click Advance Find.
  
• Build a query, for example, to populate all active contact details. You don’t need to save it.
• Click Download Fetch XML. This file will be used in the report.
  
• Open the file in notepad to check the query.
  

NOTE: We will use this query later in Visual Studio Query Builder.

SSRS Reports Using Fetch XML in Dynamics 365:

• Open Visual Studio and start a New Project.
• Click Business Intelligence > Reporting Services > Report Server Project Wizard.
• Specify project Name and Solution Name.
• Click Ok.
  
• This will open the Reporting Wizard.
  
• Click Next to Select the Data Source:
  • Select New Data Source.
  • Specify the Name.
  • Select the Type as Microsoft Dynamics 365 Fetch.
  • Specify the Connection String (this is the URL you use to connect to Dynamics 365)
  • Click Credentials and Use a Specific User Name and Password.
  • Click Ok.
  
  NOTE: If multiple CRM Organizations are available, there may be dialog box which may ask you to Login or select the right organization. Provide the credentials and Login to relevant organization.
• Copy and Paste the query from Fetch XML to the Query Builder in Visual Studio and click Run. The system will fetch the result from Dynamics 365.
  
• Click OK. The system will display the Query String that you just ran.
  
• Click Next. Select the Report Type (Tabular or Matrix).
  
• Design the report and click Next.
  
• While Completing the Wizard, specify Report Name and select Preview Report and click Finish.
  
• The system will show you a preview of how your report will look like in Dynamics 365.
  

Build the RDL file:

• In the Solution Explorer, right-click on the Project and click Properties.
  
• In the Target Server Version field, select SQL Server 2008 R2, 2012 or 2014. Click Apply. (This is Important to avoid errors!).
  
• In the Solution Explorer, right-click on the Project and click Build or Rebuild.
  
• In the Solution Explorer, right-click on the Solution and click Open Folder in File Explorer.
  
• You will find the RDL file placed in the ..\Client Details Report\bin\Debug folder.
  

Import the RDL file in Dynamics 365:

• In Dynamics 365, open the Reports section and click New.
  
• In the New Reports form
  • Select Report Type as Existing File
  • Click Choose File to select the RDL file
  • Specify Name
  • Select Display In as Report area
  
• Click Save and Close, this will import the report in the Available Report List, from where you can now run the report.