Due Date VS Document Date in AR Aging Report

(0) Share

Report

Posted on by joseytang

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

All responses (7)

Answers (3)

Verified answer

soma 24,406 on at

Like (0)

Report

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 Microsoft Employee on at

Like (0)

Report

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 1,800 on at

Like (0)

Report

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 32,738 on at

Like (0)

Report

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 1,800 on at

Like (0)

Report

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 1,800 on at

Like (0)

Report

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 32,738 on at

Like (1)

Report

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,