Building SSRS reports in AX2012 (Query and RDP) – Part 2

In my last post I went through the process of creating a query based report for AX2012. In Part 2 I will give you a walkthrough on how to create a RDP (Report Data Provider) based report for AX2012.

Things to know before you get started

1. Report Data Provider (Class)

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:

1. SRSReportQueryAttribute: 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: defines the data contract class that will be used by this report to prompt for parameter values. If the RDP class contains any parameters this define 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

This class holds all of your parm methods with the DataMemberAttribute defined inside the method. This class will eventuelly be used to define all the parameters for your report.

3. TmpTable

When working with RDP based reports we also need a temporary table. This can be either an InMemory or TempDB table. The RDP class will process the data and store it inside the table, which will then be used by your report to render data.

So we will start off by creating a table:

We also need a TmpTable. Set the tableType to InMemory or TempDB. I will set mine to TempDB for now:

I’m also gonna add some fields to the table. Expand the AdCustTmpTable node and add the following fields:

The next step is to add the classes that we need. We will start with the RDP class. Right click the Classes node and select new class.

Open the Class declaration by right clikcing on it and selecting View code:

Then add the following code:

class AdCustReportDP extends SRSReportDataProviderBase
{
AdCustTmpTable adCustTmpTable;
}

Add a new method and name it getAdCustTmpTable. This method is mandatory because the report will use this method to get the table containing all of your processed data. The SRSReportDataSetAttribute attribute is used to indicate the temp table name and also tells reporting services to use this table to retrieve the data:

[SRSReportDataSetAttribute(tablestr(‘AdCustTmpTable’))]
public AdCustTmpTable getCustReportRDPDemoTmp()
{
//select data from table
select * from adCustTmpTable;
//return the table
return adCustTmpTable;
}

Add another method and name it insertData. This method contains the business logic.

///The method should compute data and populate the data tables that will be returned to SSRS.
private void insertData()
{
CustInvoiceJour custInvoiceJour;

while select * from custInvoiceJour
{
adCustTmpTable.clear();

adCustTmpTable.OrderAccount = custInvoiceJour.OrderAccount;
adCustTmpTable.InvoiceAmountMST = custInvoiceJour.InvoiceAmountMST;
adCustTmpTable.SalesId          = custInvoiceJour.SalesId;

adCustTmpTable.insert();
}
}

Then add another method and name it processReport. This method is called by reporting services to generate data:

[SysEntryPointAttribute]
public void processReport()
{
this.insertData();
}

When I’m happy with my classes and my table I jump into Visual Studio and create a new project. Give your solution a fitting name and click “OK”:

Then we need to add a report to the project (Right click -> Add -> Report):

Create a new dataset, name it and select Data Source Type, Report Data Provider:

The go down to the properties and in the query box hit the ellipsis button.

Select your Report Data Provider and hit Next.

Select your fields and hit OK. This will pull in our fields created in the tmp table into our dataset:

Select your dataset and drag it over to the Designs node. This will create an Auto Design report. Name it and head over to the properties of the Auto Design and select your layout template and table layout:

Table style template:

Then go ahead and preview the report:

Sweet! So we got our data rendered in the report.

That’s it for now! Good luck!