Solved

Sage 50 Accounts Professional 2011 - automated reports

Posted on 2013-01-04
12
2,184 Views
Last Modified: 2013-01-09
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
0
Comment
Question by:Jules74
[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
 
LVL 82

Expert Comment

by:leakim971
ID: 38749632
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
0
 

Author Comment

by:Jules74
ID: 38749975
Thanks leakim971 - I'm looking for a report from the software, rather than backing it up.
0
 
LVL 82

Expert Comment

by:leakim971
ID: 38750388
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
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:Jules74
ID: 38750606
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.
0
 
LVL 10

Accepted Solution

by:
Bruce Denney earned 500 total points
ID: 38750927
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.
0
 

Author Comment

by:Jules74
ID: 38751268
Thanks! Good idea! Will try that out (need to get Excel onto the Sage machine)
0
 

Author Comment

by:Jules74
ID: 38754425
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
0
 
LVL 82

Expert Comment

by:leakim971
ID: 38754466
http://support.microsoft.com/kb/942976/en

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

Author Comment

by:Jules74
ID: 38754602
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.
0
 
LVL 10

Expert Comment

by:Bruce Denney
ID: 38758604
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).
0
 

Author Comment

by:Jules74
ID: 38759576
Thanks very much Bruce. Confirmed that LibreOffice Base will pick up the data.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
size of file 1 22
return value based on substr 10 48
MS Access TransferDatabase with ODBC - Receive Run-time error '2507' 1 23
Capture Perfect Software 3 26
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question