web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

Community site session details

Community site session details

Session Id :
Microsoft Dynamics GP (Archived)

Aging report 30 days apart

(0) ShareShare
ReportReport
Posted on by 3,015

I need an aging report that looks like this in REPORT BUILDER (not excel, like the example). The out of the box aging reports do not serve our purposes. 

5710.example2.JPG

The formulas in excel to create this are as follows...

4186.example3.JPG

7382.example4.JPG

5707.example5.JPG

How do I write these formulas in report builder? I know that I need to pull by Fields! rather than by cells (i.e. C2) but it doesn't seem to be working for me. Below is what my report looks like. I am using the ReceivablesTransactions table in Report Builder 2.0. 

2063.example6.JPG

I just need to know how to translate my excel formulas into SQL formulas. Any help is appreciated. 

*This post is locked for comments

I have the same question (0)
  • Verified answer
    painterisaac Profile Picture
    3,015 on at

    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.

  • Victoria Yudin Profile Picture
    22,769 on at

    I personally have found it preferable to write the actual logic in SQL directly, create a view or stored procedure, then point whatever reporting tool I want to work with to that SQL view or stored procedure. If you're going this route and want some sample SQL code, here are links to some views I have posted that are similar to what you're doing:

    Receivables Current Aging Summary: victoriayudin.com/.../sql-view-for-current-receivables-aging-in-dynamics-gp

    Receivables Current Unapplied Transactions: victoriayudin.com/.../sql-view-for-all-unapplied-receivables-transactions-in-dynamics-gp

  • painterisaac Profile Picture
    3,015 on at

    I am still very new to SQL so using an expression was easier for me. Once I advance in my knowledge I'm sure that the way you are suggesting will be easier. Thank you.  

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans