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.
how to provide parameters for query type ssrs report,
ReplyDeletefor DP type it is possible , but i want to know in query type
I will try to make an article regarding your query. Can you please refer the below link.
Deletehttps://www.axpeditesoftware.com/report-parameters-for-query-based-reports-d365-ssrs/
how to provide parameters for query based report ?
ReplyDeleteYou mentioned above about 2 type of methods
ReplyDelete1 Query Base approach
2 data provider approach
so , where is 2nd one ??? I need Data Provider approach. please mention it.
Yes, I mentioned already, But this article explains only Query Based SSRS Reports, In the next articles, i will explain DP Based SSRS report creation with an easy example. Thanks for your feedback.
DeleteCan you please show Query Based SSRS Report with grouping methods? I am more interested in the Query Based Data Source Grouping method! Both with AUTO DESIGN & PRECISION DESIGN???
ReplyDeleteCan you please show Query Based SSRS Report with grouping methods? I am more interested in the Query Based Data Source Grouping method! Both with AUTO DESIGN & PRECISION DESIGN???
ReplyDeletehttps://app.gemoo.com/share/image-annotation/642508338824966144?codeId=DGW2kb3JKg7d3&origin=imageurlgenerator&card=642508337558286336
ReplyDeleteCan you please create /show Query Based SSRS Report with grouping methods? I am more interested in the Query Based Data Source Grouping method! With AUTO DESIGN & PRECISION DESIGN