Microsoft Dynamics AX Tools and Tutorials: AX-2012 - Developing SSRS reports using Report data Provider.

AX-2012 - Developing SSRS reports using Report data Provider.

In this article we will cover Report data provider (RDP) class and Report contract class. We will also learn how to implement these classes for our reporting needs. We will learn creating an report using the RDP class by following the step-by-step walkthrough.

Report Data Provider (RDP) Framework:-

A report data provider (RDP) class is an X++ class that is used to access and process data for a report. An RDP class is an appropriate data source type when the following conditions are met:

You cannot query directly for the data you want to render on a report.

The data to be processed and displayed is from Microsoft Dynamics AX.

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:

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.
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.

Note: 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.

Data Contract Class:-
This class is used to define one or more parameters that will be used in a SSRS report.We can define any number of parameters using X++ statements of any data type, which can be passed on to the RDP class. And then, we can use the same contracts to query data from the database engine which will decrease an overhead on execution of a query in SQL.

A data contract class is an X++ class which contains parm methods with the DataMemberAttribute defined at the beginning of the method.

Table
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.

Now consider a scenario, where we have to display list of customers transactions. It is relatively simple report but have to build this report based on RDP or Report Data Provider framework.

Steps:-

Create a temporary table
Define the report parameters
Add business logic for the report
Create a reporting project
Bind a report to a report data provider class

Step 1 :- Open an Ax client. and press Ctrl + shift +P keys to open Development work space.
You can find projects at View=>Projects => Public project.
Create a new project at and rename it with "CustTransactionRDPDemo"

Step 2:- create a temp table and rename it with "CustTransactionTMP" and set TableType to tempDb.

The major step in RDP report is decision the fields require in report, create a temp table and add these field in temp table. For current example what fields we required on report are as follow:-

Now save the table, right click compile and synchronize table.

Step 3 :- Now create a AOT Query with Name "CustomerTransactionQuery". Add data source "CustTrans" and Save this query.

Step 4:- Define the report parameters For report we required three parameters: ["Customer Account", "From date" and "To date"].

In Report Data Provider framework we have to create a data contract class.
Create a new class in, rename it "CustomerTransactionContract".
In its declaration section create three variables

[DataContractAttribute]
class CustomerTransactionContract
{
  CustAccount CustomerAccount;
  TransDate FromDate;
  TransDate ToDate;
}

Create three data method that has setters,getters and the DataContractAttribute attribute.
-First parameter is "CustAccount"

[DataMemberAttribute(identifierStr(CustAccount)),
SysOperationLabelAttribute ("Customer Account"),
SysOperationHelpTextAttribute("Customer Account"),
SysOperationDisplayOrderAttribute("1")]
public CustAccount  parmCustomerAccount(CustAccount _CustomerAccount = CustomerAccount)
{
   CustomerAccount = _CustomerAccount;
   return CustomerAccount;
}

-Second parameter is "FromDate"

[DataMemberAttribute(identifierStr(FromDate)),
SysOperationLabelAttribute ("From Date"),
SysOperationHelpTextAttribute("FromDate"),
SysOperationDisplayOrderAttribute("2")]
public TransDate parmFromDate(TransDate _FromDate = FromDate)
{
   FromDate = _FromDate;
   return FromDate;
}

-Third parameter is "ToDate"

[DataMemberAttribute(identifierStr(ToDate)),
SysOperationLabelAttribute ("To Date"),
SysOperationHelpTextAttribute("To Date"),
SysOperationDisplayOrderAttribute("3")]
public TransDate parmToDate(TransDate _ToDate = ToDate)
{
   ToDate = _ToDate;
   return ToDate;
}

Step 5:- Add business logic for the report.

In Report data provider framework we have to write Data provider classes,which contain business logic to populate temp table.For this we have to create a new class"CustomerTransactionDP"
and extend from SRSReportDataProviderBase and add "SRSReportParameterAttribute" Attribute to define the contract class.

[SRSReportParameterAttribute(classstr(CustomerTransactionContract))
,SRSReportQueryAttribute(queryStr(CustomerTransactionQuery))]
class CustomerTransactionDP extends SRSReportDataProviderBase
{
   CustTransactionTMP _CustTransactionTMP;
}

Create two method,one will work as getter method to return the temp table,and second one which contain logic that populate temp table.

[SRSReportDataSetAttribute("CustTransactionTMP")]
public CustTransactionTMP getCustTransactionTMP()
{
   select * from _CustTransactionTMP ;
   return _CustTransactionTMP ;
}

public void processReport()
{
   TransDate _FromDate;
   TransDate _Todate;
   AccountNum _CustAccount;
   CustomerTransactionContract dataContract;
   Query query;
   QueryRun queryRun;
   QueryBuildDataSource queryBuildDataSource;
   QueryBuildRange queryBuildRange;
   QueryBuildRange TransDateFilter;
   custtrans querycusttrans;
   query = this.parmQuery();
   dataContract = this.parmDataContract();
   _CustAccount = dataContract.parmCustomerAccount();
   _FromDate = dataContract.parmFromDate();
   _Todate= dataContract.parmToDate();

   queryBuildDataSource = query.dataSourceTable(tablenum(CustTrans));
   if (_CustAccount)
   {
      queryBuildRange = queryBuildDataSource.findRange(fieldnum(CustTrans, AccountNum));
      if (!queryBuildRange)
      {
        queryBuildRange = queryBuildDataSource.addRange(fieldnum(CustTrans, AccountNum));
      }
      queryBuildRange.value(_CustAccount);
   }
   TransDateFilter =  SysQuery::findOrCreateRange(query.datasourceTable(tableNum(custtrans)),fieldNum(custtrans,transdate));
   TransDateFilter.value(SysQuery::range(_FromDate,_Todate));

   queryRun = new QueryRun(query); 
   ttsbegin;
   while(queryRun.next())
   {
      _CustTransactionTMP .clear();
      querycusttrans = queryRun.get(tablenum(custtrans));
      _CustTransactionTMP.AccountNum =  querycusttrans.AccountNum;
      _CustTransactionTMP.AmountMST =  querycusttrans.AmountMST;
      _CustTransactionTMP.Invoice =  querycusttrans.Invoice;
      _CustTransactionTMP.TransDate =  querycusttrans.TransDate;
      _CustTransactionTMP.TransType =  querycusttrans.TransType;
      _CustTransactionTMP.Txt =  querycusttrans.Txt;
      _CustTransactionTMP.Voucher =  querycusttrans.Voucher;
      _CustTransactionTMP.CustName = CustTable::find(querycusttrans.AccountNum).name();    
      _CustTransactionTMP.insert();
   }
   ttscommit;   
}

Now compile the class, generate Incremental CIL.
Step 6:- Open Visual studio and create a new report project named "CustTransRDPReport".

Datasets: Datasets retrieve data from the AOT query. It acts as a bridge between AX and the SSRS report. Only the fields added in the datasets can be used in a report.
Designs: It defines the layout of the report.
Images: It contains the images that you want to display in the SSRS report.
Data Methods: It contains the business logic which can then be used in the report.
Parameters: It is used to apply filtering to the data in a report.

Add new Dataset named "CustTransaction",navigate to the property of the dataset and
set Data Source Type to “Report Data Provider”.

Click on Query and from browser window select The data provider class we created in previous step "CustomerTransactionDP"

Expand parameter of report and open the property of CustAccount parameter and set its allow blank to true and nullable to true, so if no customer is selected, report will run for all customer in legal entity.

There are two types of designs that can be created in a SSRS report:
-Auto Design: Visual studio automatically creates a design based on the dataset provided. Auto design is the preferred method because it is easy and usually fulfills the requirements for the majority of scenarios.
-Precision Design: It is used when you need custom placement of fields or the layout of the report is too complex.

In this demo we will use Precision Design:-

1- Navigate to Designs node and add new precision design named "Report".

2- Navigate to report node and press edit and you can design the layout as required.

3-then save and deploy the project from Solution explorer.

Step 7 :- To open the report in AX, a menu item is required. Create a menu item that will open the report from AX.

Now switch back to AOT. Create a new menu Item of type "Output"