How to Create an Accounts Receivable Aging Report

Question

How can I generate an aging report that shows a breakdown of accounts that are 0-30, 31-60, and over 60 days past due?

Solution

1. Use a for command to open the Primary table and specify any required selection criteria using a with clause.

2. Use a list records command to list the fields you want to print.

3. Under the list records command, list the field you want to group (e.g., CUSTOMER NO., MEMBER ID, STUDENT ID) in groups.

4. Use an if function to check each record to see which age group it falls into (e.g., 0-30, 31-60, 60+, or whatever age groups you want to establish).

5. Within each if function use the sum statistical operator to generate the total for each group.

Example 1

for INVOICES

with PAID = NO ;

list records

MEMBER ID in groups ;

LAST NAME ;

if ( current date - INVOICE DATE < 30, AMT_DUE,

blank) : sum ;

if ( current date - INVOICE DATE between 31 to

60, AMT_DUE , blank) : sum ;

if ( current date - INVOICE DATE > 60, AMT_DUE ,

blank) : sum .

end

This example tells DataEase to: (1) list all open invoice records grouped by MEMBER ID, (2) check the difference between today's date and the INVOICE DATE to determine the number of days the invoice is past due, and (3) if the records for each member have amounts less than 30 days past due, calculate the sum of those amounts. If the records for each member contain totals between 30 and 61 days past due, calculate the sum of those totals. Finally, if the records for each member contain totals more than 60 days past due, calculate the sum of those totals.

Sample Output 1

Example 1 can be formatted to look like this:

Example 2

 define temp "MEMID" numeric string 5 .

 define temp "XTHIRTY" number .

 define temp "XSIXTY" number .

 define temp "XOVER" number .

 define temp "ACC_THIRTY" number .

 define temp "ACC_SIXTY" number .

 define temp "ACC_OVER" number .

 for INVOICES with PAID = no ;

  if MEMID not = MEMBER ID then

  assign temp MEMID := MEMBER ID .

  assign temp XTHIRTY := sum of INVOICES named

   "under 30" with ( MEMBER ID = INVOICES

  MEMBER ID

  and PAID = NO and current date - INV_DATE < 30)

  AMT_DUE .

  assign temp XSIXTY := sum of INVOICES named

   "thirty-60" with ( MEMBER ID = INVOICES

  MEMBER ID

  and PAID = NO and current date - INV_DATE

  between 31 to 60 ) AMT_DUE .

  assign temp XOVER := sum of INVOICES named

   "over60" with ( MEMBER ID = INVOICESMEMBER ID

  and PAID = NO and current date - INV_DATE > 60 )

  AMT_DUE .

  assign temp ACC_THIRTY := temp ACC_THIRTY + temp

   XTHIRTY .

  assign temp ACC_SIXTY := temp ACC_SIXTY + temp XSIXTY .

  assign temp AC_OVER := temp ACC_OVER + temp XOVER .

  end

  list records

  MEMBER ID in groups with group-totals ;

  LAST NAME ;

  temp XTHIRTY ;

  temp XSIXTY ;

  temp XOVER ;

  temp ACC_THIRTY ;

  temp ACC_SIXTY ;

  temp ACC_OVER .

Example 2 uses temporary variables to accumulate the individual totals for each member (broken down by day range) and report totals for each day range. This example tells DataEase to: (1) create seven separate temporary variables. The first will be used to track each instance of the group changing (in this example, it holds the current MEMBER ID). The next three variables are used to accumulate the day range values: one accumulates totals less than 30 days past due, the next, 31 to 60 days past due and the last, over 60 days past due. The last three variables accumulate report totals for each of the three day ranges. The next part of the script tells DataEase to: (2) use a for command to open the INVOICES table, and (3) under the for loop, assign each temporary variable whenever the current record represents a new group. This is accomplished by testing the MEMID variable with an if command to see if it contains the same value as the MEMBER ID in the current record. In the rest of the script, (4) whenever the MEMID variable is reassigned, the next three variables (XTHIRTY, XSIXTY and XOVER) are also reassigned. At the same time, the values in the last three variables are incremented to accumulate report totals for each category, and (5) list the MEMBER ID (in groups) and the values in each of the variables.

Sample Output 2

Example 2 can be formatted to produce the identical output as Example 1 shown on the previous page.

Tip

Example 2, though much more complex than Example 1, provides the added benefit of assigning the totals DataEase generates to temporary variables. This lets you post the totals to a separate summary file if desired.

When you choose a layout for the procedure, you should choose a tabular format (the default) and place the temporary variables in the layout.