PeopleSoft Adoption Made Smooth & Simple!
On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool. Claim Your Free WalkMe Account Now
Course Of The Month
9 days, 15 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Sage 50 Accounts Professional 2011 - automated reports
Posted on 2013-01-04
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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
6
3
2
12 Comments
Active 1 day ago
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/
http://www.experts-exchange.com/Software/Industry_Specific/Financial/Q_27605996.html
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/
http://www.experts-exchange.com/Software/Industry_Specific/Financial/Q_27605996.html
Active 1 day ago
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
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.
Hi.
Scripting with AHK or AUTOIT would work, but a little flaky.
The way I would do it is
An excel spreadsheet that pulls data from Sage via ODBC (creates report(s) using a pivot table or some such)
Al little VBA in the sheet to automate it and save it/send it as email and then close the sheet
set this to run when you open the sheet.
Schedule the opening of the sheet with windows scheduler.
Scripting with AHK or AUTOIT would work, but a little flaky.
The way I would do it is
An excel spreadsheet that pulls data from Sage via ODBC (creates report(s) using a pivot table or some such)
Al little VBA in the sheet to automate it and save it/send it as email and then close the sheet
set this to run when you open the sheet.
Schedule the opening of the sheet with windows scheduler.
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+A
Thanks for your help!
J
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+A
Thanks for your help!
J
Active 1 day ago
http://support.microsoft.com/kb/942976/en
C:\Users\Jules74>cd %systemdrive%\Windows\SysW
C:\Windows\SysWOW64>Odbcad
C:\Users\Jules74>cd %systemdrive%\Windows\SysW
C:\Windows\SysWOW64>Odbcad
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.
Featured Post
Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article was originally published on Monitis Blog, you can check it
here
.
If you have responsibility for software in production, I bet you’d like to know more about it. I don’t mean that you’d like an extra peek into the bowels of the sourc…
- Software-Other
- Web Applications
- Magento
- Web Development Software
- Monitis
- Networking, *magento2
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL).
Depending on business requirements, a single Stored Procedure can return differe…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
- Document Management
- Document Imaging
- Programming
- Adobe Acrobat
- Scripting Languages
- Printers and Scanners, Images and Photos, Photos / Graphics Software, *Scripts, *PDF
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
- Document Management
- Document Imaging
- Programming
- Adobe Acrobat
- Scripting Languages
- Printers and Scanners, *Scripts, *batch, *PDF
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month9 days, 15 hours left to enroll
623 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.