Hi,
I am
excited to share this new source of data[RDP] for the dataset..We all know in
the older version, for a dataset we had only Query and Business logic as a
dataset source in SSRS reports. Now in the current version, we have actually 4
options : Query, Business Logic, Report data provider and AX Enum provider.
Today,
I will be covering the Report data provider. An RDP class is an X++ class that
is used to access and process data for a Reporting Services report. An RDP
class is an appropriate data source type when the following conditions are met.
hmmmm..
confused..let me explain with an example. It will be all clear soon
First
things first: The following elements are required to set RDP as your data
source type.
step 1:
Query
step 2: Temporary table – RDP class fills a temporary table with data that will
be used by Reporting Services to display the report.
[msdn help]
step 3:Data contract class – To define the parameters in the report.
step
4:Report data provider class – processes business logic based on parameters and
a query, and then returns the tables as a dataset for the report. [msdn help]
/*********************************************************************/
Let me
explain all the steps mentioned above with an example
For step 1: Create a new query by name DNSalesInvoiceQry and add the datasource as
Next step 2: Let us create a new temp table by name DNSalesInvoiceTmp and
add fields as shown below. Ensure that you set the Table type propert as
TempDB or InMemory
Use an
InMemory temporary table if the dataset is small, for reports that will display
fewer than 1000 records.
Use a
TempDB temporary table for large datasets to improve performance.
Step 3:
Now we need to Data Contract class – which will help to define parameters in
the report.
Create
a new class by name "DNSalesInvoiceContract" and add a SalesId global variable as shown below
class DNSalesInvoiceContract
{
SalesId salesId;
}
______________________________________________
Add one more parm method to it as shown below
[
DataMemberAttribute("SalesId")
]
public SalesId parmSalesId(SalesId _SalesId = salesId)
{
SalesId = _SalesId;
return salesId;
}
________________________________________________
Step
4:
DP class follows..
/// <summary>
/// Declares variables and tables for the <c>AssetBookCompare</c> report.
/// </summary>
[
SRSReportQueryAttribute(querystr(DNSalesInvoiceQry)),
SRSReportParameterAttribute(classstr(DNSalesInvoiceContract))
]
public class DNSalesInvoiceDP extends SRSReportDataProviderBase
{
DNSalesInvoiceTmp DNSalesInvoiceTmp;
SalesId SalesId;
}
___________________________________________________________________
private Query buildQuery( Query _query,
SalesId _SalesId)
{
if(_SalesId)
_query.dataSourceTable(tablenum(SalesTable),1).addRange(fieldnum(SalesTable, SalesId)).value(_SalesId);
return _query;
}
__________________________________________________________________
private void getReportParameters()
{
DNSalesInvoiceContract SalesInvoiceContract = this.parmDataContract();
SalesId = SalesInvoiceContract.parmSalesId();
}
_____________________________________________________________________
// <summary>
/// Executes temporary table.
/// </summary>
/// <returns>
/// The temporary table <c>LedgerJournalTmp</c>.
/// </returns>
[
SrsReportDataSetAttribute(tablestr(DNSalesInvoiceTmp))
]
public DNSalesInvoiceTmp getTempData()
{
select DNSalesInvoiceTmp;
return DNSalesInvoiceTmp;
}
_____________________________________________________________________
private void insertTempData(SalesTable _SalesTable,SalesLine _SalesLine,CustInvoiceJour _CustInvoiceJour)
{
SalesLine SalesLineloc;
int m;
DNSalesInvoiceTmp.SalesId = _SalesTable.SalesId;
DNSalesInvoiceTmp.CustAccount = _SalesTable.CustAccount;
DNSalesInvoiceTmp.InventLocationId = _SalesTable.InventLocationId;
DNSalesInvoiceTmp.SalesOrderDate = _SalesTable.createDate();
DNSalesInvoiceTmp.CurrencyCode = _SalesTable.CurrencyCode;
DNSalesInvoiceTmp.WorkerSalesResponsible = _SalesTable.WorkerSalesResponsible;
DNSalesInvoiceTmp.InvoiceDate = _CustInvoiceJour.InvoiceDate;
DNSalesInvoiceTmp.InvoiceId = _CustInvoiceJour.InvoiceId;
DNSalesInvoiceTmp.ItemId = _SalesLine.ItemId;
DNSalesInvoiceTmp.SalesQty = _SalesLine.SalesQty;
DNSalesInvoiceTmp.SalesPrice = _SalesLine.SalesPrice;
DNSalesInvoiceTmp.LineAmount = _SalesLine.LineAmount;
DNSalesInvoiceTmp.Logo = Companyimage::findByRecord(CompanyInfo::find()).Image;
DNSalesInvoiceTmp.Warehouse = InventLocation::find(DNSalesInvoiceTmp.InventLocationId).Name;
DNSalesInvoiceTmp.SalesResponsibleID = Hcmworker::find(DNSalesInvoiceTmp.WorkerSalesResponsible).PersonnelNumber;
DNSalesInvoiceTmp.SalesResponsibleName = Hcmworker::find(DNSalesInvoiceTmp.WorkerSalesResponsible).name();
DNSalesInvoiceTmp.ItemName = InventTable::find(DNSalesInvoiceTmp.ItemId).itemName();
DNSalesInvoiceTmp.CustName = CustTable::find(DNSalesInvoiceTmp.CustAccount).name();
DNSalesInvoiceTmp.CustAddress = CustTable::find(DNSalesInvoiceTmp.CustAccount).address();
DNSalesInvoiceTmp.AddressCity = Dirparty::primaryPOstalAddress(CustTable::find(DNSalesInvoiceTmp.CustAccount).Party).City;
select sum(LineAmount) from SalesLineloc where SalesLineloc.SalesId == _SalesTable.SalesId;
m = strLen(numeralsToTxt_in(SalesLineloc.LineAmount));
//strDel(numeralsToTxt_in(_SalesLine.LineAmount), m -5, m) +" "+ CompanyInfo::standardCurrency();
DNSalesInvoiceTmp.StrLineAmount = strDel(numeralsToTxt_in(SalesLineloc.LineAmount), m -5, m) +" " + CompanyInfo::standardCurrency();
DNSalesInvoiceTmp.insert();
}
__________________________________________________________________
[
SysEntryPointAttribute(false)
]
public void processReport()
{
SalesTable SalesTable;
SalesLine SalesLine;
CustInvoiceJour CustInvoiceJour;
DNSalesInvoiceContract contract;
QueryRun queryRun;
this.getReportParameters();
queryRun = new QueryRun(this.buildQuery(this.parmQuery(),SalesId));
while (queryRun.next())
{
SalesTable = queryRun.get(tableNum(SalesTable)) as SalesTable;
SalesLine = queryRun.get(tableNum(SalesLine))as SalesLine;
CustInvoiceJour = queryRun.get(tableNum(CustInvoiceJour))as CustInvoiceJour;
this.insertTempData(SalesTable, SalesLine, CustInvoiceJour);
}
}
Open
visual studio. Click on File >> New project and follow the below
process.
Now add
a new report to the report model by right clicking and selecting new report as
shown below
Rename
the report to "SR_CustTableRDPReport" as shown below by going to the
properties of the report
Now the
next thing what we have to do [the most awaited...] is to create a dataset for
this report.
Right
click on the DataSet and add new dataset as shown below. Rename it to
CustomerDataSet as shown below
Go to
its properties and rename it to "CustomerDataSet"
Now the
real trick, we have a datasource type property on the dataset properties.
Select report data
provider as the datasource type as show below.
Select
the query property and Click on the ellipsis (…) button to selct the RDP class
which you have created in the step 4 as shown below
This
will come up with all the RDP classes available in AX. Select SR_CustTableRDP
Class and click on next button to select the fields from the tmpTable to shown
it on the report.
Follow
the below screen shot to select the fields and click on Ok button
wonderful..we
are done with dataset..Only few steps now..Now we need to create design. Drag
and drop this CustomerDataSet to Designs node. It will automatically create the
Autodesign1 as shown below
Few
more properties for good look and feel of reports : Right click on the
Autodesign1 and set the
LayOutTemplate as ReportLayoutStyleTemplate as shown below
Then set
the style template as TableStyleTemplate as shown below
Also,
since I dont have data in default DAT Company, I would like to use company
parameter as well. so I am unhiding the company parameter to select the company
parameter along with the Account number [step 3 parameter]
To do
this, follow me and change the hidden property to visible as shown below for
company parameter
Thats it..Now let us run this report.
Right
click on the Autodesign1 and select option Preview and select the parameters as
shown below
Select
the report Tab to view the report. It will take some time to render data.
Here
comes the final data on the report – from the RDP class by inserting in to
temporary table and showing the same on to report.
hmmmm..
I am done..Hope you understood how RDP classes will help to render the data on
to report by using the temporary table concept and Data contract classes. In my
last post I have explained how to add this report to the AX menu item.. Follow
the same process and check the report by opening from within AX.That’s it for
now..
public static CustController construct(Args _args)
