Share
ReportThanks
Francis
*This post is locked for comments
You’re offline. This is a read only version of the page.
1,921
Thanks
Francis
*This post is locked for comments
Hello Rob,
what do you mean when you say that "the built in aging buckets...those give you the customer's balance, not the balance of the document"
Ive noticed that the documents arent in the right buckets, but maybe GP uses a different criteria...
Also, if i I want it to consider the due date of the document I have to build the smartlist myself? Its not a standard?
Thanks!
Hi Francis,
I know this question was posted a long time ago and you have received many answers to your question, but I didn't see anyone answer how you can create a DETAILED aging in SmartList. It is possible, but you can NOT use the built in aging buckets because those give you the customer's balance, not the balance of the document.
You will need SmartList Builder if you are on GP2010 or 10.0. If you are you are on GP2013 SP1 or higher for you can use SmartList designer for free.
To accomplish this you will create calculated fields for each of your aging buckets...NOTE: By doing this, you are HARD CODING the calculations into the report - it will completely ignore your AR aging routine or any of your buckets that you have defined in GP, and goes entirely off the document date, although you could modify this to go off due date since due date is coded into the document details.
Each calculated field will look something like this:
CASE WHEN Datediff(day, [RM20101].[DOCDATE], Getdate()) <= 30 THEN [RM20101].[CURTRXAM] ELSE 0 END
You will add one of these for each aging, i.e. for 30-60 it will look like this:
CASE WHEN Datediff(day, [RM20101].[DOCDATE], Getdate()) <= BETWEEN 31 AND 60 THEN [RM20101].[CURTRXAM] ELSE 0 END
NOTE: You are creating one calculated field for each aging bucket.
Let me know if you have any questions about this.
There's also an SSRS report available. However it is not Excel ready. You have to modify it. Not quite awesome.
Leslie,
Yep, that's what I typically do for the historical aging report. For the current aging, though, a lot of times I have been asked for additional data (for example linking to the SOP tables for a user defined field), or a different way of presenting it...so I prefer to do that in SQL.
I have often taken a different route and just made the canned report 'Excel Friendly'. It works like a charm and the table definitions have already been done for me!
Kind regards,
Leslie
LOL Frank! Is that a hint? ;-) It's difficult to do this generically in a view, like I usually post, because aging can be done differently (due date vs. doc. date) and different customers have different names and days for their aging buckets. And some want aging by GL date, while others want it by doc date... So it's often a lot easier to tell someone how to do it, so they can do it with their own settings.
Francis,
Just looking at your reply to Ian - keep in mind that in some cases the only thing that will match the GP report is the overall total due. If the aging process has not been run in GP and/or if you are calculating aging columns differently, the individual aging bucket totals may not match. If the only goal is to match the GP report, there is actually a column in the RM20101 table that holds the position of the GP aging bucket that the transaction is in currently - AGNGBUKT. I typically prefer to do my own calculation on what aging bucket the transactions should be in, so that my report is not relying on someone running the Aging process in GP. Also, in some cases the RM module is setup to leave all credits in the Current aging bucket - this is not typically recommended, but some companies prefer it.
I should have known Victoria would have a solution to this - I spoke too soon Frances!
Why has no one ever posted a script or a view for this in any of the blogs or forums - I looked everywhere before answering Frances! LOL
That's amazing Victoria, thanks a lot!
Francis,
SmartList out-of-the-box will not give you a detailed current aging, only summary. The good news is that this is pretty easy to get from the tables. The data for all unpaid AR transactions is in RM20101 - filter on CURTRXAM <> 0. I also typically link to the RM00101 table on CUSTNMBR to get some additional customer details. The other critical piece is the type of transaction (RMDTYPAL) in RM20101. From http://victoriayudin.com/gp-tables/rm-tables/:
Add these:
1 – Sale / Invoice
2 – Reserved for scheduled payments
3 – Debit Memo
4 – Finance Charge
5 – Service Repair
6 – Warranty
And subtract these:
7 – Credit Memo
8 – Return
9 – Payment
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,235
Most Valuable Professional
#3
nmaenpaa
101,156
All responses (
Answers (