SSRS
report in AX 2012 with the help of RDP classes.
Steps:
1. Create
1 temporary table with those fields which you want in SSRS report.
2. Create
1 data provider class and extends SRSReportDataProviderBase
class.
3. Create
1 contract class for dialog parameters with methods [DataMemberAttributes] and
class declaration have [DataContractAttribute].
4. If
we want to add any lookup or want to manipulate dialog field by means of other
fields or individually for and dialog field then, create 1 UIbuilder class
which extends SRSReportDataContractUIBuilder
class.
5. If
we want pass default parameters for the dialog field we can create 1 controller
class extends SRSReportRunController.
6. Report
design with help of visual studio 2010.
7. Deployment
of report.
Requirement:
If we want to display customers (one time and
permanent) with their invoice and total amount excluding tax.
Parameters:
·
Customer
string with the look up so that user can print either all or 1.
·
One
time
checkbox if this is check customer lookup will show those 1 time customer list
else only permanent customer list.
Functionality:
·
If one time check and no customer
selected report will display all 1 time customers with the desired value.
·
If one time check and customer selected
report will display customer with the desired value.
·
If one time is not check and no customer
is selected then report will display all permanent customers’ value.
·
If one time is not check and customer is
selected then report will display selected customers’ value.
Elaborated
steps:
1.
Create
1 temporary table with those fields which you want in SSRS report.
·
AOT> DataDictionary> Tables>
New tstCustInvoiceDataTmp table.
·
Set TableType property as InMemory.
·
Create fields.
Sl.No
|
Field name
|
EDT
|
1.
|
CustAccount
|
CustAccount
|
2.
|
Name
|
Name
|
3.
|
InvoiceId
|
InvoiceId
|
4.
|
TotalAmount
|
Amount
|
2. Create 1 data provider class and
extends SRSReportDataProviderBase class.
AOT>Classes> new
tstCustInvoiceDataProvider extends SRSReportDataProviderBase
Class tstCustInvoiceDataProvider extends SRSReportDataProviderBase
{
// declare
temporary table buffer
tstCustInvoiceDataTmp custInvoiceDataTmp;
//
declare contract class variable for accessing parameter’s value
tstCustInvoiceContract
custInvoiceContract;
}
|
[SRSReportDataSetAttribute(tableStr(‘tstCustInvoiceDataTmp’))]
Public tstCustInvoiceDataTmp getCustInvoiceDataTmp()
{
//
Specify condition on which basis report will display data.
Select * from custInvoiceDataTmp;
return custInvoiceDataTmp;
}
|
///<summary>
/// Processes the SQL
Server Reporting Services report business logic
/// </summary>
/// <remarks>
/// This method
provides the ability to write the report business logic. This method will be
called by
/// SSRS at runtime.
The method should compute data and populate the data tables that will be
returned
/// to SSRS.
/// </remarks>
Public void
processReport()
{
CustTable custTbale;
SalesTable salesTable;
SalesLine salesLine;
;
custInvoiceContract = this.parmDataContract()
as tstCustInvoiceContract;
while select CustTable
join SalesTable
join salesLine
where salesTable.CustAccount ==
custTable.AccountNum
&& SalesLine.SalesId == salesTable.SalesId
&& salesTable.SalesStatus ==
salesStatus::Invoice
&& (custTable.AccountNum
== custInvoiceContract.parmAccountNUm()
|| CustTable.OneTimeCust
==
custInvoiceContract.parmOneTime())
{
custInvoiceDataTmp.CustAccount
= custTable.AccountNum;
custInvoiceDataTmp.Name = CustTable.Name;
custInvoiceDataTmp.InvoiceId = SalesTable.InvoiceId;
custInvoiceDataTmp.Amount =
custInvoiceDataTmp.insert();
}
}
|
3.
Create
1 contract class for dialog parameters with methods [DataMemberAttributes] and
class declaration have [DataContractAttribute].
[DataContractAttribute, SysOperationContractProcessingAttribute(classstr(tstCustInvoiceDataUIBuilder))].
Class
tstCustInvoiceContract
{
NoYesId oneTimeCust;
CustAccount custAccount;
}
|
[DataMemberAttributes]
CustAccount parmAccountNum(CustAccount _custAccount =
custAccount)
{
custAccount = _custAccount;
return custAccount;
}
|
[DataMemberAttributes,
SysOperationLabelAttribute(literalStr("One time"))]
NoYesId parmOneTime(NoYesId _oneTime =
oneTimeCust)
{
oneTimeCust = _oneTime;
return oneTimeCust;
}
|
4.
If
we want to add any lookup or want to manipulate dialog field by means of other
fields or individually for and dialog field then, create 1 UIbuilder class
which extends SRSReportDataContractUIBuilder
class.
Class
tstCustInvoiceDataUIBuilder extends SRSReportDataContractUIBuilder
{
dialogField dlgCustAccount;
dialogField dlgOnetimeCust;
tstCustInvoiceContract
custInvoiceContract;
}
|
Public void postBuild()
{
;
Super();
custInvoiceContract = this.dataContractObject();
dlgCustAccount = this.bindInfo().getDialogField(custInvoiceContract, methodStr(custInvoiceContract,
parmCustAccount));
dlgOnetimeCust =
this.bindInfo().getDialogField(custInvoiceContract,
methodStr(custInvoiceContract, parmOneTime));
//register the method we want to override
dialogField.registerOverrideMethod(methodstr(FormStringControl,
lookup), methodstr(SRCustomLookupsUIBuilder, accountNumLookup),
this);
}
|
private void accountNumLookup(FormStringControl accountNumLookup)
{
Query query
= new Query();
QueryBuildDataSource qbds_CustTable;
SysTableLookup sysTableLookup;
QueryBuildRange qbr;
if (accountNumLookup != null)
{
// Create an instance of SysTableLookup with
// the current calling form control.
sysTableLookup
= SysTableLookup::newParameters(tablenum(CustTable), accountNumLookup);
//sysTableLookup.addLookupMethod(
// Add fields to be shown in the lookup form.
qbds_CustTable
= query.addDataSource(tableNum(CustTable));
sysTableLookup.addLookupfield(fieldnum(CustTable, AccountNum), true);
sysTableLookup.addLookupfield(fieldnum(CustTable, Name),false);
qbr
= qbds_CustTable.addRange(fieldNum(CustTable,OneTime));
qbr.value(dlgOnetimeCust.value());
sysTableLookup.parmUseLookupValue(false);
sysTableLookup.parmQuery(query);
// Perform the lookup.
sysTableLookup.performFormLookup();
}
}
|
public boolean OneTimeModified(FormStringControl _control)
{
dlgOnetimeCust.value(_control.valueStr());
dlgCustAccount.value('');
return true;
}
|
5.
If
we want pass default parameters for the dialog field we can create 1 controller
class extends SRSReportRunController.
Class
tstCustInvoiceDataController extends SRSReportRunController
{
}
|
protected void prePromptModifyContract()
{
tstCustInvoiceContract
custInvoiceContract;
super();
// defaulting parameters
custInvoiceContract = this.parmReportContract().parmRdpContract() as tstCustInvoiceContract;
custInvoiceContract.parmNoYesId(NoYes::Yes);
}
|
public static client void main(Args args)
{
//define the new object
for controller class
tstCustInvoiceDataController custInvioceDataController;
custInvioceDataController = new tstCustInvoiceDataController
();
//pass the caller args
to the controller
custInvioceDataController.parmArgs(args);
//set the report name
and report design to run custInvioceDataController.parmReportName(ssrsReportStr(tstCustInvDataReport,CustInvoiceDesign));
//execute the report
custInvioceDataController.startOperation();
}
|
6. Report design with help of visual
studio 2010.
·
Open Visual studio. Go
to File>New > Project
In the Installed templates section select Microsoft
Dynamics AX and then select Report Model in the right
pane. Name the project tstCustInvDataReport and press Ok.
- A new project will be created as shown below (Solution
explorer)
·
Now add a new report in the project by right
clicking on the project tstCustInvDataReport > Add > Report
- A report will be added to the project with the name Report1.
Rename the report tstCustInvDataReport.
- Now double click the report to open it.
- The description of the individual node is given below:
o
Datasets: Datasets retrieve data from RDP class. It acts
as a bridge between AX and the SSRS report. Only the fields added in the
datasets can be used in a report.
o
Designs: It defines the layout of the report.
o
Images: It contains the images that you want to
display in the SSRS report.
o
Data
Methods: It contains the
business logic which can then be used in the report.
o
Parameters: It is used to apply filtering to the data in a
report. All the parameters defined in the data contract class are automatically
added here when the RDP class is defined in the datasets.
·
Now you
will want to create a new Dataset by right clicking Datasets àAdd Dataset. Name it CustInvoiceDetail
- Select the CustInvoiceDetail dataset and open the properties window. Set the Data
Source Type to Report Data Provider. Then select
the Query field. An ellipse button appears. Click it to
open a dialog box.
- This dialog box lists all the RDP classes present in
the AOT. Select tstCustInvoiceDataProvider
and press Next.
- Select the fields to be displayed in the report and
press OK. Only the fields selected in this dialog box can be
shown in the report.
- There are two types of designs that can be created in a
SSRS report:
o
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 majority scenarios.
o
Precision
Design: This is used when
you need custom placement of fields or the layout of the report is too complex.
- In this demo we will use Auto Design. Now right click
the Designs nodeàAdd à Precision
Design. A new design is added. Rename it CustInvoiceDesign.
It is recommended that you set the name of the Design to either ‘Design‘or
‘Report‘.
·
Design the report
based on requirement.
7.
Deployment
of report.
·
Right click on the solution and select
“Build”
·
Right click on the solution and select
“Deploy”
·
Right click on the solution and select
“Add to AOT”
·
Go to environment: refresh the SSRS
library and right click on report and select “Deploy element”
·
Create 1 output menu item with
properties Object Type: class and
reference object “tstCustInvoiceDataController”
No comments:
Post a Comment