How to write the customized dialog lookup for SSRS report

Following Sample code is to create customized lookup for dialog field for SSRS report filter. In previous post we used contract class and RDP class , by using this ContractUIBuilder class we can overide the dialog lookup values of Contract class  parmMethods.

class ProfitabilityLookupsUIBuilder extends SrsReportDataContractUIBuilder

{

      DialogField             dialogCategory;

      DialogGroup           dialogGroup;

      boolean                  enable;

} 

//customized lookup method for a field(Category)

private void categoryLookup(FormStringControl _categoryLookup)

{

    Query                                      query = new Query();

    QueryBuildRange                   qbr;

    SysTableLookup                         sysTableLookup;
    // SysReferenceTableLookup     sysRefTableLookup; (For Reference field lookup in AX2012)

    QueryBuildDataSource              qbdsInventTable;

    ;

  

    sysTableLookup = SysTableLookup::newParameters(tablenum(InventTable), _categoryLookup);

   //sysRefTableLookup = SysReferenceTableLookup::newParameters(tablenum(InventTable), _categoryLookup);

  

    qbdsInventTable = query.addDataSource(tableNum(InventTAble)); 

    qbdsInventTable.addRange(fieldNum(InventTable, SupplierCategory).value("");    

    sysTableLookup.addLookupfield(fieldNum(InventTable,SupplierCategory));

    sysTableLookup.parmQuery(query);

    sysTableLookup.performFormLookup();

} 

public void build()

{

    UBMProductProfitabilityContract     rdpContract =  this.dataContractObject();

 

        dialogCategory = this.addDialogField(methodstr(UBMProductProfitabilityContract,parmcategory),rdpContract);

        dialogCategory.lookupButton(2);

} 

public void postRun()

{

    Dialog dialogLocal = this.dialog();

    DialogField dialogField,dialogField1;

    super(); 

    // This method should be called in order to handle events on dialogs.

    dialogLocal.dialogForm().formRun().controlMethodOverload(false); 

    // Override the methods of category  field. 

    dialogField1 = this.bindInfo().getDialogField(this.dataContractObject(), methodstr(UBMProductProfitabilityContract, parmcategory));

    dialogField1.registerOverrideMethod(methodstr(FormStringControl, lookup), methodstr(ProfitabilityLookupsUIBuilder,  categoryLookup), this);

}

The above class should be used in contract class as following

[DataContractAttribute,

SysOperationContractProcessingAttribute(classstr(ProfitabilityLookupsUIBuilder))

]

class ProfitabilityContract  

{

      SupplierCategory  category;                     

}

Add parmcategory method as shown below

 

[DataMemberAttribute('Category')

]

public AccountNum parmCategory(SupplierCategory _category = category)

{

       category = _ category;

       return category;

} 

------------- After this as usual need to write RDP class  --------------

 

Creating new SSRS report in AX2012

New SSRS report  in AX2012

1 . Create a temp table with the required fields

2. Create a query with required datasource

3.  Create a contract class (Report filters )

class UBMGrowthNetSalesContract

{

    FromDate            fromDate,fromDate2;

    ToDate              toDate,toDate2;

    InventSiteId        inventSite;

    InventLocationId    inventLocation;

    ItemGroupId         itemGroupId;

 

}

[DataMemberAttribute("FromDate1"),SysOperationDisplayOrderAttribute("1")]

public FromDate parmFromDate(FromDate    _fromDate = fromDate)

{

  fromDate = _fromDate;

  return fromDate;

}

[DataMemberAttribute("FromDate2"),SysOperationDisplayOrderAttribute("3")]

public FromDate parmFromDate2(FromDate    _fromDate2 = fromDate2)

{

  fromDate2 = _fromDate2;

  return fromDate2;

}

[DataMemberAttribute("ToDate1"),SysOperationDisplayOrderAttribute("2")]

public ToDate parmToDate(ToDate    _toDate = toDate)

{

  toDate = _toDate;

  return toDate;

}

 

[DataMemberAttribute("ToDate2"),SysOperationDisplayOrderAttribute("4")]

public ToDate parmToDate2(ToDate    _toDate2 = toDate2)

{

  toDate2 = _toDate2;

  return toDate2;

}

[DataMemberAttribute("Item Group"),SysOperationDisplayOrderAttribute("5")]

public ItemGroupId parmItemGroup(ItemGroupId    _itemGroupId = itemGroupId)

{

  ;

  itemGroupId = _itemGroupId;

  return itemGroupId;

}

[DataMemberAttribute("Warehouse"),SysOperationDisplayOrderAttribute("7")]

public InventLocationId parmLocationId(InventLocationId    _inventLocation = inventLocation)

{

  inventLocation = _inventLocation;

  return inventLocation;

}

[DataMemberAttribute("Site"),SysOperationDisplayOrderAttribute("6")]

public InventSiteId parmSiteId(InventSiteId    _inventSite = inventSite)

{

  inventSite = _inventSite;

  return inventSite;

}

 

4 . Create an RDP class with the required bussiness logic

 

[ SRSReportQueryAttribute (querystr(UBMGrowthNetSales)),

SRSReportParameterAttribute(classstr(UBMGrowthNetSalesContract))

]

class UBMGrowthNetSalesRDP extends SRSReportDataProviderBase

{

  UBMGrowthNetSales uBMGrowthNetSales;

}

 

 

 

[SRSReportDataSetAttribute("UBMGrowthNetSales")]

public ubmGrowthNetSales getUBMGrowthNetSales()

{

    select * from uBMGrowthNetSales;

    return uBMGrowthNetSales;

}

 

 

 

[SysEntryPointAttribute(false)]

public void processReport()

{

    QueryRun                                      queryRun1,queryRun2,queryRun3;

    Query                                             query1,query2,tableQuery,transQuery1,transQuery2;

    UBMGrowthNetSalesContract       ubmGrowthNetSalesContract;

    QueryBuildDataSource            queryBDSTable,qbdsInventTable,qbdsTable,qbdsTrans,qbdsTrans1,qbdsinventDim1,qbdsInventDim2;

    QueryBuildDataSource                  qbdsItemGroup;

    QueryBuildRange                          qbrItem;

    TransDate                                      _toDate,_fromDate,_toDate2,_fromDate2;

    ItemId                                             item,_itemId;

    InventSiteId                                    inventSite;

    InventLocationId                             inventLocationId;

    InventTable                                    inventTable;

    InventTrans                                   inventTrans1,inventTrans2;

    LineAmount                                   lineAmt,lineAmt1;

    InventDim                                      inventDim1,inventDim2;

    InventItemGroupItem                     itemGroupTable;

    ItemGroupId                                  itemGroup;

 

    ;

  //breakpoint;

    ubmGrowthNetSalesContract = this.parmDataContract() as UBMGrowthNetSalesContract;

 

    _toDate             = ubmGrowthNetSalesContract.parmToDate();

    _fromDate           = ubmGrowthNetSalesContract.parmFromDate();

    _toDate2            = ubmGrowthNetSalesContract.parmToDate2();

    _fromDate2          = ubmGrowthNetSalesContract.parmFromDate2();

    inventSite          = ubmGrowthNetSalesContract.parmSiteId();

    inventLocationId    = ubmGrowthNetSalesContract.parmLocationId();

    itemGroup           = ubmGrowthNetSalesContract.parmItemGroup();

 

    query1 = this.parmQuery();

    queryBDSTable = query1.dataSourceTable(tableNum(InventTable));

    qbrItem = queryBDSTable.findRange(fieldNum(InventTable,ItemId));

    item = qbrItem.value();

 

    tableQuery = new Query();

 

    qbdsInventTable = tableQuery.addDataSource(tableNum(Inventtable));

    qbdsInventTable.addRange(fieldNum(inventTable,ItemId)).value(item);

 

    qbdsItemGroup   = qbdsInventTable.addDataSource(tableNum(InventItemGroupItem));

    qbdsItemGroup.joinMode(JoinMode::ExistsJoin);

    qbdsItemGroup.addLink(fieldNum(InventTable,ItemId),fieldNum(InventItemGroupItem,ItemId));

       qbdsItemGroup.addRange(fieldNum(InventItemGroupItem,ItemGroupId)).value(queryValue(itemGroup));

 

    queryRun1 = new QueryRun(tableQuery);

    while(queryRun1.next())

    {

        inventTable = queryRun1.get(tableNum(inventTable));

 

        ubmGrowthNetSales.ItemId        = inventTable.ItemId;

        ubmGrowthNetSales.ItemName      = inventTable.itemName();

        ubmGrowthNetSales.FromDate      = _fromDate;

        ubmGrowthNetSales.FromDate2     = _fromDate2;

        ubmGrowthNetSales.ToDate        = _toDate;

        ubmGrowthNetSales.ToDate2       = _toDate2;

        //Lineamt value calculation

        transQuery1 = new Query();

        qbdsTrans = transQuery1.addDataSource(tableNum(inventTrans));

        qbdsTrans.addRange(fieldNum(Inventtrans,ItemId)).value(inventTable.ItemId);

        qbdsTrans.addRange(fieldNum(inventTrans,DatePhysical)).value(queryRange(_fromDate,_toDate));

 

        qbdsinventDim1 = qbdsTrans.addDataSource(tableNum(InventDim));

        qbdsinventDim1.joinMode(JoinMode::ExistsJoin);

        qbdsinventDim1.addLink(fieldNum(InventTrans,InventDimId),fieldNum(InventDim,inventDimId));

        qbdsinventDim1.addRange(fieldNum(InventDim,inventLocationId)).value(inventLocationId);

        qbdsinventDim1.addRange(fieldNum(InventDim,inventSiteId)).value(inventSite);

 

 

        queryRun2 = new QueryRun(transQuery1);

        while(queryRun2.next())

        {

            inventTrans1 = queryRun2.get(tableNum(InventTrans));

            lineAmt += inventTrans1.lineAmount();

        }

        ubmGrowthNetSales.NetSales1 =  lineAmt;

 

         // End of - Lineamt value calculation

 

        // LineAmt1 value calculation

        transQuery2 = new Query();

        qbdsTrans1 = transQuery2.addDataSource(tableNum(inventTrans));

        qbdsTrans1.addRange(fieldNum(Inventtrans,ItemId)).value(inventTable.ItemId);

        qbdsTrans1.addRange(fieldNum(inventTrans,DatePhysical)).value(queryRange(_fromDate2,_toDate2));

 

 

        qbdsinventDim2 = qbdsTrans1.addDataSource(tableNum(InventDim));

        qbdsinventDim2.joinMode(JoinMode::ExistsJoin);

        qbdsinventDim2.addLink(fieldNum(InventTrans,InventDimId),fieldNum(InventDim,inventDimId));

        qbdsinventDim2.addRange(fieldNum(InventDim,inventLocationId)).value(inventLocationId);

        qbdsinventDim2.addRange(fieldNum(InventDim,inventSiteId)).value(inventSite);

 

 

        queryRun3 = new QueryRun(transQuery2);

        while(queryRun3.next())

        {

            inventTrans2 = queryRun3.get(tableNum(InventTrans));

            lineAmt1 += inventTrans2.lineAmount();

        }

        ubmGrowthNetSales.NetSales2 =  lineAmt1;

 

        // End of - Lineamt1 value calculation

 

        if (lineAmt)

        {

            ubmGrowthNetSales.GrowthPercentage = ((lineAmt1 - lineAmt) / lineAmt) * 100;

 

        }

        lineAmt  =0;

        lineAmt1 =0;

        ubmGrowthNetSales.insert();

        ubmGrowthNetSales.clear();

 

    }

}

 

 

5.   Create required design format .

6.   Add to AOT

7 .  Build and deploy the report

 

How create the records on click event with required format

The following code helps to create new records in the current form on a button click

void clicked()

{

    Dialog                                 dialog;

    DialogField                         textField,field1,field2;

    UBM_BookValue               FromBook,ToBook,noOfHashs,fromLen,toLen;

    UBM_ReceiptBook           ReceiptBookTable;

    UBM_ReceiptBookId        formatText,commonText,hashText;

    str                                        hashstr;

    ;

    super();

    dialog        =  new Dialog("Create Receipt BookId's");

    dialog.addText("Enter the From Receipt BookId and To Receipt BookId");

    textField   = dialog.addField(extendedTypeStr(UBM_Format)," Format  : ");

    field1        = dialog.addField(extendedTypeStr(UBM_BookValue)," From Book");

    field2        = dialog.addField(extendedTypeStr(UBM_BookValue),"TO Book");

    dialog.run();

    if (dialog.closedOk())

    {

        formatText  = textField.value();

       //noOfHashs   = strNfind(formatText,"#",1,strLen(formatText));

        //hashstr     = subStr(formatText,strNfind(formatText,"#",1,strLen(formatText)),strLen(formatText));       

        FromBook        = field1.value();

        ToBook             = field2.value(); 

        while(FromBook <=  ToBook)

        {

            hashText   = strReplace(hashText,"#",int2str(0));

            ReceiptBookTable.ReceiptBookId    =  NumberSeq::numInsertFormat(any2int FromBook),formatText);

            ReceiptBookTable.Description   = ReceiptBookTable.ReceiptBookId;

            ReceiptBookTable.insert();

            FromBook++;

        }

    } 

    UBM_ReceiptBook_ds.research();

    UBM_ReceiptBook_ds.reread(); 

}