SSRS stands for SQL Server Reporting Service report. You can use SSRS reports to dynamically present your data from Microsoft Dynamics in graphs, charts and bars to help with smooth Dynamics implementation.
SSRS provides user-friendly facilities with batch processing, email support, and print archive functionality
It is a beneficial way to produce packing slips for tracking orders from manufacturing to sales warehousing. Furthermore, it provides a beautiful way to extract data to articulate business health. It also provides tools to support different basic reporting requirements. These SSRS reports work best when you integrate them with Excel, Reporting Services, Power BI Data Flow, and Management Reporter.
Basic Components of SSRS Report
DP (Data Provider) class: A DP (Data Provider) class renders a job to provide a data
Data Contract class: In the contract class, you define several variables and report parameters in the data contract class. There can be some hidden parameters in the data contract class. Here is a scenario we have one hidden parameter, and that is the sale’s agreement RecId.
Three temporary tables: We create three temporary tables to define and carry the report data. They will be filled, and the date these tables are holding will be exposed to the SSRS report, and this job will be rendered by the Data Provider class.
Controller class: In the Controller class, you handle the report dialogue form, the SSRS report’s design, and the hidden parameters’ values. In this scenario, we will take the value of the sale agreement RecId as our concealed parameter.
Menu item: There should be something that opens the SSRS report so you can view the SSRS report in printable form. The menu item usually renders this job. It also tells you that the menu item triggers the report dialogue form.
Now let’s create temporary tables for our SSRS report.
Creating Temporary Tables:
Navigation to create temporary tables for your SSRS report is given below
Create temporary tables by going to Add > New Items > Dynamics 365 Items > Data Model > Table
By following the same procedure, you can create three temporary tables
DocAgreementHeader –used to store a sales agreement’s header data.
DocAgreementLine – used to store a sales agreement’s lines data.
DocAgreementContact – used to store a sales agreement’s customer contact data
As u can see below
What makes your table behave as a temporary table is a TableType property. Set this property to InMemory for all tables. Selecting this property for your tables allows you to convert a standard table to a temporary one.
Create the Controller Class
First of all, we have to extend our contract class with SRSReportRunController.
After that, we have to define DataContractAttribute attributes
A Data Provider class extends SRSReportDataProviderBase and should also implement at least the process report() method.
To add a new class, toggle to Add > New Items > Dynamics 365 Items > Code > Class.
By following this navigation, we will create three classes as given below
DocAgreementDocumentController – Controller class.
DocAgreementDocumentContract – Data Contract class.
DocAgreementDocumentDP – Data Provider class
For every report, each controller class should extend SrsReportRunController
Create the Data Contract Class
The data contract class basically stores all the data members or parameters of the report.
Code for Data Contract Class
Create the Data Provider Class
Remember, your Data Provider class should expand SRSReportDataProviderBase for pre-processed reports. Also, it should at least implement the ProcessReport() method.
The ProcessReport() method is called by Reporting Services to fetch the report data and fill the temporary tables. In our scenario, we have three temporary tables.
Code for Data Provider Class
Create the SSRS Report
Now we will create our SSRS report by using the navigation that is given below
To add a new SSRS report into Dynamics 365.
Go to Add > New Items > Dynamics 365 Items > Reports > Report
Set the Source Data of the Report to DP Class
Now add a dataset to the report. Also, set its Data Source Type property as Report Data Provider. Once done with selecting the source type, click the small button available on the Query property.
In your SSRS report’s dataset, add a new dataset and change the property of Data Source Type to Report Data Provider, then check the small button available on the Query Property. As shown below, follow the same procedure.
Replicate the same steps to add more than one dataset.
Now we’ll create the design of our report
Create the Menu Item for our SSRS Report:
To create a menu item, create a new extension for the SalesAgreement form.
The first (Preview document) will open the report on screen, and the second (Print document) should open the report dialogue form.
As in our case, we will create a different extension for the SalesAgreement form for adding two menu items.
The first one is to preview the document on the screen.
The second one will open the report dialogue form.
Execution Of SSRS Report:
The report that we’ve just created is going to be executed.
So now open your Microsoft dynamics 365 finance and operation.
Follow the navigation that is given below.
Goto Accounts receivable > Orders > Sales agreements.
Here, open any sales agreement. Then, click the Preview document to see the result.
Your report will display on the screen with the same design that is given below
In this article, we’ve covered how to create dynamics CRM SSRS reports. SSRS can assist you with making direct, graphical, and freestyle reports from social, multi-layered, and XML-based information sources. The messages can likewise be distributed and gotten to on request. SSRS again has an implicit planning instrument to perform fundamental report organizations through email, record share, or SharePoint.
Did we mention that building SSRS reports can help you construct a healthy business management system? If you are looking to integrate SSRS reports within your Microsoft Dynamics with expert assistance, do not hesitate to contact us.