Sage 50 ODBC Aged Debtors

Posted by Carbon_Acc PM | on Wed, 08/02/2012 - 15:26 884 5 comments | report

Hello

I am trying to get my Sage aged debtors summary listing into Excel via ODBC to prepare ageing charts and credit control reports. I have managed to get the SALES_LEDGER table and AUDIT_HEADER with to the period I want to report to.

However the aged totals on the AUDIT_HEADER table relate to the current period todate on Sage and don't seem to relate to the periods I am reporting on. Is there a way of setting the criteria to a report on a certain date?

I can't seem to find the exclude later payments criteria option in the ODBC screen.

Am I using the right tables or is their an other way via ODBC.

I am not interested in excel integrated reporting, or any 3rd party add ons as I have quite a good pack developed already.

Hope that explains everything - hope some one can help..

Thanks

Comments

Aged Debtors

johndon68 | Wed, 08/02/2012 - 16:03 | Permalink

The aged figures are calculated 'on the fly' and, as far as I know, there is no way to run a query that will calculate the figures and exclude the later payments in the same way that Sage does when you run the report...

John

Must be possible

stevie | Thu, 09/02/2012 - 10:07 | Permalink

As Sage can create ADRs for any date and exclude later payments, it must be holding the data that makes this possible.

It's easy enough to create an unaged debtors listing in Excel via ODBC using transactions from the AUDIT_JOURNAL or AUDIT_SPLIT tables. To "age" the report you need allocations information and I think you can get this from the AUDIT_USAGE table.

All a bit complex and time-consuming so I'd just do the "Aged Balance" report from Sage and copy/paste it to Excel.

It is possible

johndon68 | Thu, 09/02/2012 - 10:25 | Permalink

stevie wrote:

As Sage can create ADRs for any date and exclude later payments, it must be holding the data that makes this possible.

It is certainly possible to do it but, even assuming it can be done via a single query, that query would be hideously complex.

The aged fields, if extracted 'as is' will always produce the current ageing. You can certainly produce an historical aged figure but this would, as you say, involve reading data from the AUDIT_USAGE table.

John

csv file?

paulwakefield1 | Fri, 10/02/2012 - 08:02 | Permalink

You could run the usual detailed aged debt report in Sage excluding later payments and export it as a csv file. The downside is that no headings come through.

However the the headings can be set up on a pro forma sheet. Combine this with a pivot table and it then just requires pasting of the csv file and a refresh of the pivot table. This could all be automated.

Aged debtors from Sage

jason.raikes | Fri, 10/02/2012 - 17:24 | Permalink

Our product DBSage has an aged debtors report in Excel that works from a date set by the user and using user configurable ageing buckets.

Please see http://www.pendragonsystems.com/sage.htm for more details or http://www.pendragonsystems.com/exsage.htm for an example of the report.

Please feel free to contact me if you have further questions.

Regards,

Jason