Report Designer (AP632500): Duplicate totals due to APAdjust join when summing USD balances

Question

Hello everyone in the community,I am working in the Acumatica Report Designer customizing the Accounts Payable report AP632500.rpx (AP Vendor Summary / Details). I need some help to solve a total accumulation issue with USD amounts in the group footers.Technical Context & What We've Done So Far:	Currency filtering (CuryID): We modified the detail cells for the Monto USD and Saldo USD columns with a conditional so they only display data when the document is in USD and remain empty (Null ) for peso (MXN) vendors, avoiding visual duplication.			Sign inversion: We applied logic to subtract documents of type ADR, CRM, and PPM.			Current formulas in detail cells:			Monto USD: =IIf([APRegister.CuryID] = 'USD', IIf([APRegister.DocType]='ADR' Or [APRegister.DocType]='CRM' Or [APRegister.DocType]='PPM', -1 * [APRegister.CuryOrigDocAmt], [APRegister.CuryOrigDocAmt]), Null)						Saldo USD: =IIf([APRegister.CuryID] = 'USD', IIf([APRegister.DocType]='ADR' Or [APRegister.DocType]='CRM' Or [APRegister.DocType]='PPM', -1 * [APRegister.CuryDocBal], [APRegister.CuryDocBal]), Null)			The Problem:At the detail level, the individual rows sum up to the exact real amount (for example, summing the independent balances of a USD vendor gives a real total of 45,483.52 USD). However, when trying to display the accumulated total in the vendor footer (groupFooterSection8), the sum is incorrect and gets inflated.This happens because the report performs a join with the APAdjust table (payment applications), which duplicates the lines and causes any direct sum function (=Sum(...)) to overstate the value in the footer.What I Need Help With:How can I structure the expression, variable, or function in the vendor footer within the Acumatica Report Designer so that the sum respects the USD filter and takes only the net balance of the master document, without being altered or duplicated by the APAdjust table rows?Thank you very much in advance for your help!

Steve Milner · Answer

@Ariyair Don't sum the detail cells. The stock report has the exact same duplication problem for the base-currency balance and solves it with variables instead of Sum. Copy that pattern for USD.

Open the header of the groupDocument group (groupHeaderSection3 in the stock layout). Its Variables collection already holds OrigDocAmt, SumAdjdAmt, SumAdjgAmt, DocBal, and then VendorBal with ResetGroup set to groupVendor and ValueExpr =$VendorBal+$DocBal. That header runs once per document, so each document lands in the vendor running total exactly once no matter how many APAdjust rows it joins to. The vendor footer just prints =$VendorBal. (In the stock layout that's groupFooterSection1. groupFooterSection8 is the supplied-by vendor footer, which prints $SuppliedByVendorBal.)

Add two variables to that same collection, listed after DocBal. Order matters, a variable can only use the ones above it.

DocBalUSD=IIf([APRegister.CuryID]='USD', IIf([APRegister.DocType]='ADR' Or [APRegister.DocType]='CRM' Or [APRegister.DocType]='PPM', -1*[APRegister.CuryDocBal], [APRegister.CuryDocBal]), 0)VendorBalUSD   (ResetGroup = groupVendor)=$VendorBalUSD + $DocBalUSD

Use 0 rather than Null inside the variable, otherwise the addition goes null. In the vendor footer put =$VendorBalUSD, or =IIf($VendorBalUSD=0, Null, $VendorBalUSD) if you want MXN vendors blank. Repeat the pair with CuryOrigDocAmt for the Monto USD total.

One caution. CuryDocBal is the document's balance today, not as of the report period. The stock DocBal rebuilds the as-of-period balance from the APAdjust rows. If you need USD as of the period, mirror SumAdjdAmt and SumAdjgAmt using CuryAdjdAmt and CuryAdjgAmt in place of AdjAmt.

Technical Context & What We've Done So Far:

The Problem:

What I Need Help With:

Sign up

Social Login

Login to the community

Social Login