Filter Second lookup based on First Value – Ax 2012 SSRS
Hi Folks,
I got one requirement to get invoices of a customer on the particular month range in SSRS Report Dialog. I have achieved that and want share. Here we go…..
Create a Contract Class with 4 parameter methods for Customer account, Month, Year, InvoiceId and follow the below code
1) Create a class for UI Builder
class SampleReportUIBuilder extends SrsReportDataContractUIBuilder
{
DialogField dialogCustomer;
DialogField dialogMonth;
DialogField dialogYear;
DialogField dialogInvoiceId;
SampleDataContract dataContract;
}
2) override the build method
public void build()
{
Dialog dialogLocal;
dialogLocal = this.dialog();
dataContract = this.dataContractObject();
this.addDialogField(methodStr(SampleDataContract, parmCustAccount), dataContract);
this.addDialogField(methodStr(SampleDataContract, parmMonth), dataContract );
this.addDialogField(methodStr(SampleDataContract, parmYear), dataContract );
this.addDialogField(methodStr(SampleDataContract, parmInvoiceId), dataContract );
}
3) override the initializeFields method
public void initializeFields()
{
dataContract= this.dataContractObject();
}
4) override the getFromDialog Method
public void getFromDialog()
{
comparisionDataContract = this.dataContractObject();
super();
}
5) add new method for CustomerAccount Lookup
public void lookupCustomerAcc(FormStringControl _control)
{
Query query = new Query();
QueryBuildDataSource qbdsCustomer;
QueryBuildFieldList qbfsCustomerFieldList;
SysTableLookup sysTableLookup;
;
sysTableLookup = SysTableLookup::newParameters(tableNum(CustTable), _control);
qbdsCustomer = query.addDataSource(tableNum(CustTable));
qbfsCustomerFieldList= qbdsCustomer .fields();
qbfsCustomerFieldList.dynamic(false);
qbfsCustomerFieldList.clearFieldList();
qbfsCustomerFieldList.addField(fieldNum(CustTable, AccountNum));
qbfsCustomerFieldList.addField(fieldNum(CustTable, Party));
// Perform Lookup
sysTableLookup.parmQuery(query);
sysTableLookup.addLookupfield(fieldnum(CustTable, Id));
sysTableLookup.addLookupfield(fieldnum(CustTable, Party));
sysTableLookup.performFormLookup();
}
6) add Modified method for CustomerAcc
public boolean customerAccModified(FormStringControl _control)
{
dialogCustomer.value(_control.valueStr());
return true;
}
7) add new method for Year Lookup
//BP deviated
public void lookupYear(FormStringControl _control)
{
Query query = new Query();
YearBase maxYear;
YearBase minYear;
YearBase currentYear;
// new own tmp table to hold year
MyOwnYearTmp yearTmp;
SysTableLookup sysTablelookup;
sysTablelookup = SysTableLookup::newParameters(tableNum(KepYearTmp),_control);
minYear = 1965;
maxYear = year(systemDateGet());
yearTmp.recordLevelSecurity(true);
for(currentYear = maxYear; currentYear >= minYear; currentYear--)
{
yearTmp.YearBase = currentYear;
yearTmp.insert();
}
sysTablelookup.addLookupfield(fieldNum(KepYearTmp,YearBase));
sysTableLookup.parmTmpBuffer(yearTmp);
sysTableLookup.performFormLookup();
}
8) add new Modified method for Year
public boolean yearModified(FormStringControl _control)
{
dialogYear.value(_control.valueStr());
return true;
}
9) add InvoiceId lookup, this lookup will get data based on the selected customer, month and year
public void lookupInvoiceId(FormStringControl _control)
{
Query query = new Query();
QueryBuildDataSource qbdsCustInvoiceTable;
QueryBuildRange qbrCustInvoiceTable;
QueryBuildFieldList qbfsCustInvoiceTableFieldList;
SysTableLookup sysTableLookup;
TransDate invStDate, invEndDate;
container conDates;
;
sysTableLookup = SysTableLookup::newParameters(tableNum(CustInvoiceTable), _control);
// my own class to get month startdate and enddate from dialog
conDates= MyDateClass::getDateRange(dialogMonth.value() , dialogYear.value() , false, true);
invStDate = conPeek(conDates, 1);
invEndDate = conPeek(conDates, 2);
// CustInvoiceTable DataSource
qbdsCustInvoiceTable = query.addDataSource(tableNum(CustInvoiceTable));
qbdsCustInvoiceTable.addRange(fieldNum(CustInvoiceTable, InvoiceAccount)).value(queryValue(dialogCustomer.value()));
qbdsCustInvoiceTable.addRange(fieldNum(CustInvoiceTable, InvoiceDate)).value(queryValue(invStDate));
qbdsCustInvoiceTable.addRange(fieldNum(CustInvoiceTable, InvoiceDate)).value(queryValue(invEndDate));
qbdsCustInvoiceTable.addRange(fieldNum(CustInvoiceTable, InvoiceStatus)).value(queryValue(InvoiceStatuses::Posted));
qbdsCustInvoiceTable.addRange(fieldNum(CustInvoiceTable, InvoiceType)).value(queryValue(InvoiceTypes::Invoice));
// CustInvoiceTable FieldList
qbfsCustInvoiceTableFieldList = qbdsCustInvoiceTable.fields();
qbfsCustInvoiceTableFieldList.dynamic(false);
qbfsCustInvoiceTableFieldList.clearFieldList();
qbfsCustInvoiceTableFieldList.addField(fieldNum(CustInvoiceTable, InvoiceAccount));
qbfsCustInvoiceTableFieldList.addField(fieldnum(CustInvoiceTable, InvoiceId));
qbfsCustInvoiceTableFieldList.addField(fieldNum(CustInvoiceTable, McsCmBilStartDate));
qbfsCustInvoiceTableFieldList.addField(fieldNum(CustInvoiceTable, McsCmBilEndDate));
qbfsCustInvoiceTableFieldList.addField(fieldNum(CustInvoiceTable, McsCmBilInvoiceStatus));
qbfsCustInvoiceTableFieldList.addField(fieldNum(CustInvoiceTable, McsCmBilInvoiceType));
// Perform Lookup
sysTableLookup.parmQuery(query);
sysTableLookup.addLookupfield(fieldnum(CustInvoiceTable, InvoiceId));
sysTableLookup.addLookupfield(fieldnum(CustInvoiceTable, McsConnectionId));
sysTableLookup.performFormLookup();
}
10) add new Modified method for InvoiceId
public boolean InvoiceIdModified(FormStringControl _control)
{
dialogInvoiceId.value(_control.valueStr());
return true;
}
11) override the PostBuild method
public void postBuild()
{
super();
dialogCustomer = this.bindInfo().getDialogField(this.dataContractObject(),methodStr(SampleDataContract, parmCustomer));
if (dialogCustomer)
{
dialogCustomer.lookupButton(2);
}
// register override method for lookup
dialogCustomer.registerOverrideMethod(methodStr(FormStringControl, lookup), methodStr(SampleReportUIBuilder , lookupCustomerAcc), this);
// register override method for modified
dialogCustomer.registerOverrideMethod(methodStr(FormStringControl, modified), methodStr(SampleReportUIBuilder , customerAccModified), this);
// From binding info, get the dialog field for racecode attribute
dialogMonth = this.bindInfo().getDialogField(this.dataContractObject(),methodStr(SampleDataContract, parmMonth));
// From binding info, get the dialog field for racecode attribute and add button
dialogYear = this.bindInfo().getDialogField(this.dataContractObject(),methodStr(SampleDataContract,parmYear));
if (dialogYear)
{
dialogYear.lookupButton(2);
}
// register override method for lookup
dialogYear.registerOverrideMethod(methodStr(FormStringControl, lookup), methodStr(SampleReportUIBuilder , lookupYear), this);
// register override method for modified
dialogYear.registerOverrideMethod(methodStr(FormStringControl, modified), methodStr(SampleReportUIBuilder , yearModified), this);
dialogInvoiceId = this.bindInfo().getDialogField(this.dataContractObject(),methodStr(SampleDataContract, parmInvoiceId));
if (dialogInvoiceId)
{
dialogInvoiceId.lookupButton(2);
}
// register override method for lookup
dialogInvoiceId.registerOverrideMethod(methodStr(FormStringControl, lookup), methodStr(SampleReportUIBuilder , lookupInvoiceId), this);
// register override method for modified
dialogInvoiceId.registerOverrideMethod(methodStr(FormStringControl, modified), methodStr(SampleReportUIBuilder , InvoiceIdModified), this);
}
11) Use the UI Builder class in Contract Calss as below
[
DataContractAttribute,
SysOperationContractProcessingAttribute(classstr(SampleReportUIBuilder)),
SysOperationGroupAttribute('Criteria', "@SYS13128", "1")
]
class SampleDataContract implements SysOperationInitializable, SysOperationValidatable
{
CustomerAcc CustomerAcc;
TransDate invoiceDate;
MonthsOfYear invoiceMonth;
Year invoiceYear;
InvoiceId invoiceId;
}
Hope it will help……
// Vasanth Arivali