Developing RDP (Report data provider) based SSRS reports – AX 2012

RDP (Report data provider) based reports are developed, when reports has a complex logic that cannot be achieved by just using an AOT query. In RDP based reports, in addition to an AOT query, an RDP class is developed which contains the report logic. After processing all the report logic, the data is filled into the temporary table which is used as a data source for SSRS report. Now, I am going to describe about how to build an RDP based report. We will modify the previous AOT query for this example to add the CustTrans data source. The query will now look like as shown below:

Now create a new RDP class and name it SampleReportDP. To create RDP based report, following objects needs to be created:

• An RDP class.
• An AOT query.
• A temp table.

The query has been modified for this example and now RDP class and temp table needs to be created. Just for a demo purpose, I have thought of a requirement where we need to display the transaction amount based on the customer group. The logic is as follows:

· If (customer group is 20) then multiply the transaction amount by 20

· If (customer group is 40) then multiply the transaction amount by 40

· If (customer group is 60) then multiply the transaction amount by 60

Create a new class and name it SampleReportDP. In class declaration extend the class from SRSReportDataProviderBase class. Add the following line before class declaration:

[SRSReportQueryAttribute(querystr(SampleQuery))]

This attribute specifies the AOT query which is used by this SSRS report. Also declare some class variables which are needed to store the values, retrieved after running the report query. The class declaration should look like as shown below:

The second step is to create a temp table which is used to store the report data. Create a new table and name it SampleReportTmp. Set the TableType property of the new table to InMemory. Create four fields in the temp table as shown below:

Create a new method in RDP class which will select the temp table records and return it afterwards to be used by the SSRS report. The new method should look like as shown below:

Now we are going to implement the logic discussed above in RDP class processReport method. The processReport method in RDP class is the point where the report processing begins. This method is called by the reporting framework. Actual report logic needs to be written in this method. In our case, the code for processReport method is as follows:

public void processReport()
{
    QueryRun queryRun;

    queryRun = new QueryRun(this.parmQuery());

    while (queryRun.next())
    {
        custTable = queryRun.get(tablenum(CustTable));
        custTrans = queryRun.get(tablenum(CustTrans));

        amountCur = custTrans.AmountCur;
        voucher   = custTrans.Voucher;
        custGroup = custTable.CustGroup;
        transDate = custTrans.TransDate;

        switch (custGroup)
        {
            case '20':
                amountCur = amountCur * 20;
            break;

            case '40':
                amountCur = amountCur * 40;

            case '60':
                amountCur = amountCur * 60;
            default:
                amountCur = custTrans.AmountCur;
        }

        this.insertSampleReportTmp();
    }
}

 

code for insertSampleReportTmp method is as follows:

private void insertSampleReportTmp()
{
    ;
    sampleReportTmp.AmountCur   = amountCur;
    sampleReportTmp.Voucher     = voucher;
    sampleReportTmp.TransDate   = transDate ;
    sampleReportTmp.CustGroup   = custGroup ;

    SampleReportTmp.insert();
}

Create a new Microsoft Dynamics AX reporting project from VS 2010 and name it RDPBasedReport. Add a report item to the project by right clicking on the Project. Change the report name to RDPBasedReport. Create a new data set and name it SampleRDPReportDS. Now open the properties page for the newly created dataset by clicking Ctrl + Enter. Set the data source type to ‘Report Data Provider’. Select the query property and click the ellipses to open the RDP class selection window. From the list of classes, select your RDP class which was SampleReportDP and then click next. Afterwards, select all the fields and then click OK. You report should match with the following screen shot:

Notice the query mentioned in the Query property of dataset. It is the syntax for mentioning RDP class and temp table in the query property whenever you use the report data provider as data source type. Add the new report to AOT by right clicking on the report project. Now deploy the report by right click on the report solution and select deploy solution to deploy the SSRS report on report server.

Create a new output menu item, and name it RDPBasedReport. Set its properties as shown below:

Now right click on the menu item and select Open to launch the SSRS report we have just developed. The report output should match with the below screen shot: