Open Forum

Expand all | Collapse all

AR aging - 1 line

  • 1.  AR aging - 1 line

    GOLD CONTRIBUTOR
    Posted 22 days ago
    My boss is asking for a Sales aging report, but only wants 1 line for  each customer.  I can to the Historical Receivable Trial Balance, but there is a lot of white space on the report.

    Thanks

    ------------------------------
    Danita Simon
    Senior Accountant
    Detroit Metro Convention & Visitors Bureau
    Detroit MI
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: AR aging - 1 line

    TOP CONTRIBUTOR
    Posted 22 days ago
    Hi @Danita Simon,

    Are you wanting that historical component (i.e. being able to age backwards/forwards) or are you okay with it always being a current aging (as of today/today's date)?

    There are several options I can think of to consider:

    Historical Trial Balances:
    1.) You can export the report to a tab delimited, but that would require some manual clean up to get rid of the blank lines/white space.
    2.) You could look at creating a new SSRS version of the report or modifying the existing one to have less space.
    3.) You could leverage the stored procedures around the historical aged trial balance and build a refreshable excel report that presents the report directly in excel in the aesthetic layout you desire.

    If you're okay with current aging, I'd recommend looking at @Victoria Yudin 's script referenced below. You may need to tweak the code a bit as it has hardcoded aging buckets and is based on due date, but it's pretty straight forward to make those changes.

    If you use the code below, you can easily drop it into a SmartList, SSRS report, or refreshable excel report.

    https://victoriayudin.com/2012/01/25/sql-view-for-current-receivables-aging-in-dynamics-gp/​​

    Does that make sense?   If you have questions, let me know.

    Thanks,

    Samantha

    ------------------------------
    Samantha Higdon ,CPA,CGMA
    Consultant
    Lagom, LLC
    Carmel IN
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: AR aging - 1 line

    GOLD CONTRIBUTOR
    Posted 22 days ago
    I made a view to extract the AR Trial Balance detail.  I linked this to an excel extraction :

    Then, to fill the need, you just have to create a pivot table to have the sum of the transactions, which gives a 1 line per customer AR situation.

    You can also, as you may see in my excel extraction, generate the aging based on the buckets you want to look at, and then add this to the pivot to have the bucketed AR (0-30, 30-60, 60-90, 90+)...

    Sounds like a viable solution or you prefer a report ?


    ------------------------------
    Luc St-Yves, Senior Business Analyst
    Humanware Technologies, QC (Canada)
    Implementation projects leader
    -Essilor Instruments USA and Canada
    -Essilor International S.A.S., France
    --Promoting Dynamics GP in Europe--
    ------------------------------

    Academy - Online Interactive Learning from Experts


If you've found this thread useful, dive deeper into User Group community content by role