Creating an Aging Report


A function called GetAgingDate() is available to help you create your own Aging reports. When used, this function will prompt for the Aging date, and then return the entered date until the report finishes. You can use this function to create Aging columns in the Advanced Report Designer (such as "Over 30", "Over 60"). Here's how you do it.

  1. Create a Quick Report.

  2. In Step 2 of the Quick Report Wizard, select the fields you want to appear in the report.

  3. In Step 5, turn on the Advanced layout setting and click the Edit button to bring up the Advanced Report Designer.

  4. Drag a new field on to the report in the detail band, beside the last field we added in step 2 (Balance in this case).

  5. Set the output expression of the field to something like the following (remember to use the expression builder to enter the expression for a field from the database):

    iif(Last_Invoice_Date < (GetAgingDate() - 30) AND Last_Invoice_Date >= (GetAgingDate() - 60), Draw_Balance, 0)

    This expression will evaluate to the Draw Balance if the Aging Date entered for the report is between 30 and 60 days greater than the Invoice Date (the "Over 30" column). You can use similar expressions for "Current", "Over 60", "Over 90", etc... columns.

  6. Do the same as above in the Group Footer band, but instead, set the "Calculation Type" on the "Calculation" Tab to Sum, and the "Reset Based on" to the field you decided to group on (in this case, the Job Number).

  7. Do the same as above in the Summary band, but instead, set the "Reset Based on" to "Report", so that this field gives us a total for the entire report.

  8. Continue adding columns (i.e. Over 60, Over 90) until you have the desired number of Aging columns on the report.