How to create SSRS report using Report data provider (RDP Report) in AX 2012

RDP or Business logic based SSRS Reports in Dynamics Ax 2012 R3.

Logic based report in MS Dynamics Ax 2012 can be develop in following steps

1. Create a temporary table
2. Define the report parameters
3. Add business logic for the report
4. Create a reporting project
5. Bind a report to a report data provider class

In graphical shape RDP reports will be as

Image Inspiration http://dynamics-ax.blogspot.com/2011/12/ax-2012-ax-ssrs-report-design-concepts.html

Now consider a scenario, where we have to display list of Item, quantity, Price and total amount sold to customers. It is relatively simple report but have to build this report based on RDP or Report Data Provider framework.

First step to open an Ax client. When Ax client open press Ctrl + shift +W keys to open Dev environment or AOT.

For all artifacts for report development will be a single place and we did not move to node to node in AOT we have to create a AX project.

You can find projects at View=>Projects => Public project.

Create a new project at and rename it with “CustomRDBReport”

Step 1 create a temp table.

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

• CustomerAccount
• CustomerName
• ItemId
• ItemName
• SalesPrice
• SalesQuantity
• SalesAmount

If we see these fields exists in SalesLine Table. So we drag them into our temp table, and rename them accordingly

Right click on project and create at table with Name “CustomerSalesTemp”,

From property window rename the table as “CustomerSalesTemp” and set  TableType to   tempDb

Now close all window, open AOT and opens salesLine table. From top menu click on windows => Tile and both tables comes in parallel to each other

and start drag and drop fields in temp table

Now save the table and rename the fields accordingly if required. Also add a new field with Name CustomerName with extended data Type with Name.

Right click compile and synchronize table.

Now create a AOT Query with Name QSalesLine. Add data source on SalesLine and Add following fields on Salesline table

For Date Fileter, we will use ShippingDateConfirmed on Date.

Safe this query.

Step 2 define the report parameters

For current report we required three parameters, Customer, From date and To date.

In Report Data Provider framework which is based on WCF, we have to create a data contract class.

Create a new class in, rename it, CustomerSalesDataContract.

In its declaration section create three variables

[DataContractAttribute]

class CustomerSalesDataContract

{

CustAccount CustomerAccount;

TransDate FromDate;

TransDate ToDate;

}

Now Create three data method

[

DataMemberAttribute(identifierStr(CustAccount)),

SysOperationLabelAttribute (“Customer Account”),

SysOperationHelpTextAttribute(“Customer Account”),

SysOperationDisplayOrderAttribute(“1″)

]

public CustAccount  parmCustomerAccount(CustAccount  _CustomerAccount = CustomerAccount)

{

CustomerAccount = _CustomerAccount;

return CustomerAccount;

}

[

DataMemberAttribute(identifierStr(FromDate)),

SysOperationLabelAttribute (“From Date”),

SysOperationHelpTextAttribute(“FromDate”),

SysOperationDisplayOrderAttribute(“2″)

]

public TransDate  parmFromDate(TransDate  _FromDate = FromDate)

{

FromDate = _FromDate;

return FromDate;

}

[

DataMemberAttribute(identifierStr(ToDate)),

SysOperationLabelAttribute (“To Date”),

SysOperationHelpTextAttribute(“To Date”),

SysOperationDisplayOrderAttribute(“3″)

]

public TransDate  parmToDate(TransDate  _ToDate = ToDate)

{

ToDate = _ToDate;

return ToDate;

}

Step 3 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 add create a new class “CustomerSalesDataProvider”

Extend this class SRSReportDataProviderBase

[

SRSReportParameterAttribute(classstr(CustomerSalesDataContract))

]

class CustomerSalesDataProvider extends  SRSReportDataProviderBase

{

CustomerSaleTemp _CustomerSaleTemp;

}

This Class two method, first is return the temp table, and second one is which contains the logic to populate  temp table.

[SRSReportDataSetAttribute(“CustomerSaleTemp”)]

public CustomerSaleTemp getCustomerSaleTemp()

{

select * from _CustomerSaleTemp;

return _CustomerSaleTemp;

}

public void processReport()

{

TransDate _FromDate;

TransDate _Todate;

AccountNum _CustAccount;

CustomerSalesDataContract dataContract;

Query query;

QueryRun queryRun;

QueryBuildDataSource queryBuildDataSource;

QueryBuildRange queryBuildRange;

QueryBuildRange ShippingDateConfirmedFilter;

SalesLine querySalesLine;

query = new Query(queryStr(“QSaleLine”));

dataContract = this.parmDataContract();

_CustAccount = dataContract.parmCustomerAccount();

_FromDate = dataContract.parmFromDate();

_Todate= dataContract.parmToDate();

queryBuildDataSource = query.dataSourceTable(tablenum(SalesLine));

if (_CustAccount)

{

queryBuildRange = queryBuildDataSource.findRange(fieldnum(SalesLine, CustAccount));

if (!queryBuildRange)

{

queryBuildRange = queryBuildDataSource.addRange(fieldnum(SalesLine, CustAccount));

}

}

ShippingDateConfirmedFilter = SysQuery::findOrCreateRange(query.datasourceTable(tableNum(SalesLine)),fieldNum(SalesLine,ShippingDateConfirmed));

ShippingDateConfirmedFilter.value(SysQuery::range(_FromDate,_Todate));

queryRun = new QueryRun(query); ttsbegin;

while(queryRun.next())

{ _CustomerSaleTemp.clear();

querySalesLine = queryRun.get(tablenum(SalesLine));

_CustomerSaleTemp.SalesPrice =  querySalesLine.SalesPrice;

_CustomerSaleTemp.ItemId =  querySalesLine.ItemId;

_CustomerSaleTemp.ItemDescription =  querySalesLine.Name;

_CustomerSaleTemp.SalesQty =  querySalesLine.QtyOrdered;

_CustomerSaleTemp.CustAccount =  querySalesLine.CustAccount;

_CustomerSaleTemp.CustomerName = CustTable::find(querySalesLine.CustAccount).name();

_CustomerSaleTemp.insert();

}

ttscommit;

}

Now compile the class, generate Incremental CIL.

Step 4 create a reporting project

Now open Visual studio and create Model project say “CustomSalesLineReport”.

From solution explorer, create a new report rename it RDPSalesLineReport

Step 5 Bind a report to a report data provider class

Now double click on report and open it in

Add new DataSet and rename it “DSSalesLine”. On right click and from property window set Data Source Type to “Report Data Provider”

And click on Query and from browser window select The data provider class we created in previous step

Click ok to create fields

Now drag and drop data set to Design node in report to create AutoDesign.  Rename it “RDPSalesLine”

Expand “RDPSalesLine” design and drag and drop CustAccount field from Data Set to Group and sort nodes

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

Save the report compile it, deploy it and add to AOT

Now switch back to AOT.  Create a new menu Item under Display node.

And set menu item Name as “mnuRDPSaleLine” and set its properties as follow

Save it and right click on menu item and open it

Set values for From Date and To date and run the report, Report will work with business logic as follow

 Reference taken by:- https://community.dynamics.com/ax/b/alirazatechblog/archive/2015/07/08/rdp-or-business-logic-based-ssrs-reports-in-dynamics-ax-2012-r3