Using aging buckets

(0) Share

Report

Posted on by painterisaac

3,015

I'm not sure if this is the right forum to be asking this question but I thought that I would give it a shot.

I am trying to create an aging report for a particular customer class, with GP data, using the report builder that comes with report manager.

I am using the ReceivablesManagement database and the receivables transaction table. The fields I need are customer name, customer PO number and aging buckets 30 days apart (up to 120 days).

The part I am struggling with is getting the aging buckets to be thirty days apart.

I am very grateful for any help that is offered.

*This post is locked for comments

All responses (11)

Answers (3)

Verified answer

painterisaac 3,015 on at

Like (0)

Report

RE: Using aging buckets

If anyone cares to know, I figured this out.

Current

=IIF(DateDiff("d",Fields!Document_Date.Value, Now())<=30, Fields!Current_Trx_Amount.Value, "")

31-60 days

=iif(DateDiff("d",Fields!Document_Date.Value,now())>=31 and DateDiff("d",Fields!Document_Date.Value,Now())<=60,Fields!Current_Trx_Amount.Value,"")

61-90 days

=iif(DateDiff("d",Fields!Document_Date.Value,now())>=61 and DateDiff("d",Fields!Document_Date.Value,Now())<=90,Fields!Current_Trx_Amount.Value,"")

91-120 days

=iif(DateDiff("d",Fields!Document_Date.Value,now())>=91 and DateDiff("d",Fields!Document_Date.Value,Now())<=120,Fields!Current_Trx_Amount.Value,"")

121-150 days

=iif(DateDiff("d",Fields!Document_Date.Value,now())>=121 and DateDiff("d",Fields!Document_Date.Value,Now())<=150,Fields!Current_Trx_Amount.Value,"")

151+ days

=IIF(DateDiff("d",Fields!Document_Date.Value, Now())>=151, Fields!Current_Trx_Amount.Value, "")

If you correlate this with the screenshots it will make sense on how to apply it to your situation.
painterisaac 3,015 on at

Like (0)

Report

RE: Using aging buckets

So I THINK I found where it needs to be put; however, it is giving me an error.

Am I doing this right?
painterisaac 3,015 on at

Like (0)

Report

RE: Using aging buckets

Please excuse my ignorance but where and how do I put in this information. I understand that it is SQL and that it is code to pull different data in different ways back to create a consolidated report. What I don't understand is how to actually implement what you are suggesting.

This is a learning process for me so about 50% of your acronyms don't mean anything to me. Thank you for your patience and assistance.
Verified answer

L Vail 65,271 on at

Like (0)

Report

RE: Using aging buckets

Hi again,

Here's what I use to produce my summary report.

Kind regards,

Leslie:

select

CASE WHEN

DATEADD ( d , -30, GETDATE() ) > T1.DOCDATE AND

DATEADD ( d , -45, GETDATE() ) <= T1.DOCDATE

THEN

CASE T1.RMDTYPAL

WHEN 7 THEN -1*T1.CURTRXAM

WHEN 8 THEN -1*T1.CURTRXAM

WHEN 9 THEN -1*T1.CURTRXAM

ELSE T1.CURTRXAM

END

ELSE 0 END

as [31 - 45],

CASE WHEN

DATEADD ( d , -60, GETDATE() ) > T1.DOCDATE AND

DATEADD ( d , -75, GETDATE() ) <= T1.DOCDATE

THEN

CASE T1.RMDTYPAL

WHEN 7 THEN -1*T1.CURTRXAM

WHEN 8 THEN -1*T1.CURTRXAM

WHEN 9 THEN -1*T1.CURTRXAM

ELSE T1.CURTRXAM

END

ELSE 0 END

as [61 - 75],

CASE WHEN

DATEADD ( d , -90, GETDATE() ) > T1.DOCDATE

THEN

CASE T1.RMDTYPAL

WHEN 7 THEN -1*T1.CURTRXAM

WHEN 8 THEN -1*T1.CURTRXAM

WHEN 9 THEN -1*T1.CURTRXAM

ELSE T1.CURTRXAM

END

ELSE 0 END

as [Over 90],

CASE WHEN

DATEADD ( d , -30, GETDATE() ) <= T1.DOCDATE

THEN

CASE T1.RMDTYPAL

WHEN 7 THEN -1*T1.CURTRXAM

WHEN 8 THEN -1*T1.CURTRXAM

WHEN 9 THEN -1*T1.CURTRXAM

ELSE T1.CURTRXAM

END

ELSE 0 END

as [0 - 30],

CASE WHEN

DATEADD ( d , -45, GETDATE() ) > T1.DOCDATE AND

DATEADD ( d , -60, GETDATE() ) <= T1.DOCDATE

THEN

CASE T1.RMDTYPAL

WHEN 7 THEN -1*T1.CURTRXAM

WHEN 8 THEN -1*T1.CURTRXAM

WHEN 9 THEN -1*T1.CURTRXAM

ELSE T1.CURTRXAM

END

ELSE 0 END

as [46 - 60],

CASE WHEN

DATEADD ( d , -75, GETDATE() ) > T1.DOCDATE AND

DATEADD ( d , -90, GETDATE() ) <= T1.DOCDATE

THEN

CASE T1.RMDTYPAL

WHEN 7 THEN -1*T1.CURTRXAM

WHEN 8 THEN -1*T1.CURTRXAM

WHEN 9 THEN -1*T1.CURTRXAM

ELSE T1.CURTRXAM

END

ELSE 0 END

as [76 - 90],

CASE T1.RMDTYPAL

WHEN 7 THEN -1*T1.CURTRXAM

WHEN 8 THEN -1*T1.CURTRXAM

WHEN 9 THEN -1*T1.CURTRXAM

ELSE T1.CURTRXAM

END as [Current_Bal],

CASE T1.RMDTYPAL

WHEN 7 THEN -1*T1.CURTRXAM

WHEN 8 THEN -1*T1.CURTRXAM

WHEN 9 THEN -1*T1.CURTRXAM

ELSE T1.CURTRXAM

END as [Original Amt],

T1.[CUSTNMBR],

T2.[CUSTNAME]

from [TWO]..RM20101 T1 with (nolock)

INNER JOIN [TWO]..RM00101 T2 with (nolock)

on

T2.[CUSTNMBR] = T1.[CUSTNMBR]

where

T1.[CURTRXAM] <> '0'
L Vail 65,271 on at

Like (0)

Report

RE: Using aging buckets

Hi,

I've also done this one in SmartList Builder - which is easy to switch to Excel. I don't know so much about MR. Victoria may be your best bet there.

I have SLB objects in both summary and detail. I'll be happy to send it to anyone who will send me an e-mail at leslievail@earthlink.net and ask for it.

Kind regards,

Leslie
Verified answer

Community Member Microsoft Employee on at

Like (0)

Report

RE: Using aging buckets

Victoria Yudin has written the code for you. You can use the view for a SmartList or SRS report.

victoriayudin.com/.../sql-view-for-current-receivables-aging-in-dynamics-gp

-Trevor
painterisaac 3,015 on at

Like (0)

Report

RE: Using aging buckets

I have scanned through the "out of the box" reports and none of them serve the purpose for what we need.

The aging report detail is too much. We don't want prompts.

The aging by customer is by one customer at a time so it is too little.

We just need it to look like this:

Are there other aging reports that I am not aware of that can do this?

I watched this youtube video on how to do it in excel and this doesn't seem too hard. I don't know how to apply this to RB though.

http://www.youtube.com/watch?v=oXLD1CaG30E

Thank you for the help thus far!
Richard Whaley 25,195 on at

Like (0)

Report

RE: Using aging buckets

I agree with others that using an existing report would be easier. Otherwise you will need to do some serious calculations

You need to create several date starting with the ageing date as date A, then B is A minus 30 days, the C is B minus 30 days, the D is C minus 30 days for as many buckets as you need.

Then for each open item, you have to do a series of If statements

If the date is between A and B, then add to C1 and print in Column 1

if the date is between B and C, then add to C2 and print in column 2

continue until the last one reads

If the date is before (last date) add to column C(last) and print in Column (last)

print your totals out when the customer changes

Add the c totals to the R totals

Repeat above until the list is complete

Print the R(eport) totals

Yes, this is a simple explanation, the if statements really need to say date<=A and Greater than B etc
Richard Wheeler 75,730 on at

Like (0)

Report

RE: Using aging buckets

Have you deployed the SSRS version of reports for this company? If so, you can link those reports to BP.
painterisaac 3,015 on at

Like (0)

Report

RE: Using aging buckets

Because we cannot display that report on Business Portal.

The out of the box aging report in report manager requires the use of prompts to pull the data you want. We don't want our managers to have to use the prompts. We want it to simply display.