Creating SSRS Report for Dynamics 365 Online and Model Driven Power Apps

Microsoft Dynamics 365 online and Model Driven PowerApps offers several advanced reporting options. Advanced reports are often necessary when business requirements demand complex calculations, returning multiple data sets, grouping large sets of data based on rules, and retrieving data from different data sources.  Before creating an advanced report using SSRS, you may also consider using Advanced Find, Dashboards and Charts, or the Report Wizard in Model Driven PowerApps/D365 Online.

In my experience, the out of the box reports offered by Dynamics 365 are not often used and in most cases, there is a need for custom reports. In such a situation, SQL Server Reporting Service reports offer much flexibilities as compared to Dynamics 365 Report wizard and creating SSRS reports does not warrant any particular development skill.

Watch Video: Microsoft Dynamics 365 – Reporting

In this article we are going to show that with some basic knowledge you can create a report using SSRS (SQL Server Reporting Services) for Model Driven PowerApps very quickly.

Pre-Requisites:

• Visual Studio 2015 or higher. Download from here
• .Net Framework 4.5.2 and above.
• Microsoft Dynamics 365 Report Authoring Extension to author Fetch-based.
• Microsoft SQL Server Reporting Services.

Optional Requisites

• XrmToolBox to connect to the Common Data Service for Apps (CDS)
• FetchXML Builder Plugin for XrmToolBox, to create and execute queries against CDS and Dynamics 365/CRM data.

Once we have downloaded and installed above required, we are ready to start Creating SSRS Report for Dynamics 365 Online/Model Driven PowerApps.

SSRS Reports Using Fetch XML in Dynamics 365

• Open Visual Studio 2015 and start a New Project. Select Templates > Business Intelligence > Report Server Project
• Specify project Name and Solution Name. Click Ok.
• Now expand Solution Explorer > Right Click on Reports Folder > Add > New Item From the Add new Item Window Select Report > Provide a Name > Click on Add
• Once the report is added you should see the following screen.

Adding Parameters

• You can add parameters to a report to define a report’s individual parameters, pass information through a query, or provide access to user settings, such as CRM_CurrencySymbol and CRM_CurrencyPositivePattern parameters.
• From Report Data Toolbar expand Parameters and right click to add parameters.

Hidden parameters

• The Report Designer in Visual Studio has built-in support for hidden parameters. In addition, you can hide parameters by adding a CRM_ prefix to the parameter name in a report. By default, the parameters with a CRM_ prefix are hidden when the report is published. When you run the report, you aren’t prompted to enter parameter values for the hidden parameters.

Special parameters

The following table shows the special hidden parameters that you can use in your reports.

Parameter	Description
CRM_FilterText	Contains the value of the filter text that a report user interactively creates in the Report Viewer when the user runs a report. The parameter is in a filter summary text box that is located in the report header. The initial value is set to the default filter.
CRM_URL	Set to the URL of the app. Use this parameter when drilling through.
CRM_Filtered[Entity]	Use in a query expression to enable data pre-filtering (through Advanced Find). You should replace [Entity] with your Actual entity name for which the report is being developed

You must create all parameters in a report before you can refer to them. The values of these special parameters are filled in by Customer Engagement (on-premises) when you run the report.

Also read : Dynamics 365 Integration Service

Adding Data Source to The Report

• From Report Data Toolbar expands Data Sources and right-click to add new data sources. Specify the following at Data Source Properties window.
  • Provide a proper Name and select Embedded Connections
  • Select the Type as Microsoft Dynamics 365 Fetch
  • In the connection, string section put your D365 URL
  • From the left navigation click on Credentials and Use your Username and Password or Select Prompt for credentials if you don’t want to store the credentials.
  • Click OK

Adding Data Set to The Report

• From Report Data Toolbar expand Datasets and right click to add new data set.
• Specify Name and select the Data source created on previous step.
• Select the Query type as Text.
• Use FetchXML Builder to create Fetch XML and paste the same here in the Query text.
• Apply prefilter and filters as per requirement.
• Make sure to remove page size attribute from fetch XML if present as these is not supported.

Designing the report

• Once all the above steps are completed, we are ready to design the report.

• Use the Toolbox and the Dataset to create the report as per your requirement.

Build and Publish the Report

Build the RDL file

• In the Solution Explorer, right-click on the Project and click Properties.
• 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 ..\[Project Folder]\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 per your requirement.
• Click Save and Close, this will import the report in the Available Report List, from where you can now run the report.

Open the Report

• Go to the entity form for which you have chosen the report.
• Click on the 3 dots symbol if Run Report button is not available in ribbon of the form.
• In Run Report section you should be able to view the report.
• Click on the report to run it.

The goal of this article was to demonstrate how quickly you can leverage SSRS reporting capability and apply it to your Dynamics 365 information set. It is really onto you to experiment and explore how you can exploit all the features of SSRS reports and we may talk about that in some later post. Meanwhile, enjoy creating reports and stay in touch if you need any further assistance.