Excel zone
Feature
Management Reporting in Sage Line 50 and Instant. By David Carter
There are at least five ways of getting data out of Sage and into Excel.- Display a screen, then File-Send To-Excel
- Print a report, then Save as CSV
- Create a report in the Report Designer
- Sage EIR (Excel Integrated Reporting)
- Import into Excel via ODBC and Query
1. DISPLAY A SCREEN, THEN FILE-SEND TO-EXCEL
The simplest way to get data out of Sage and into Excel is from the screen. From main menu choose File – Send to - Excel. But there are problems. First, all dates come over as text, so you can't sort transactions into date order. Second, all numbers are exported as positives (eg both invoices and credit notes), so once they're in Excel you can't add them up!
We ran a three-part series on sending the data on the Financials screen to Excel. But all it did in the end was to prove what a mess it all was. A great pity because File-Send to Excel is very easy and powerful, but these faults make the data unusable:
Tips on Exporting from Sage Line 50 into Excel
Tips on Exporting from Sage into Excel – Part 2
Exporting Sage data Part 3 – Building a P&L in Excel
2. PRINT A REPORT, THEN SAVE IT AS CSV
If you generate a report and save it as type CSV (comma separated variable), Sage strips out the formatting and just saves the data. Many people, for example, save the Audit Trail, Detailed report in this way. However, it loses the column headings and, obviously, you are limited to the data in the report.
The Sage Line 50 and Pivot Tables tutorial shows how to export the Nominal Activity Report (NOMACTIV) out of Sage as CSV, then build up reports from it with pivot tables.
3. CREATE A REPORT IN THE REPORT DESIGNER
Exporting into Excel is fine if you want to write a once-off report. But if you want to create a report that is going to be used regularly, you should either live link via ODBC (see below) or use the Sage Report Designer.
The Report Designer is available in both Line 50 and Sage MMS, but has a steep learning curve. In the first tutorial Getting to Grips with the Sage Report Designer - 1 we modified the standard Purchase Daybook report in Sage and added essential fields such as the supplier name.
Sage has added a Wizard to make the Report Designer easier to use. The second tutorial Getting to Grips with the Sage Report Designer - 2 showed you how to use the Wizard to build the Purchase Daybook report from scratch. But it turned out a lot more difficult than it should be.
Sage enhanced the Report Designer in version 13 (Sage 2007). However, my version kept crashing so I wasn't able to test it. I'll be testing the Sage 2008 version shortly.
4. SAGE EIR (EXCEL INTEGRATED REPORTING>
EIR is an add-in module which installs itself into your copy of Excel. When you go into Excel, there is a now a new Sage menu or toolbar. This contains a list of about 30 Sage reports which you can produce in Excel.
There is also a “Refresh” button. Simply click this button and any new transactions in Sage will be updated into the Excel report automatically.
In fact you can run ANY Sage report through EIR, simply by going into the Report Designer and saving a copy of the report into the Sage-Reports-Excel folder.
EIR has been included since version 11, and it's free. For more details, see the recent review.
5. IMPORT INTO EXCEL VIA ODBC AND QUERY
Personally, I think this is the best way. The data comes over cleanly and, once set up, it's very easy. The hardest bit is setting up the ODBC link in the first place. This tutorial shows how:
Tutorial MSQuery and Sage #1: How to Set up Import External Data
At the simplest level you might want to import this month's nominal balances into Excel, then use it to create a P & L. That's explained in Tutorial MSQuery and Sage #2: Import Balances into Excel.
But Sage doesn't store many balances, and if you want to create P&L reports by department, for example, you will have to import transactions into Excel and calculate the balances in a pivot table. This tutorial shows how to create an "analysis-ready file" of transactions. Tutorial: MSQuery and Sage #3: Set up your analysis-ready file
It isn't possible to get all the data you need for management reporting out of Sage. These three tutorials show how to add the missing items.
Tutorial: MSQuery and Sage #4: Add financial year and period number
Tutorial: MSQuery and Sage #5: Add nominal categories
Tutorial: MSQuery and Sage #6: Add customer and supplier names
If you sell products as opposed to services, you need to extract the data from the invoice and invoice_item tables.
Tutorial: MSQuery and Sage: Create an analysis ready file for sales
TIPS and ANY ANSWERS
You can find some useful Any Answers tips on report writing in Sage in:
Sage Tips and Queries #1
Sage Tips and Queries #2
Number of comments: 4
AccountingWEB.co.uk 3-Oct-2007
Categories: ExcelZone Features, Management Reporting Features, IT Features
Times read: 9870
At Delandale Solutions we have developed Excel tools which allow departmental analysis management reports, multi-compnay into one spreadsheet if required using SQL which allows powerful reporting abaility. www.delandalesolutions.com
Pretty good, really Ursula... David Carter and I both looked at Sage Intelligent Reporting when the application first came out and really rated the way it made OLAP analysis so easy with Sage Line 50. There will always be some minor user experience issues with such a powerful application, but it was not that hard to pick up.
David pointed out some shortcomings on getting data out for margin analysis, but initially thought it would be a very good tool for accountants who prepare managment reports for their clients - especially with the Financial Statements designer that was very good at tidying up messy trial balances. Since the initial review, Sage tool on board some of the suggestions and now have a tool that extends into wider sales and financial analysis reporting.
See our 2006 review of Sage Intelligent Reporting for more details. John Stokdyk
If you need more details or want a demo, please contact me at info@sage-pastel.co.uk. Thanks.
Technology editor
AccountingWEB.co.uk
David,
Very useful tutorials, thank you. How do you rate Sage Intelligent Reporting ?
The lesson given by Carter is really good. However, to save all these hassle, we have a 3rd party software designed by Alchemex for Sage 50. The BI tool has been designed specifically for Sage 50 and gives you all you reports in excel at one click. You have sevral types of reports reanging from Master fiels to Management reports.
