Historical Receivables Aged Trial Balance Summary in Smartlist

(0) Share

Report

Posted on by Community Member

I am trying to create a smartlist that shows me each customer and their current balance as of a certain date in time (historical). I know the Customers object will show aging buckets and and summarize the totals but doesn't allow me to filter on date. We own Smartlist Builder so I was hoping someone has alreaady created this so I can replicate or provide the tables and fields and restrictions and calculatons necessary to make it work.

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (0)

Mike Smith 6,840 on at

Like (0)

Report

I could be incorrect but there really isn't a way to do this in SmartList or SmartList Builder. The "Search" functionality is used for a filter and not necessarily feeding a stored procedure or other to carry out the calculations needed to replicate the functionality behind the Hx Aged TB.

If users are after SmartList type of details, I would recommend starting with one of the standard SRS reports, creating a copy, and modifying this until you get to the point of the details you and the users are after.

Hopefully, someone else has a better idea for doing this in SmartList or SmartList Builder.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Ryan,

Thank you for using the Forums.

There currently isn't a way for us to print the balance for customers as of a certain date using Smartlist nor Smartlist Builder. These reports pull the data from the tables and there isn't a table that stores the customer balance for each day of each year.

However, as Mike S mentioned, you could use the Reporting Services Reports: Historical Aged Trial Balance report and export a copy of it to Excel.

If you have any further questions, please let me know.

Thank you,

Was this reply helpful? Yes No
Ian Richardson 4,150 on at

Like (0)

Report

I am exporting the historical aged trial balance to excel I am having a heck of a time getting the columns of data to line up with the column headings. Is there a way in report writer to force the columns into certain positions when the export to excel happens?

Was this reply helpful? Yes No
Suggested answer

Frank Hamelly | MVP... 46,625 Super User 2025 Season 2 on at

Like (0)

Report

As you've seen Ian, that is a really ugly way to get the HATB into Excel. What version are you on? An SSRS HATB has been available in GP since version 10.0. That will export to Excel with ease.

Was this reply helpful? Yes No
Ian Richardson 4,150 on at

Like (0)

Report

Hi Frank

We are GP2010. I looked in ssrs, and can only see the functional currency HATB. We are multicurrency and need the MC HATB which sadly is a different report.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Frank,

I am having the same issue as Ian. My customer want their MC Payables Historical Aged Trial Balance and MC Receivables Historical Aged Trial Balance reports to be perfectly aligned after exporting to Excel. This just doesn't happen. I am exporting as comma-delimited files.

Is there any way to overcome this?

Thanks in advance

Regards,

Saras

Was this reply helpful? Yes No
Ian Richardson 4,150 on at

Like (0)

Report

Hi Saras

I was eventually able to get the report via report writer to line up. I had to remove a lot, and I mean a lot of the header information so that there was only only value in the header that lined up with the information in the body of the report.

Coincidentally this post came up just yesterday on a similar topic.

community.dynamics.com/.../256886.aspx

I have not tried the stored proc referred to, but it may assist.

Was this reply helpful? Yes No