Skip to main content

Notifications

Microsoft Dynamics GP (Archived)

Using aging buckets

Posted on by 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

  • Verified answer
    painterisaac Profile Picture
    painterisaac 3,015 on at
    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 Profile Picture
    painterisaac 3,015 on at
    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 Profile Picture
    painterisaac 3,015 on at
    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 Profile Picture
    L Vail 65,271 on at
    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 Profile Picture
    L Vail 65,271 on at
    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 Profile Picture
    Community Member Microsoft Employee on at
    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 Profile Picture
    painterisaac 3,015 on at
    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 Profile Picture
    Richard Whaley 25,195 on at
    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 Profile Picture
    Richard Wheeler 75,730 on at
    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 Profile Picture
    painterisaac 3,015 on at
    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.

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!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templatesâš¡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,030 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans