SSRS Report with RDP in AX 2012 R3

What is RDP Class?

Report data provider (RDP) is class that is used to access and process data for a report. It’s used when a report has a complex logic that cannot be achieved by AOT query.

Report Definition Language (RDL)

RDL is an XML application primarily used with Microsoft SQL Server Reporting Services. RDL is usually written using Visual Studio.

AX has Report Definition Language Contract classes that can generate and build the RDL for an AX SSRS report. The base class is SrsReportRdlDataContract.

DP (Data Provider)Class :

A) Attributes :

[SRSReportQueryAttribute(querystr(Passing Query))] :
This attribute specifies the AOT query which   is used by this SSRS report.

SRSReportParameterAttribute(classstr(Passing Contarct Class)) :
This attribute specifies the Contract class as a parameter.
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.

Code Sample :

[
SRSReportQueryAttribute(PassingQuery),
SRSReportParameterAttribute(classstr(Passing Contract class))
] public class SrsRdpSampleClass extends SRSReportDataProviderBase
{
// To do

}

Debugger:

Note: If we required to run debugger process in DP class then DP class should be extended by SrsReportDataProviderPreProcess\

B) Temporary Table: Properties — Table type.

There are 3 types of Table type.
1. Regular Table: Permanent table

2. In Memory: It’s temporary table which is exited in previous version. Such tables are held in memory and written to a local disk. If data set is small like 1000 records, then recommended to use In Memory property.

3. Temp DB: This is new option introduced Ax 2012. They are “physical” temporary tables held in the SQL Server database. If data set is large then recommended to use Temp DB.

Important methods in DP (Data Provider) class:

Create a new method for Temporary table.

[SRSReportDataSetAttribute(‘TmpVend‘)] public TmpCustTableSamplegetTmpVendTable()
{
select * from tmpVend;
return tmpVend;
}

Process report(): It’s override method to provide business logic for our report.

Code Sample:
public voidprocessReport()
{
Declare variables

AccountNumaccntNum;
VendTablevendTable;

      Declare Contract class
SrsRdpContractSampledataContract;

        Get the parameters from Contract class
dataContract    = this.parmDataContract();
accntNum        =  dataContract.parmAccountNum();
while select accountNum from vendTable
where vendTable.AccountNum == accntNum
{
// To do
 Insert records in Temp Table
}
OR
We can use Query directly instead of used while select.

 

Contract Class: It’s use to define the parameter in report.

Sample:

[DataContractAttribute] public class SrsRDPContractSample
{
AccountNumaccountNum;
}

[DataMemberAttribute(“AccountNum”)] public AccountNumparmAccountNum(AccountNum _accountNum = accountNum)
{
accountNum = _accountNum;
return accountNum;
}

Controller Class: It’s control the report execution. The base class is srsReportRunController.

Code Sample:

class SSRSDemoController extends SrsReportRunController
{

// To do
}

preRunModifyContract ( ): preRunModifyContract is called by report controller before the report is run.

prePromptModifyContract ( ): prePromptModifyContract is called by report controller before the parameter dialog is shown to the User.

Main() :
public static client void main(Argsargs)
{
//define the new object for controller class
SSRSDemoControllerssrsDemoController;
ssrsDemoController = new SSRSDemoController();

//pass the caller args to the controller
ssrsDemoController.parmArgs(args);
//set the report name and report design to run
ssrsDemoController.parmReportName(ssrsReportStr(Report Name, Design name));

//execute the report
srsDemoController.startOperation();
}

Create Report in Visual Studio

1. Open Visual studio.
2. Open a new Project.
3. Select Microsoft Dynamics AX in the installed templates.
4. Select report model.
5. Name your report than click Ok.

Setup Report :

Now need to add a report in our project.

You can rename your report to VendTableReport.

Once we have added the report, right click on the dataset and add a dataset. Right click on your newly added Dataset and select “Properties”. Select “Report Data provider” in the Data Source Type.

Click on the button provided against the query property and select our RDP class.

Design:

Now add AutoDesign in our report. We can add an auto design by right clicking on the designs or you can drag your VendTableDataset on the designs node.

Select a Layout template in the properties of your Auto Design

Select a Style template in the properties of your VendTableDatasetDesign node.

 

 Add report to AOT

The setup is completed. We need to view the report now. In order to do so, build the report by right clicking on your solution. After successful building of the report, deploy the report and add it to AOT.

Create an Output Menu item for your report

In order to view the report in Dynamics AX, add an output menu item.
Select SRRS Report in Report Type.
Select your report in Object and report design in ReportDesign.
Once you have setup the menu item, save it and open it. Here is the final report :

Output:

Advantage of SSRS Reports:

SSRS supports aggregating and displaying data from multiple data sources. This means you can show data from your AX production data, your Analysis Services cubes, your external database as well as data from a web service, all on a single report

SSRS reports can be published to a number of formats such as PDF, XPS, Excel and Word. In addition, SSRS has an extensible framework that allows you to add plugins to support other export formats.

The same report can be rendered from multiple locations such as Dynamics AX Client, Enterprise Portal, the Report Manager or even an external application,
thereby providing end-users with a lot of flexibility and power to access their data.