SSRS : Load Control based on Other parameter Control

Recently I came through a scenario in which I need to fill Invoice Lookup according to the selected Sales Order. To achieve this problem statement I have perform following task to accomplish this

First lets create the sales order and Invoice Lookup Parameter in Contract Class

Class declaration code is like below

{

    FromDate                    fromDate;

    ToDate                      toDate;

    List                        listSO,listInvoice,listCustomer;

}

Note here I am using SO and Invoice as a List type. it is because we are creating a multi select lookup for both.

Now the sales Order Parameter code in contract class is below

[

    DataMemberAttribute('Sales Order'),

      AifCollectionTypeAttribute("Sales Order", Types::String),

    SysOperationLabelAttribute(literalStr("Sales Order")),

    SysOperationHelpTextAttribute(literalStr("Please Select Sales Order")),

    SysOperationDisplayOrderAttribute('05')

]

public List parmSO(List _listSO=listSO)

        {

            listSO=_listSO;

            return listSO;

        }

Similarly invoice parameter code is like

[

    DataMemberAttribute('Invoice'),

    AifCollectionTypeAttribute("Invoice", Types::String),

    SysOperationLabelAttribute(literalStr("Invoice")),

    SysOperationHelpTextAttribute(literalStr("Please Select Invoice")),

    SysOperationDisplayOrderAttribute('04')

]

public List parmInvoice(List _listInvoice=listInvoice)

        {

            listInvoice=_listInvoice;

            return listInvoice;

        }

We are done with the contract class now lets move to the UI Builder Class

In UI Class first we write the method responsible for sales Order lookup but wait before we jump into the code we need to create the AOT Query which is going to be used by the code we are going to write for Lookup below Image is the Sales Order Lookup AOT Query

On the datasource Node drag the salesTable and set the Dynamic property “No” of the datasource fields Node as highlighted in above Image and than Add the SalesId field under the fields node by right clicking on field node click New than Add -> Field.   Refer to Image below. Select the salesId field here

After creating SO Lookup AOT Query now we will create a Lookup Method in UI Builder class which call above AOT query and display sales Order Lookup. Code of SO Lookup is as below

private void LookUpSO(FormStringControl _control)

        {

          Query       query;

          SysLookupMultiSelectGrid    sysLookupSO;

                    sysLookupSO = SysLookupMultiSelectGrid::construct(_control, _control);

                    query = new query("SL_SOLookup");

                    sysLookupSO.parmQuery(query);

                    sysLookupSO.run();

        }

If you look Lookup Code its query Object is initializing with our created sales Order Lookup AOT Query as “SL_SOLookup”

Now Repeat the above AOT Query creation steps and create a Invoice Lookup AOT Query similar to Sales Order Lookup Below is the InvoiceLookup snap shot for reference

Note I have used the custInvoiceJour as datasource and InvoiceId in fields of datasource as dynamic property set to “No”

Let’s create Lookup Invoice method which will call InvoiceLookup AOT Query created above

private void LookupInvoice(FormStringControl _control)

 {

     Query                      query;

     container                  socontainer;

     str                        selectedRecId;

     SalesId                    selectedSalesId;

     SysLookupMultiSelectGrid    sysLookupInvoice;

     int          counter;

        sysLookupInvoice = SysLookupMultiSelectGrid::construct(_control, _control);

     if (sysLookupSO)

     {

         socontainer = sysLookupSO.getSelected();

     }

         query = new query("SL_InvoiceLookup");

         for(Counter=1; Counter<=conLen(socontainer); Counter++)

         {

             selectedSalesId = this.getSalesId(conPeek(socontainer,counter));

             query.dataSourceTable(tableNum(CustInvoiceJour)).addRange(fieldNum(CustInvoiceJour,SalesId)).value(SysQuery::value(selectedSalesId));

         }

        sysLookupInvoice.parmQuery(query);

         sysLookupInvoice.run();

 }

Above Invoice Lookup Method is calling private Method in UI builder class as “getSalesId” which is as below

private SalesId getSalesId(RecId _recId)

{

    SalesTable                  salesTable;

    select firstOnly salesTable where salesTable.recId == _recId;

   return salesTable.salesId;

}

After we done with the Lookup Code now move to the postBuild()

 Method and its code will be as below

postBuild()

{

dfSO = this.bindInfo().getDialogField(dataContract, methodStr(SL_SalesTaxInvoiceContract, parmSO));

           if (dfSO)

            {

                dfSO.registerOverrideMethod(methodStr(FormStringControl, lookup), methodStr(SL_SalesTaxInvoiceUI, LookUpSO), this);

                dfSO.lookupButton(FormLookupButton::Always);

            }

             

               dfInvoice = this.bindInfo().getDialogField(dataContract, methodStr(SL_SalesTaxInvoiceContract, parmInvoice));

           if (dfInvoice)

            {

                dfInvoice.registerOverrideMethod(methodStr(FormStringControl, lookup), methodStr(SL_SalesTaxInvoiceUI, LookUpInvoice), this);

                dfInvoice.lookupButton(FormLookupButton::Always);

            }

}

In above post build method dfSO and dfInvoice are the dialog field declare in class declaration of UI builder class as below

{

DialogField             dfSO,dfInvoice,dfCustomer,dFromDate,dTodate;

   

}

Do not forget to add Build Method in UI Builder class. Build Method Looks Like Below :

Public void build()

{

    dataContract          = this.dataContractObject() as TK_SalesTaxInvoiceContract;

            dFromDate     = this.addDialogField(methodStr(TK_SalesTaxInvoiceContract, parmFromDate), dataContract);

            dToDate       = this.addDialogField(methodStr(TK_SalesTaxInvoiceContract, parmToDate), dataContract);

            dfSO          = this.addDialogField(methodstr(TK_SalesTaxInvoiceContract, parmSO), dataContract);

            dfInvoice     = this.addDialogField(methodstr(TK_SalesTaxInvoiceContract, parmInvoice), dataContract);

            dfCustomer    = this.addDialogField(methodStr(TK_SalesTaxInvoiceContract , parmCustomer),dataContract);

}

We are done with the code part when Running Report and selecting the SO-000014 the Invoice against it populated in Invoice Lookup as shown Below

And Changing the SO selection will change the invoice population too as shown in below image

Remember one good thing we have made the sales Order and Invoice Lookup as multi select parameter so that we can select multi Sales Order and according to selected multiple sales order multiple invoice will be displayed as shown in below image

So that s all i have for this post. do let me know for any feed back or any confusion/ suggestion