Custom SSRS REPORT IN DYNAMICS 365 F&O || AX 2012 R3

There are multiple ways to develop SSRS reports in Microsoft Dynamics 365 F&O and AX 2012 R3. This article will guide you in developing your first SSRS reports based on Report Data Provider (RDP). RDP base SSRS reports; are used when complex business logic cannot; be achieved using an AOT query.

You Need Following Before Start.?

1. Microsoft Dynamics AX 2012 || Microsoft Dynamics 365 F&O.

2. Visual studio 2015 || 2017.?

3. SQL Server Reporting Services (SSRS); must be configured.?

4. Reporting services extensions; must be installed in Dynamics AX 2012 || Dynamics 365 FO.

Essential Concepts

1.Report Data Provider (RDP) Class?

RDP class is an x++ class that is used to access and process data for an SSRS report. The RDP class processes the business logic based on a specified parameter and/or query and returns a dataset to the reporting services.?

In order to create an RDP class; you have to extend that class with?SRSReportDataProviderBase. This tells AX || 365FO that this class will be used by reporting services to process the data.?Two important attributes are used in RDP classes:?

1.?SRSReportQueryAttribute:

It specifies which AOT query will be used in this report. If the RDP class uses an AOT query to process data, define this attribute at the beginning of the class.

?2.?SRSReportParameterAttribute:?

It defines the data contract class that will be used by this report to prompt for parameter values. If the RDP class contains any parameters then defines this attribute at the beginning of the class.?

Both the attributes are optional. If the report does not use any query or does not want any parameter to filter report data, these attributes do not need to be used.

2. Data Contract Class

?A data contract class is an x++ class that contains parm methods with the?DataMemberAttribute?defined at the beginning of the method.?This class is used to define one or more parameters that will be used in an SSRS report.?

3. Table?

A table is used as the dataset to store data for the report. The RDP class processes the data and stores it in the table which is then used by an SSRS report to render data.

?A table can be a temporary table?(InMemory or TempDB)?or a regular table, but it is?Microsoft best practice?to use a temporary table. The type of temporary table is based upon performance considerations.?InMemory?temporary table is used when the data set is small, while?TempDB?is normally used for larger datasets to improve performance

Let start developing our first SSRS report

Now create a contract class, which is responsible for setting and getting the data in which use the fields to set parameters in the form to get the condition from the user.

Like in the below contract class; we define two fields of?Str type?(String type)ItemId and DataAreaId and define the setter and getter function for the fields; one thing to remember is to mention the?“DataContractAttribute”?which differentiates the Contract Class from other classes.

Now create a temp table for the report to display and create fields in it, which you want to use in the report, Like, in the fig below, I use four fields: SalesId, ItemId, DataAreaId, and Line Amount. One thing to learn is to change the?Table Type?property of Table; from?Regular?to?TempDB?in the Table properties.

Now create a data provider class and define it with the SSRS parameter attribute like the name for the contract class is “SalesContractClass” and extends the class from?“SrsReportDataProviderPreProcessTempDB”.?In which make the object of Temp Table, Contract Class, Dialog fields to save and the table on which you want to run the query.

Now add the two functions in the?RDP class, one for set data to the?Temp?table?and in which write the logic to get data from the table, like from SalesLine table (write the Query). The second function is to return data to the report when called. One point is that to get data which user enters to get from contract object like highlighted in below fig.

Now, add the report to the project to display the report based on the query; you just added it to?Temp Table. Firstly, add a dataset to the report from the properties of the dataset you created, and change the data source type to?“Report Data provider”. As shown in below fig.

Add the design in the report and design it in the way you love, but I am using a simple pattern.

Now add the?Output Menu item?to display the report and update the properties like?Object?to the name of your report, and?Object Type?to SSRS Report and update the design to the design you want to assign, as shown in below fig.

Add the?Menu extension?to show the menu item as shown in below fig. For this create a sub menu by right click and add a sub-menu. Drap and drop your menu item inside this sub-menu and don't forget to add LABEL in label property of sub-menu.

Build the project with DB sync (true) option and also deploy the report by click right on?Report?in Solution Explorer and click on?"Deploy Report"?option

?Now, open the module where you attached the report menu item. In my case, I added it in Account Receivable because it was related to Sales.

Add the values in the form dialogs and click Ok, then wait for the report to open.

Many many Congrats! You have learnt how to create custom SSRS report using RDP class.?Now, do more practice by yourself and become expert in your career.

Happy Learning!

Syed Amir Ali

?