query.findQueryFilter to find parameter

In controller class:

protected container preRunValidate()
{
    container ret;
    QueryRun                queryRun;
    int cnt,i;
    Query                   query = this.getFirstQuery();
    QueryBuildRange range;
    QueryBuildDataSource    qbds;
    str filter = "";
   
    ret = super();

    qbds = query.dataSourceTable(tablenum(SFA_MembershipTrans));
    queryRun = new QueryRun(query);
 
    if(query.findQueryFilter(qbds,"PaymMode"))      
    {
        ret = [SrsReportPreRunState::Error, "Payment mode is not able to filter in this report"];
    }
   
    if(query.findQueryFilter(qbds,"AmountCur"))      
    {
        ret = [SrsReportPreRunState::Error, "Amount Currency is not able to filter in this report"];
    }
   
    if(query.findQueryFilter(qbds,"AmountMST"))      
    {
        ret = [SrsReportPreRunState::Error, "Amount is not able to filter in this report"];
    }
   
    return ret;
}

Best way inserting query to temporary table to improve performance

Use Query::insert_recordset in R2 for report best practice

insert_recordset and update_recordset will expedite the report performance, and to insert data from query to temp table, have to use Query::insert_recordset.

Understand that from forum it is only available in R3. However I have tried in R2 and surprised it is working.
My code show as below:


[SysEntryPointAttribute]
public void processReport()
{
    SFA_MembershipTransContract     contract;
    SFA_MembershipTrans             membershipTrans;
    RetailTransactionTable          retailTable;
    RetailTransactionSalesTrans     retailSalesTrans;
    RetailTransactionPaymentTrans   retailPaymentTrans,retailPaymentTrans2,retailPaymentTrans3,retailPaymentTrans4;
    Query                           query,q;
    QueryRun                        queryRun;
    RecordInsertList                insList;
    QueryBuildDataSource            qbds, qbds2;
    QueryBuildFieldList             fieldList;
    Map                             fieldMapping;
    SFA_RetailSalesTransTmp         salesTransTmp;
    SFA_RetailSalesTransCountTmp    STCountTmp;
    SFA_RetailPaymentTransTmp       paymTransTmp,paymTransTmp2,paymTransTmp3,paymTransTmp4;
    ;
 
    query = this.parmQuery();
 
    qbds  = query.dataSourceTable(tableNum(SFA_MembershipTrans));
    queryRun    = new QueryRun(query);


    qbds  = query.dataSourceTable(tableNum(SFA_MembershipTrans));

    q = new Query(this.parmQuery());
    fieldList = qbds.fields();
    fieldList.addField(fieldNum(SFA_MembershipTrans, RecId));
    fieldList.addField(fieldNum(SFA_MembershipTrans, Remarks));

    fieldList.dynamic(QueryFieldListDynamic::No);

    fieldMapping = new Map(Types::String, Types::Container);
    fieldMapping.insert(fieldStr(SFA_MembershipTransTmp, MemberTransRefRecId), [qbds.uniqueId(), fieldStr(SFA_MembershipTrans, RecId)]);
    fieldMapping.insert(fieldStr(SFA_MembershipTransTmp, Remarks), [qbds.uniqueId(), fieldStr(SFA_MembershipTrans, Remarks)]);

    query::insert_recordset(tmpTbl,fieldMapping,q);

    ttsBegin;
    update_recordset tmpTbl setting BatchID = membershipTrans.BatchID,BatchName = membershipTrans.BatchName,CardId = membershipTrans.CardId, CardType = membershipTrans.CardType,
    ClubHouse = membershipTrans.ClubHouse, ICNum = membershipTrans.ICNum, InvoiceId = membershipTrans.InvoiceId, MemberStatus = membershipTrans.MemberStatus,
    MemberTypeCode = membershipTrans.MemberTypeCode, ModuleType = membershipTrans.ModuleType, NumberOfMonths = membershipTrans.NumberOfMonths, NumberOfYears = membershipTrans.NumberOfYears,
    PeriodEndDate = membershipTrans.PeriodEndDate, PeriodStartDate = membershipTrans.PeriodStartDate, PlanDate = membershipTrans.PlanDate, ProcessDate = membershipTrans.ProcessDate,
    Processed = membershipTrans.Processed, PromotionId = membershipTrans.PromotionId, ReasonCode = membershipTrans.ReasonCode, ReasonId = membershipTrans.ReasonId,
    ReceiptId = membershipTrans.ReceiptId, RenewalType = membershipTrans.RenewalType, RetailLoyaltyCustId = membershipTrans.RetailLoyaltyCustId, SalesId = membershipTrans.SalesId,
    SFA_Card = membershipTrans.SFA_Card, SourceType = membershipTrans.SourceType, TransDate = membershipTrans.TransDate, Valid = membershipTrans.Valid, PaymentVoucher = membershipTrans.PaymentVoucher
    join membershipTrans where membershipTrans.RecId == tmpTbl.MemberTransRefRecId;

    insert_recordset salesTransTmp (receiptId,netAmountInclTax)
    select receiptId, sum(netAmountInclTax) from retailSalesTrans
    group by retailSalesTrans.ReceiptId
    join tmpTbl where retailSalesTrans.ReceiptId == tmpTbl.ReceiptId;  

    insert_recordset STCountTmp (receiptId,NoCount)
    select receiptId, count(RecId) from retailSalesTrans
    group by retailSalesTrans.ReceiptId
    join tmpTbl where retailSalesTrans.ReceiptId == tmpTbl.ReceiptId && retailSalesTrans.ItemId like "MER-0*";

    update_recordset salesTransTmp setting NoCount = STCountTmp.NoCount
    join STCountTmp where STCountTmp.ReceiptId == salesTransTmp.ReceiptId;

    update_recordset tmpTbl setting AmountMST = salesTransTmp.netAmountInclTax
    join salesTransTmp where salesTransTmp.ReceiptId == tmpTbl.ReceiptId;

    update_recordset tmpTbl setting  PaymReference = "Multiple"
    join salesTransTmp where salesTransTmp.ReceiptId == tmpTbl.ReceiptId && salesTransTmp.NoCount > 1;
 

    insert_recordset paymTransTmp (receiptId,amountMST)
    select receiptId, sum(amountMST) from retailPaymentTrans
    group by retailPaymentTrans.receiptId
    join tmpTbl where retailPaymentTrans.ReceiptId == tmpTbl.ReceiptId;
 
    update_recordSet paymTransTmp setting SFA_PaymMode1 = retailPaymentTrans.SFA_PaymMode, SFA_PaymMode = retailPaymentTrans.SFA_PaymMode
    join firstonly retailPaymentTrans where paymTransTmp.receiptId == retailPaymentTrans.ReceiptId;
     
    update_recordSet paymTransTmp setting SFA_PaymMode2 = retailPaymentTrans2.SFA_PaymMode, SFA_PaymMode = paymTransTmp.SFA_PaymMode +","+ retailPaymentTrans2.SFA_PaymMode
    join firstonly retailPaymentTrans2 where retailPaymentTrans2.ReceiptId == paymTransTmp.receiptId && retailPaymentTrans2.SFA_PaymMode != paymTransTmp.SFA_PaymMode1;
 
    update_recordSet paymTransTmp setting SFA_PaymMode3 = retailPaymentTrans3.SFA_PaymMode, SFA_PaymMode = paymTransTmp.SFA_PaymMode +","+ retailPaymentTrans3.SFA_PaymMode
    join firstonly retailPaymentTrans3 where retailPaymentTrans3.ReceiptId == paymTransTmp.receiptId && retailPaymentTrans3.SFA_PaymMode != paymTransTmp.SFA_PaymMode1 && retailPaymentTrans3.SFA_PaymMode != paymTransTmp.SFA_PaymMode2;
 
    update_recordSet paymTransTmp setting SFA_PaymMode4 = retailPaymentTrans4.SFA_PaymMode, SFA_PaymMode = paymTransTmp.SFA_PaymMode +","+ retailPaymentTrans4.SFA_PaymMode
    join firstonly retailPaymentTrans4 where retailPaymentTrans4.ReceiptId == paymTransTmp.receiptId && retailPaymentTrans4.SFA_PaymMode != paymTransTmp.SFA_PaymMode1 && retailPaymentTrans4.SFA_PaymMode != paymTransTmp.SFA_PaymMode2 && retailPaymentTrans4.SFA_PaymMode != paymTransTmp.SFA_PaymMode3;
 
    update_recordset tmpTbl setting  PaymMode = paymTransTmp.SFA_PaymMode, PaymAmountMST = paymTransTmp.amountMST
    join paymTransTmp where paymTransTmp.ReceiptId == tmpTbl.ReceiptId;
 
    ttsCommit;
}


*Add correct index to temporary table will also improve the performance


My AX version between CU6 - CU7