SSRS Reports for Dynamics 365 Using FetchXML

Images and text references using this link

In this blog we are going to learn how to create SSRS reports using Visual Studio in Dynamics 365 (v9). Please follow the below steps.

Pre-Requisites

  • Install Visual Studio 2012 or later. For more information click here.
  • Make sure to have .Net Framework 4.5.2 and above
  • 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.

Get the fetch XML query from Advanced find in 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.

    101

     

  • NOTE 1: There is a possibility that you may not be able to connect with Dynamics 365 and “Could not connect to DataSource” or “can’t use cached credentials to sign in to microsoft dynamics 365” errors will come. In this case please follow this link and do the needful. This is because Microsoft hasn’t released version of Report Authoring Extensionthat supports both v9.0 and enforced TLS 1.2.
  • NOTE 2: If it connects with out any issue then, 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.

    Thats all for SSRS online reports…

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s