Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics GP (Archived)

Due Date VS Document Date in AR Aging Report

(0) ShareShare
ReportReport
Posted on by 1,800

We are looking to have the AR Aging report age the invoiced based on due date (as opposed to invoice date), please. the allocation between “31-60 Days”, “61-90 days” and “91 and over” columns doesn’t seem to change when we change the report to be “by due date” .

We noticed that we may need to go to AR Setup window to change the aging period from Document Date to Due Date.  I know the AR report will show correctly by Due date after changing this setting in AR Module.

Here are my questions: 

1. We do need to run the AR report (by Document Date) and (By Due Date).  So we need to change the AR setting all the times?  Is there no way to run the AR aging report whichever way we need to without making this change every time?

2.  Let's say we change the aging period every time, do we need to run "reconcile" in AR module?  Or it does not need to?

Please help.  Ideally, we want to see all the dollar amounts are in the correct period in AR aging report by running "Document Date" and "Due Date".

Thanks,

Josey

*This post is locked for comments

  • Verified answer
    soma Profile Picture
    soma 24,406 on at
    RE: Due Date VS Document Date in AR Aging Report

    Josey,

    Don't consider the @EMPTYDATE. This date is used for validate the invoice & payment based on apply and unapply.

    Just create this as stored procedure with only one parameter @ASOFDATE.

    For example, create this procedure as shown below

    CREATE PROCEDURE CUST_AGING_REPORT

    @ASOFDATE AS DATETIME

    AS

    BEGIN

    DECLARE @EMPTYDATE AS DATETIME

    SET @EMPTYDATE = '1900-01-01'


    execute this procedure like  EXEC CUST_AGING_REPORT '2015-03-30' (If you want to run as of 2015-03-30).

    For SSRS report calling, use EXEC CUST_AGING_REPORT @ASOFDATE (while running through SSRS it will ask you to enter the asof date parameter value).


    Hope this helps!!!

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Due Date VS Document Date in AR Aging Report

    Hi Josey,

    @EMPTYDATE = '1900-01-01'

    @ASOF Date '2017-04-12"

    The both the fields are parameters. The @EMPTYDATE = '1900-01-01' you can pass this field in the parameter or set as default value in the procedure as it is (@EMPTYDATE = '1900-01-01' ). But for the @ASOF Date (Cut off Date) Any document that has a date on or before the Print/Age as of date is included on the report. For example, if you select Beginning of Month, the report includes trial balance information aged as of the first day of the current month.

  • joseytang Profile Picture
    joseytang 1,800 on at
    RE: Due Date VS Document Date in AR Aging Report

    Thanks, Mahmoud.

    I think we can use SSRS report. I believe it was the stored procedure. I noticed that the developer setup the following date.  My question (not sure you can answer me).  The developer (blogger) set the as of date "2017-04-12".  Apply to my case, what date I should set up for this 'as of date'.  What does this date for? If our user want to run the date as of today (3/30/2015), I can still run it if we Set @ASOF Date '2017-04-12"?

    SET @EMPTYDATE = '1900-01-01'

    SET @ASOFDATE = '2017-04-12'

    Please advise

    Thanks,

    JoseyTang

  • Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at
    RE: Due Date VS Document Date in AR Aging Report

    Yes Joeseytang

    That's actually the proposed solution

    For the smart list builder, it won't be such an effective report since there is a parameter that you should pass which is the (As of Date). In this essence, I would suggest the Crystal Report as a better solution

    Let me know if you have any further inquiries,

  • joseytang Profile Picture
    joseytang 1,800 on at
    RE: Due Date VS Document Date in AR Aging Report

    If we stick with our AR setup window as "Document Date", we can still use GP canned report to run the AR aging report by Document Date.

    Then, we can use the attached script as your reply, to run the AR aging report by "Due Date" (even our setup window is based on Document Date".

    Am I correct?

    Please advise

    Thanks,

    Josey

  • joseytang Profile Picture
    joseytang 1,800 on at
    RE: Due Date VS Document Date in AR Aging Report

    Hi Mahmoud,

    Thanks for your information. Very helpful.  By looking at the script, the report seems to be running the aging report by Due Date.

    Can we use this script in Smartlist Builder? To build this report in Smartlist?

    Please advise

    Thanks,

    Josephine

  • Verified answer
    Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at
    RE: Due Date VS Document Date in AR Aging Report

    Hello joseytang

    This is actually a common question which we have heard quite often, the answer is to simply create a customized Historical Aging Trial Balance report either by due date or document date, leaving the out of the box report of Dynamics GP without having to switch all the time from the setup level.

    I have always considered this script to create the HATB

    Receivables HATB With Aging By Due Date (using GL Posting Date)

    For your other question on whether you have to reconcile after changing the setup, if you create a customized report, you would save yourself a lot of effort of continuously changing the setup. But still, you don't have to reconcile after doing so

    Let us know if you had any further inquiries,

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Verified Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,391 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,445 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans