Create a Customer Saved Search to pull up all the list of Customers with receivables outstanding for more than xx days and with total unpaid amount percent of overdue balance greater than a certain percent.

 

To create a Customer Saved Search to pull up all the list of Customers with receivables outstanding for more than xx days and with total unpaid amount percent of Balance greater than a certain percentage, the users can perform the following steps.

Let's take the following requirements for example:

- All open invoices for more than 90 days.

- Total unpaid amount percent of Overdue Balance > 10%

1. Navigate to Reports > Saved Searches > All Saved Searches > New > Type = Customer.

2. Name the search.

3. In the Criteria tab > Standard subtab, add the following filters:

            Stage = Is Customer

            Inactive = is false

Transaction : Days Open = set this to any preferred filter (i.e. is greater than 90)

Transaction : Amount Remaining = is greater than 0.00

Overdue Balance = is greater than 0.00

Transaction: Type = Invoice

Balance = is greater than 0.00

4. In the Criteria tab > Summary subtab, add these details:

Summary Type = Sum

Field = Formula (Numeric)

Formula = CASE WHEN SUM(DECODE({balance}, '0', '0', {transaction.amountremaining}/{overduebalance})) > 0.1 THEN 1 ELSE 0 END.  In the Formula (Numeric) field, set the Value equal to 1. 

5. In the Results tab > Columns subtab, add the following fields:

Field | Summary Type | Function | Formula

Name | Group

Balance  | Maximum | Round to Hundredths

Transaction : Amount Remaining | Sum | Round to Hundredths

Formula (Percent) | Sum | Round to Hundredths | {transaction.amountremaining}/NULLIF({overduebalance},0)

Formula (Numeric) | Sum | Round to Hundredths | CASE WHEN SUM(DECODE({overduebalance}, '0', '0', {transaction.amountremaining}/{overduebalance})) > 0.1 THEN 1 ELSE 0 END

6. Preview/Save the search.