The post Developing a SSRS report using the Report Data Provider in Microsoft Dynamics AX 2012 appeared first on Dynamics 101.
There are multiple methods to develop SSRS reports in Microsoft Dynamics AX 2012. This tutorial will guide you in developing Report Data Provider (RDP) based SSRS reports.
RDP based SSRS Reports are used when complex business logic cannot be achieved using AOT query.
Report Data Provider Class is an X++ class that is used to access and process data for a 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 a RDP class in AX, you have to extend that class with SRSReportDataProviderBase. This tells AX that this class will be used by reporting services to process the data.
Two important attributes are used in RDP classes:
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.
A data contract class is an X++ class which 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 a SSRS report.
An AX 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 a 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 the performance considerations. InMemory temporary table is used when the data set is small, while TempDB is normally used for larger datasets to improve performance.
As part of this tutorial, the report will print a list of customers and their invoiced sales order counts.
class CustReportRDPDemoDP extends SRSReportDataProviderBase
//Temporary table buffer
public CustReportRDPDemoTmp getCustReportRDPDemoTmp()
//select data from table buffer
select * from custReportRDPDemoTmp;
//return the buffer
/// Processes the SQL Server Reporting Services report business logic
/// This method provides the ability to write the report business logic. This method will be called by
/// SSRS at runtime. The method should compute data and populate the data tables that will be returned
/// to SSRS.
public void processReport()
//select all customers
while select * from custTable
//clear the temporary table
//assign customer account and name
custReportRDPDemoTmp.CustAccount = custTable.AccountNum;
custReportRDPDemoTmp.Name = custTable.name();
//select count of invoiced sales order of customer
select count(RecId) from salesTable
where salesTable.CustAccount == custTable.AccountNum
&& salesTable.SalesStatus == SalesStatus::Invoiced;
custReportRDPDemoTmp.SalesOrderInvoiceCount = int642int(salesTable.RecId);
//insert in temporary table buffer