Why is a problem that a customer who had $1000 outstanding in Jan has $1500 outstanding in Feb and increasing more?

If the customer is paying the Jan invoice in full but has purchased more in Feb, that sounds OK, right?

Or is it that the customer is slow-walking payments - for example paying $500 of January's bill in Feb.  the customer continues to pay but cleverly, using partial and delayed payments, slowly increasing the total outstanding?

Would this report contain Open Invoices, Paid Invoices or Both?

Perhaps print the Aged Invoice Report to paperless office every month.

I was going to say, but got lazy, that 30day shows an increase of, 60day shows y, 90 shows z, etc. So I could show a trendline. Slow payments is another issue as well, but a bit harder to show. 

If it's paid it would lower the balance. I just want to be able to show the sales person that they have outstanding payments and that the problem is increasing (or decreasing) 

I'm actually looking to show the information in an external application I'm building, so when they start to write an invoice they'll get a quick graph summary of some key customer data - including a trend line of account balance over time. 

The raw data is not in a form that is easy to interpret "as of" a certain date... and if you have DSD multi-currency, don't even try.  Be aware the "open" data is not permanent (subject to the "days to retain" setting, instead of the years of history setting).

There is one row in AR_OpenInvoice for each first transaction (top grid on the Invoices tab in Customer Maintenance), with transactions "applied to" that first transaction (payment, credit, adjustment...) having subsequent details linked under that transaction with the data stored in AR_TransactionPaymentHistory (lower grid on the Invoices tab).  The dates for the various transaction types can be found in different places... making custom reports very difficult to write.  Starting with the raw history tables would be much more complicated.

With multi-currency it's impossible to deal with mixed currency transactions, due to the ways the program determines when an invoice is paid in full.  Don't even try.

A better idea would be to schedule the stock Aging report (which refreshes the Aging buckets in AR_Customer), then capture those values daily in a UDT.  Base your report off the UDT.

So you want something like an AR Aged Invoice report but instead of aging the Open Invoices by the aging buckets (30,60,90,120+) you want to show it by Current Year/Period.

Prior  Jan  Feb  Mar  Apr May Jun Jul Aug Sep Nov Dec Total

Kevin M said:

A better idea would be to schedule the stock Aging report (which refreshes the Aging buckets in AR_Customer), then capture those values daily in a UDT.  Base your report off the UDT.

That's an interesting idea. Probably the easiest, but I don't mind building my own summary. I don't presently have any scheduled tasks, but I'm generally pretty good at building my own data aggregations and firing them from store procedures or functions etc. I did the same thing for transaction history, so I could look back and see how much material we had on hand on any given date. We're all single currency. But, in this case, yeah, I should probably schedule the report to run at, lets say 10PM, and then have a SQL scheduled task run at 2AM or whatever to load everything the aging category buckets from AR_Customer in to my own tables.... Trouble is, it'll take 3 months to get the starting point for the data I want to show! 

If I go to at least the aggregated data, it sounds like I can use the first entry in OpenInvoice as my starting point, then calculate invoice totals by bucket, and PaymentHistory to calculate the payment history by bucket, and show the change over time? 

Basically yeah. I want to be get the data together so I can show a chart/make obvious that a customer may be an AR problem with an increasing (positive trendline) of cash in the 60day/90day buckets. So when you open the application (external to sage) you'll be able to see that, oh no, every month the total amount owed at 60 and 90 is increasing. The starting point would be able to see the 60/90 in data format, obviously.