Report for getting a comparison on Customer deposits with applied and Unapplied amounts

we create a Transaction saved search (Lists > Search > Saved Searches > New > Transaction).

We create a Transaction saved search (Lists > Search > Saved Searches > New > Transaction). The search criteria are pretty basic: Add filters Type is any of Payment, Customer Deposit and Main Line is false as illustrated below. If necessary, add other criteria e.g. to limit the results to a particular subsidiary or GL accounts. In case of a GL account filter, be sure to use the “Account (Main)” field rather than the “Account” field as the bank account will be on the header (i.e. main) line.

 In the Results subtab, enter the columns illustrated below. I will focus on explaining the formula fields as the others should be self-explanatory.

Field: Formula (Text)

• Summary Type = Group
• Formula: '<a href="/app/accounting/transactions/transaction.nl?id='||{internalid}||'">'||{tranid}||'</a>'
• Summary Label = View
• This adds a convenient link to open the transaction without having to first drill down which would be the case if we added the Document Number field directly. It is an optional but handy trick to eliminate one extra click.

1. Field: Amount

• Summary Type = Maximum
• Function = Absolute Value
• Summary Label = Total Deposit / Payment Amount
• This captures the total deposit or payment amount. We take the absolute value as the value is negative on payment transactions. Note that we use the Maximum summary type to ensure that the column is visible at the summary search level. Minimum or Average will also work as the (header) amount is the same on all lines. This observation also applies to the following formula fields.

1. Field: Formula (Currency)

• Summary Type = Minimum
• Formula = abs(sum(NVL(DECODE({typecode}, 'CustDep', {applyingtransaction.amount}, {appliedtolinkamount}),0)))
• Custom Label = Summary Label = Applied Amount
• This formula sums the total applied amount. I will explain this formula in more detail in the next section.

1. Field: Formula (Currency)

• Summary Type = Minimum
• Formula = max(abs({amount}))-abs(sum(NVL(DECODE({typecode}, 'CustDep', {applyingtransaction.amount}, {appliedtolinkamount}),0)))
• Custom Label = Summary Label = Unapplied Amount
• This formula captures the total unapplied amount. We will explore how it works shortly.