Avatar of Jules74
Jules74
 asked on

Sage 50 Accounts Professional 2011 - automated reports

Hi folks,
I need to get a daily scheduled report of aged creditors (what we owe our suppliers and how overdue the payment is) to run in Sage 50 Accounts Professional 2011.
It doesn't matter how it runs as long as I can have it automatically scheduled and it can output to a file or email.

Optional ingredients:
1
We have SDO running on Sage so for example the following will output results in a browser:
http://localhost:5493/sdata/accounts50/SDO/-/customers
Presumably the correctly crafted query could pull up the supplier information we need.
2
Am ok with: scripts/VB.Net/Java (or C++ if nothing else)
3
Maybe task scheduler to run a script or executable?
4
Don't want to shell out for Sage SDK etc as it costs a bomb
5
Theoretically we should be able to set up an ODBC to the Sage data (haven't been able to have the ODBC driver show in the ODBC data sources yet)

All ideas gratefully received.

Cheers
Tax / Financial SoftwareDatabasesSoftwareWeb Languages and StandardsScripting Languages

Avatar of undefined
Last Comment
Jules74

8/22/2022 - Mon
leakim971

Please note than the ODBC driver is read only.
You can backup the whole Sage Account folder data with the MS Windows Scheduler

http://www.addictivetips.com/windows-tips/how-to-backup-and-restore-important-filesfolder-in-windows-7/
https://www.experts-exchange.com/questions/27605996/Sage-Accounts-2011-Automatic-Backup.html
Jules74

ASKER
Thanks leakim971 - I'm looking for a report from the software, rather than backing it up.
leakim971

there's nothing in the 2011 version, you need to build your own software or use a third party tools or just upgrade to the 2012
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Jules74

ASKER
hmm - thanks. I'm looking at AutoIt - will post solution if I can work something out. I don't see anything in the 2012 version (or 2013 version) that supports automated reports.
ASKER CERTIFIED SOLUTION
Bruce Denney

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Jules74

ASKER
Thanks! Good idea! Will try that out (need to get Excel onto the Sage machine)
Jules74

ASKER
Didn't manage to get Excel to pull in data - something to do with 64-bit Excel and 32-bit Sage ODBC connector not wanting to work together.

But using odbc query tool gave me enough of a foothold to browse the table headers. In case anyone else finds it useful here's an SQL query that gave me the info I was looking for:

SELECT ACCOUNT_REF, INV_REF, AGED_30, AGED_60, AGED_90, AGED_OLDER FROM AUDIT_HEADER WHERE TYPE=PI AND (AGED_30 > 0.00 OR AGED_60 > 0.00 OR AGED_90 > 0.00 OR AGED_OLDER > 0.00) ORDER BY (AGED_30+AGED_60+AGED_90+AGED_OLDER) DESC

Thanks for your help!
J
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
leakim971

http://support.microsoft.com/kb/942976/en

C:\Users\Jules74>cd %systemdrive%\Windows\SysWoW64
C:\Windows\SysWOW64>Odbcad32.exe
Jules74

ASKER
cheers leakim971 - I went through a few goes trying to add a user DSN with the 32-bit and 64-bit versions of odbcad32.exe but either way I couldn't get msqry32.exe (in Excel: Data->From Other Sources->From Microsoft Query) within Excel to read properly from the user DSN. The message was something along the lines of "driver mismatch - wrong architecture". I'll post the actual message when I get back to the office. msqry32.exe never seems to show the Sage system DSN, only a user DSN.
Bruce Denney

As I understand it, it is not possible to access the data with 64bit drivers, the only option is to use a 32 bit application with the 32 bit drivers.

LibreOffice is FREE and an obvious replacement for Excel 2010
 (You could un-install the 64 bit version of excel and reinstall the 32 bit version).
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Jules74

ASKER
Thanks very much Bruce. Confirmed that LibreOffice Base will pick up the data.