This pattern applies to a wide range of reports. It is the most applied pattern so far as based on our experience. The idea is to isolate line-based code to the minimum and leverage set-based operations to the maximum at the same time.  Here is a list of approaches.

Working with Dynamic Parameters

As it is mentioned in an earlier blog (part 1), Ax2012 set-based operations are X++ language elements compiled into SQL at compile time. They do not take, as input, the dynamic query ranges from users at run time. To leverage set-based operations, what you can do is to keep the original line-base loop but move every possible set-based process outside this loop. We improved the trial balance report In Ax2012 R2 this way.

In the trial blance report, it first detects of any dynamic parameter is added by calling a new method queryHasRangesOrFilters(). If it is, the main queryRun.next() loop is executed but only to collect those filtered line ides from GL table. Then, the rest of the process are changed to set-based.

 

The line-by-line loop on queryRun.next() can’t be replaced whenever dynamic parameter is enabled. But you should make this loop lean, only to process those that must be line-based and leave the rest to set-based processing. Vend report in Ax 2012 R2 is another example.

Note: Ax2012 R3 enables set-based insert with dynamic parameters. See post Part 6 on this series for detail.

public void processReport()

{

    QueryRun    queryRun;

    queryRun = new QueryRun(this.parmQuery());

    ttsbegin;

    while(queryRun.next())

    {

        vendorListBasicTmp.clear();

        vendTable = queryRun.get( tableNum(VendTable));

        this.insertIntoTempTable();

    }

    ttscommit;

}

 

private void insertIntoTempTable()

{

    vendorListBasicTmp.VendAccount  = vendTable.AccountNum;

    vendorListBasicTmp.Name         = vendTable.name();

    vendorListBasicTmp.Phone        = vendTable.phone();

    vendorListBasicTmp.Email        = vendTable.email();

    vendorListBasicTmp.Telefax      = vendTable.telefax();

    vendorListBasicTmp.Telex        = vendTable.telex();

    vendorListBasicTmp.InvoiceAccountName   = vendTable.invoiceAccountName();

    vendorListBasicTmp.ItemBuyerGroupId     = vendTable.ItemBuyerGroupId;

    vendorListBasicTmp.Currency             = vendTable.currencyName();

    vendorListBasicTmp.CashDisc             = vendTable.cashDiscName();

    vendorListBasicTmp.PriceGroupName       = vendTable.priceDiscGroupName();

    vendorListBasicTmp.LineDiscName         = vendTable.lineDiscName();

    vendorListBasicTmp.MultiLineDiscName    = vendTable.multiLineDiscName();

    vendorListBasicTmp.EndDiscName          = vendTable.endDiscName();

    vendorListBasicTmp.TaxGroupName         = vendTable.taxGroupName();

    vendorListBasicTmp.DeliveryMode         = vendTable.dlvModeName();

    vendorListBasicTmp.AccountName          = vendTable.customerAccountName();

    logisticsPostalAddress       = DirParty::primaryPostalAddress(vendTable.Party);

    vendorListBasicTmp.Address   = logisticsPostalAddress.Address;

    vendorListBasicTmp.CountyName= LogisticsAddressCounty::find(logisticsPostalAddress.CountryRegionId,logisticsPostalAddress.State,logisticsPostalAddress.County).Name;

    vendorListBasicTmp.StateName = LogisticsAddressState::name(logisticsPostalAddress.CountryRegionId,logisticsPostalAddress.State);

    vendorListBasicTmp.CountryName = LogisticsAddressCountryRegion::name(logisticsPostalAddress.CountryRegionId);

   vendorListBasicTmp.PaymentTerm = PaymTerm::find(vendTable.PaymTermId).Description;

    vendorListBasicTmp.insert();

}

 

 

All the method calls in insertIntoTempTable() method are database find() type of methods, which can be expressed as join clauses in a set-based statement. To use set-based data access when dynamic parameter is enabled, you first store all the VentdTable line recIDs, and available field valuess, in the temp table without calling any method. The second step is to do set-based update over the first results to join with other tables to get the rest of the field values.

Please note that replacing a line-base table display method duplicates the business logic in the method code in the form of set-based expressions. Such denormalization is a tradeoff for performance. Developers need to make the call based on the each scenario.

Table Methods of Database Operation

As mentioned above in Vend report, a table method of database operation, such as look up or aggregate, can be replaced with a join clause in the set-based operation. One typical example is find() method that most tables have.  Basically, the loop up logic in the table function needs to be repeated by an exist-join clause in the set-based statement, such as this code snip from VendAccruedPurchasesDP_NA.

physicalDate = AccountingEvent::findByAccountingEventType(SourceDocumentLine::find(vendPackingSlipTrans.SourceDocumentLine).SourceDocumentHeader, AccountingEventType::Original).AccountingDate;

financialDate = AccountingEvent::findByAccountingEventType(SourceDocumentLine::find( vendPackingSlipTrans.SourceDocumentLine).SourceDocumentHeader, AccountingEventType::Finalize).AccountingDate;

if ( (physicalDate && physicalDate <= cutOffDate) &&  (financialDate > cutOffDate || !financialDate))

{

                   bSend  = true;

}

else

{

                   if ( (physicalDate > cutOffDate   || !physicalDate) &&  (financialDate <= cutOffDate && financialDate))

                   {

                       bSend = true;

                   }

}

The look up and the subsequent comparison need to be converted to table join clauses of the insert_recordset statement, such as this:

  #LOCALMACRO.OrigAndFinalizeExist

                    exists join origEvent where

                        origEvent.SourceDocumentHeader == vendPackingSlipTransSDL.SourceDocumentHeader &&

                        origEvent.Type == AccountingEventType::Original

                    exists join finalEvent where

                        finalEvent.SourceDocumentHeader == vendPackingSlipTransSDL.SourceDocumentHeader &&

                        finalEvent.Type == AccountingEventType::Finalize &&

                      ((origEvent.AccountingDate <= cutOffDate &&

                        finalEvent.AccountingDate > cutOffDate) ||

                       (origEvent.AccountingDate > cutOffDate &&

                        finalEvent.AccountingDate <= cutOffDate))

 Set-based approach in the Nested Loops

Many report RDP classes have nested loops. The outer loop iterates over the result of queryRun object line-by-line. And then for each line there is a nested inner loop, which is mostly un-related to the dynamic parameter as the outer loop does. In most cases, such a nested loop can be in set-based to reduce the X++ code complicity from O(n*n) to O(n).

Report examples of this type are CustBillofExchange, TaxPurchLedger, CustLedgerTrans, etc.

Sometimes, the nested loop is wrapped in a private method call.  Here is an example from LedgerTransStatementDP class:

  

private LedgerJournalAC getOffsetAccount(TmpDimTransExtract _tmpDimTransExtract)

{

   ...

    while select MainAccountId from mainAccount

        group by mainAccount.MainAccountId

    where mainAccount.MainAccountId != _tmpDimTransExtract.AccountMain

        && mainAccount.LedgerChartOfAccounts == LedgerChartOfAccounts::current()

    exists join dimensionAttributeValueCombination

        where dimensionAttributeValueCombination.MainAccount == mainAccount.RecId

    exists join generalJournalAccountEntry

        where dimensionAttributeValueCombination.RecId == generalJournalAccountEntry.LedgerDimension

    exists join generalJournalEntry

        where generalJournalEntry.RecId == generalJournalAccountEntry.GeneralJournalEntry

            && generalJournalEntry.Ledger == Ledger::current()

    exists join subledgerVoucherGeneralJournalEntry

        where subledgerVoucherGeneralJournalEntry.GeneralJournalEntry == generalJournalEntry.RecId

            && subledgerVoucherGeneralJournalEntry.Voucher == _tmpDimTransExtract.Voucher

            && subledgerVoucherGeneralJournalEntry.AccountingDate == _tmpDimTransExtract.TransDate

    {

        // An account was found that is not the account on the current calculation group.

        // Add the offset account to the set. Because it's a set each unique account is only added once.

        offsetAccountSet.add(mainAccount.MainAccountId);

 

        if (offsetAccountSet.elements() > 3)

        {

            // Use 'Multiple' when there are more than 3 offset accounts.

            offsetAccount = "@SYS78137";

            break;

        }

    }

 

    if (offsetAccount == '')

    {

        // Generate the string to display with the offset accounts that were found.

        setEnumerator = offsetAccountSet.getEnumerator();

        while (setEnumerator.moveNext())

        {

            if (strLen(offsetAccount) != 0)

            {

                offsetAccount += ', ';

            }

           offsetAccount += setEnumerator.current();

        }

    }

    return offsetAccount;

}

This method is called per line. The equivalent set-based code as implemented in Ax2012 R2 appears quite different:

private void processOffsetAccount(LedgerTransStatementTmp _ledgerTransStatementTmp)

{

LedgerTransStatementOffsetAccountTmp    offsetAccountTmp;

...

 #LOCALMACRO.JoinGJAE   

    exists join dimensionAttributeValueCombination

            where dimensionAttributeValueCombination.MainAccount == mainAccount.RecId

    exists join generalJournalAccountEntry

            where generalJournalAccountEntry.LedgerDimension == dimensionAttributeValueCombination.RecId

                && generalJournalAccountEntry.GeneralJournalEntry == offsetAccountTmp.GeneralJournalEntry

                && generalJournalAccountEntry.RecId != offsetAccountTmp.GeneralJournalAccountEntry;

    ENDMACRO

    this.setUserConnection(offsetAccountTmp);

    // Start by pushing all data to process into a seperate temp table where

    // the individual account values can be dropped into individual columns

    insert_recordset offsetAccountTmp (GeneralJournalEntry, GeneralJournalAccountEntry)

        select GeneralJournalEntry, GeneralJournalAccountEntry from _ledgerTransStatementTmp

            where _ledgerTransStatementTmp.createdTransactionId == this.parmCreatedTransactionId();

    // Fill in the first offset account (where possible)

    update_recordSet offsetAccountTmp

        setting OffsetAcct1 = mainAccount.MainAccountId

        join mainAccount

        #JoinGJAE

    // Fill in the second offset account (where possible)

    update_recordSet offsetAccountTmp

        setting OffsetAcct2 = mainAccount.MainAccountId

        join mainAccount

            where mainAccount.MainAccountId != offsetAccountTmp.OffsetAcct1

        #JoinGJAE

    // Fill in the third offset account (where possible)

    update_recordSet offsetAccountTmp

        setting OffsetAcct2 = mainAccount.MainAccountId

        join mainAccount

            where mainAccount.MainAccountId != offsetAccountTmp.OffsetAcct1

                && mainAccount.MainAccountId != offsetAccountTmp.OffsetAcct2

        #JoinGJAE

 

    // Remove all cases where more than three accounts exist

    update_recordSet offsetAccountTmp setting

        IsMoreThanThree = NoYes::Yes,

        OffsetDescription = "@SYS78137" // Multiple

        exists join mainAccount

            where mainAccount.MainAccountId != offsetAccountTmp.OffsetAcct1

                && mainAccount.MainAccountId != offsetAccountTmp.OffsetAcct2

                && mainAccount.MainAccountId != offsetAccountTmp.OffsetAcct3

        #JoinGJAE

    // Concatenate the values into a total

    update_recordset offsetAccountTmp setting

        OffsetDescription = offsetAccountTmp.OffsetAcct1

        where offsetAccountTmp.OffsetAcct1

            && offsetAccountTmp.IsMoreThanThree == NoYes::No;

    update_recordset offsetAccountTmp setting

        OffsetDescription = offsetAccountTmp.OffsetDescription + ',' + offsetAccountTmp.OffsetAcct2

        where offsetAccountTmp.OffsetAcct2

            && offsetAccountTmp.IsMoreThanThree == NoYes::No;

    update_recordset offsetAccountTmp setting

        OffsetDescription = offsetAccountTmp.OffsetDescription + ',' + offsetAccountTmp.OffsetAcct3

        where offsetAccountTmp.OffsetAcct3

            && offsetAccountTmp.IsMoreThanThree == NoYes::No;

    // Copy the descriptions back to the source table

    update_recordSet _ledgerTransStatementTmp setting

        OffsetAccount = offsetAccountTmp.OffsetDescription

            where _ledgerTransStatementTmp.createdTransactionId == this.parmCreatedTransactionId()

        join offsetAccountTmp

            where offsetAccountTmp.GeneralJournalAccountEntry == _ledgerTransStatementTmp.GeneralJournalAccountEntry

                && offsetAccountTmp.GeneralJournalEntry == _ledgerTransStatementTmp.GeneralJournalEntry;

}

 

Notice the use of a temp table, LedgerTransStatementOffsetAccountTmp, which stores the intermediate result. Such a table is often needed for set-based approach.

The set-based approach is not straightforward, but it is much faster than the line-based method call.

Aggregation and Sequencing

In a line-based processing loop, it is easy to aggregate a total or a rolling total in a variable. It is done differently in the set-based approach.

To get the summary in the temp table, it is often done in two steps: inserting all the detail lines first and followed by inserting summary lines in the same table. You can add a flag field to the table, such as isFinalRecord, to separate the aggregated values with detail values. In the end, you delete all the detail lines before rendering the report. In LedgerTransStatement report (R2 code), the set based operations are in stages: retrieve and process the raw detail records, and then grouping. In the end in recorderRows() method, only aggregated lines are kept:

    // Remove the original records that created the balances

    delete_from newTmp

        where newTmp.IsFinalRecord == NoYes::No

            && newTmp.createdTransactionId == this.parmCreatedTransactionId(); 

 

A rolling total, or a current amount, depends on the order of the result set. We can define an incremental id field to tag the temp table based on the order-by. Then do set-based with aggregation on those that has the id value smaller than the current row.

See Ax6.2 code example in LedgerTrialBalanceDP.updateAccumulatedAmounts():

private void updateAccumulatedAmounts(LedgerTrialBalanceStagingTmp _ledgerTrialBalanceStagingTmp)

{

    LedgerTrialBalanceTmpAccumulated    tmpAccumulated;

    LedgerTrialBalanceStagingTmp        ledgerTrialBalanceStagingTmpReference;

 

    this.setUserConnection(ledgerTrialBalanceStagingTmpReference);

    ledgerTrialBalanceStagingTmpReference.linkPhysicalTableInstance(_ledgerTrialBalanceStagingTmp);

 

    this.setUserConnection(tmpAccumulated);

 

    insert_recordset tmpAccumulated (LedgerTrialBalanceTmp, AccumulatedDebit, AccumulatedCredit)

        select RecId from _ledgerTrialBalanceStagingTmp

            group by _ledgerTrialBalanceStagingTmp.RecId

        join sum(AmountDebitTrans), sum(AmountCreditTrans) from ledgerTrialBalanceStagingTmpReference

            where ledgerTrialBalanceStagingTmpReference.PrimaryFocus ==  _ledgerTrialBalanceStagingTmp.PrimaryFocus

                && ledgerTrialBalanceStagingTmpReference.RecId <= _ledgerTrialBalanceStagingTmp.RecId;

 

    update_recordSet _ledgerTrialBalanceStagingTmp setting

        Accumulated = tmpAccumulated.AccumulatedDebit - tmpAccumulated.AccumulatedCredit

        join tmpAccumulated

            where tmpAccumulated.LedgerTrialBalanceTmp == _ledgerTrialBalanceStagingTmp.RecId;

 

    // Clear out the AmountDebitTrans and AmountCreditTrans from opening transactions

    update_recordSet _ledgerTrialBalanceStagingTmp setting

        OpeningBalance = _ledgerTrialBalanceStagingTmp.AmountDebitTrans - _ledgerTrialBalanceStagingTmp.AmountCreditTrans,

        AmountDebitTrans = 0,

        AmountCreditTrans = 0

    where _ledgerTrialBalanceStagingTmp.TransactionType == FiscalPeriodType::Opening;

}

 

Working with Complicated Code Logic

We already know that some line-based method calls contains only database look up logic. They can be combined into join clauses in the set-based operations. However, whenever there is line-based code logic, it is often hard or impossible to rewrite it into set-based without affecting fundamental logic of the report and data schema. Examples are rounding and exchange rate code. They will remain as line-based.

For example, display method InventTable.itemName() has code logic that can’t be in set-based.  Reports that use it must keep the call in the line-based loop. The general task for developers is to identify what code can be in set-based and what must stay as line-based.