Aged A/R report combining companies

[barbola]

I have been asked to find a report of all aged A/R over 60 days by company, by customer, by currency, on one report at each month end.

There are probably 10 companies I want to combine and they have some of the same customers. I think I have to go outside Great Plains to do this.

I found a thread suggesting tables RM00101, RM00201, RM20101, RM30101 and possibly RM20201 and RM30201. I'm not sure which fields and dates to use to figure out "aged A/R over 60 days" as I'm not an accountant.

Please don't suggest Crystal as I don't have time to learn it. I would prefer using SQL views, maybe Excel or Access. Does anyone have any ideas for me?

Thanks.

 

[VictoriaYudin]

Does this need to be a historical aged TB? Or just aged? The historical is much more difficult, as you need to back out what's been applied, using a combination of apply dates transaction dates and you need to take Voids into consideration. You also need to decide upfront if you're using Doc Dates or GL Dates (whether the report is historical or not).

We've done similar reports in the past (but without Mulitcurrency) and this is a lot of work. I think the original historical trial balance was at least 40-50 hours of work. We typically do this in SQL stored procedures, then you can choose what output method you use, most of our customers have chosen Excel.

The tables you have above look right, you will also need to add SY01500 from Dynamics for the multicompany part.

Victoria
Flexible Solutions, Inc.

 

[barbola]

Great thanks! I know back when I was trained to use GP, I was told to always use the historical, but I noticed our users now seem to use the Aged TB with Options. I'm really not sure if they are getting what they need from that report.

I don't think I can program this myself and will probably have to outsource it.

thanks for the info.

 

[VictoriaYudin]

Yep, I always train users to only use the Historical report. It does everything the others will and more.

If you are looking for someone to outsource reports to, please keep us in mind.

Victoria
Flexible Solutions, Inc.