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