Creating an Aging Report Using Stonefield Query

An aging report is one that shows totals broken down by age range. The most common example is an accounts receivable aging report, which shows how much each customer owes by period, typically within the past 30 days, 31 – 60 days, 61 – 90 days, and over 90 days. This type of report allows you to make business decisions, such as which customers to increase collection efforts with and which to cut off sales until they pay up (no point in continuing to sell products or do work for customers who won’t pay for them).

You’ve always been able to create this type of report in Stonefield Query, but until version 5.0, it was a bit of work. There were two ways to do it:

• Create a formula for each aging range that decides whether to include the amount or not using the IIF function that checks the date. For example, the expression IIF(BETWEEN(Invoices.DateDue, DATE(), DATE() – 30), Invoices.OutstandingAmount, 0) checks whether the due date for the invoice is between the current date and 30 days ago. If so, it takes the unpaid amount; if not, it takes 0. The report is grouped on customer and the formula fields are summed.
• Using the Advanced Report Designer, add fields to the report that do similar calculations to the formula approach.

A better way

A new feature added in version 5.0 called grouping formulas makes this a much simpler task. Here are the steps to creating an aging report using the sample data that comes with the Stonefield Query SDK; for other versions, such as Stonefield Query for Sage 300 ERP, the steps are very similar but you use the appropriate tables and fields instead of the ones mentioned here.

The first step is to create a quick report. Add the Customer Name field to the report, then click the New Formula button. Select the Customers table because that’s where we want the formula to go. Enter “0 – 30” for the Name and Heading since this first formula is for the first aging. Click the Expression Builder button (the one with the three dots) and select the Total Price field from the Order Details table because that’s the field we want to sum up.

Now here’s the key: we want this to be a grouping formula. A grouping formula is similar to a regular formula but it automatically summarizes the values, grouping on a certain field, and optionally for only a certain range of records. (Those of you who are technically oriented may guess that a grouping formula results in a SQL statement like SELECT SUM(SomeField) FROM SomeTable WHERE SomeConditions GROUP BY SomeGroupField.) In our case, we want to sum the total price, group the sums by customer, and only for records that are 30 or fewer days old. (Since the sample database doesn't have payments, we'll just assume that all orders are outstanding for demo purposes.)

To make this a grouping formula, click the Grouping button. Select Sum for Summary and Customer ID for Grouping Field. Click the Filter button, add a condition, select the Order Date field from the Orders table, and choose “is between” for the operator. The values to use are a little tricky: we don’t want to hard-code them to something like 05/01/2015 and 05/31/2015 because every time we run the report, we’d have to edit the formula and change the date range. We could turn on Ask at Runtime to prompt for the date range, but since we’ll ultimately have four formulas (one for each aging range), we don’t want to be asked for four different ranges of values; we really just want to put in a single value, which is the “as of” date for the aging calculations. To do that, click the More button, change Compare To to “Expression”, and enter the following expressions for the two values:

GetValueForParameter('As of date', 'D') – 30
(for the first value)

GetValueForParameter('As of date', 'D')
(for the second one)

GetValueForParameter is a built-in function that asks the user running the report for a value. The first parameter for the function is the text to display (“As of date” in this case) and the second is the data type for the value (“D” means we want a date; see the documentation for GetValueForParameter for what codes to use for different data types). The first expression tells Stonefield Query to ask the user for a date value and then subtract 30 days from that value. You may think from the second expression that Stonefield Query asks the user a second time, but GetValueForParameter has a nice feature: if the prompt is the same as a previous instance, the function doesn’t ask the user a second time but instead just returns the value they entered when they were asked. Since the two expressions both use “As of date” for the prompt, the user is asked only once. In fact, you’ll use a similar expression for the other formulas, such as 31 – 60, but subtract a different number of days, and since all of them use “As of date” for the prompt, the user is still only asked one time for the value that all expressions use.

Once you’ve saved the filter condition, the Grouping Formula Properties dialog should look like this:

Specify the formatting for the formula (I turned on Display $ and set Decimal Places to 2) and save the formula.

Finishing the report

Repeat these steps to create the 31 – 60, 61 – 90, and More Than 90 formulas. The only difference is the expressions used for the filter condition. For example:

GetValueForParameter('As of date', 'D') – 60
(first value for 31 – 60 formula)

GetValueForParameter('As of date', 'D') – 31
(second value for 31 – 60 formula)

(The expressions to use for the other two formulas are left as an exercise for the reader, but should be pretty obvious).

That’s it! Your report should now include Customer Name and the four formulas. No need to group the report on Customer Name or turn on Summary Report. When you run the report, you’re asked for the date to use for the aging calculations. The result should look like this:

Summary

Grouping formulas are a very powerful new feature added in version 5.0 of Stonefield Query. There are tons of uses for them, one of them making it very easy to create a simple aging report.