Query Based SSRS Reports in D365 with Example

In this article i will show you how to create a SSRS report in D365FO. Microsoft Dynamics 365FO users use SSRS report for business reporting purposes. Here explain how to develop a SSRS report from scratch. In Microsoft Dynamics 365 SSRS reporting feature is one of the significant feature provided by Microsoft for their users. This SSRS report will helps the users to get the right information about the business processes and transactions.

If you are a learner or beginner you can Set up Online VM for Dynamics 365 Finance and Operation for Technical and Functional Learning Purposes

Based on the complexity of the SSRS reports, the development will be divided into two methods,

• Query Based Approach.
• Data Provider Approach.

Here we are explaining the first method that is Query Based Approach,This approach we are normally using for generating simple SSRS report which are straight forward reports. For example; you want to generate SSRS report for the customers master data,Here we can use very straight forward method, because we are generating report only based on the physical fields of the CustTable.

But Data provider Approach is little bit different from Query Based Approach, because there we are using some complex logic. For example ; we want to generate a customer wise sales report in between a particular period.In this case we have to join different table and also have to check different conditions. So in this situations we can use Data Provider Approach.

As we already mentioned Here we are creating a SSRS report for displaying customer master details from table name CustTable.
for this we have to follow the below steps.

• Create a Query Qry_Customers.

• Link or refer this query to table named CustTable(ie. CustTable is the data source of the created Qry_Customers).

• Create a Report Rpt_Customers.

• Set Query Qry_Customers as the Datasets of the Report. For this Drag and  Drop Qry_Customers to DataSet of Report Rpt_Customers.

• Drag & Drop the Qry_Customers  from DataSet to design node of the Report.

• It automatically create AutoDesign.

• Under AutoDesign node one table also created Qry_CustomersTable1.

• Now you can see all the physical fields in the Data Node of the Qry_CustomersTable1.

• Add a sorting field under the Sorting node of AutoDesign of the report.

• Change sorting order based on your requirement (it will be ascending by default).

• Change Style Template Property of Qry_CustomersTable1 to the required one.

• Change the Layout Template property of AutoDesign to ReportLayoutStyle Template.

• Create one Output Menu item for displaying the menu in the Home screen for the Report.

Then i am going to show you how to do the above steps in Details, So launch your Sandbox Virtual Machine environment,

If you are a learner or beginner you can use Microsoft you can Set up Online VM for Dynamics 365 Finance and Operation for Technical and Functional Learning Purposes.

How to do it..

Here i am going to create a model SSRS_Model, Under this model i am creating a project SSRS_Customers.

Next step is create a query, for this from solution explore right click your project then follow the steps.Add-->New Item-->Data Model Node-->Query.Name the query to Qry_Customers.

Next step is Linking or referring the CustTable as the Data Source of the created Query. For this Open AOT.

View Menu-->Application Explorer-->Data Model-->CustTable. Drag and drop this table to the Data Source node of the Qry_Customers.For this you must add the reference model Application Suit in to your project.

Change the property Dynamic Fields of CustTable to Yes.Then go to Fields node of the CustTable and remove the unnecessary fields.

Next step is Create a SSRS report. for this from solution explore right click your project then follow the steps.Add-->New Item-->Reports Node-->Report.Name the Report to Rpt_Customers.

Next Step is set the query Qry_Customers as the dataset of the Report.

For this Drag and Drop Qry_Customers to the DataSet of Report Rpt_Customers.

Then Drag & Drop the Qry_Customers from DataSet node of report to Design node of the report. It will automatically create and AutoDesign Node under Design. Under AutoDesign node you can see one Table with name  Qry_CustomersTable1 is automatically created.

Now you can see all the physical fields in the Data Node of the Qry_CustomersTable1. Add a sorting field under the Sorting node of AutoDesign of the report.

Change sorting order based on your requirement (it will be ascending by default). Change Style Template Property of Qry_CustomersTable1 to the required one.

Change the Layout Template property of AutoDesign to ReportLayoutStyle Template.

Finally Create one Output Menu item for displaying the menu in the Home screen for the Report and change the label property to Customers. Put this Output menu under AccountsReceivable or wherever you required. I created one Output menu name Mnu_Customers and change the label property to Customers. Set the object type property of Mnu_Customers to SSRS Reports and Object property to Rpt_Customers.

Now all setup has been completed. Next go to Solution Explorer and right click on the report Rpt_Customers then select Deploy Reports.

Finally Build the Project. Then go to functional environment and go to the module Account Receivable under the Main menu Inquiries and report you can see one output menu name Customers as shown in the below figure.

Click the output menu Customers. Then click OK. then it will navigate to Report preview window.

Ensure the service SQL server Reporting service is running on the Virtual Machine. if no go to services and start the service.

Finally the report will generated , By default SSRS report preview window has the features for  find , Zoom and export the file to different formats(PDF,Word,Excel etc...)

Happy coding with D365Snippets. Please post your comments and share the post.