Getting Data out of Sage

The SAGE family of business management software packages is very popular with small to mid-sized companies in the UK and we are often asked “What is the best method to obtain data from SAGE for interrogation with IDEA?”

The first thing that should be said is that there is no “best way” to obtain data from Sage. Different users will have differing levels of access to the application and there are a number of options available to get data out of Sage, so there can be no all encompassing method that suits everybody.

What follows are guidelines to the main methods that can be utilised. There will be other methods not outlined here, however we have tried to set out a broad methodology to use and made it as “generic” as possible so that it will apply to the many variants of Sage (screen dumps and options are based on Sage Line 50 version 8).

If your organisation has a “Sage Expert”, these methods can be expanded and improved on. Alternatively, your local Sage dealer can assist with your particular version of Sage - they will be fully conversant with the export capabilities of Sage and will be glad to help.

Standard Sage Reports

There are a large number of standard report templates that can be used to generate information from within Sage. Selecting the report(s) to enable you to perform your interrogation will be determined by what you are intending to test.

Depending on your knowledge of Sage it may be a case of selecting the required report(s) (or requesting a Sage user to do this for you) or it may be that you have to take the time to find out what is produced by the different reports in order to find the reports you require.

The assistance of a regular Sage user would be very helpful in your search.

Each different area or module of Sage has its own set of report templates.

Simply open a module and then click on the Reports button to view a list of the standard report templates available for that module.

Many of these templates will be outside your area of interest and can be quickly disregarded, whereas others can be “previewed” to see if they will contain useful or relevant data.(See the Output settings on the right hand side of the window above.)

Clicking on the Run button will display of the preview of the report, however before it displays, a dialog box appears which will allow you to set parameters or “criteria” limiting the range of the report.

After setting the criteria as desired, the report will display.

The default option of the reports is to create a printout and the layouts of the reports are designed with this in mind.

Once the required templates have been identified, these can be used to create files that can be imported into IDEA. The reports can be sent to a “virtual printer”, essentially saving what would be output by a printer to a file. However, this can lead to difficulties with formatting and other problems trying to get the data into IDEA.

Fortunately, each report can also be saved in other file formats, clicking on the “Save As” button will provide the opportunity to save the report in a number of other formats. These include a text file format (.txt extension) which can be imported into IDEA using DataImport or Report Reader and, even easier to import, reports can be saved in comma separated variable (.CSV) file format.

Note that when a file is saved as a .CSV file, the column or field headers are not created along with the data, these will have to be added when the file is imported into IDEA using either the ImportAssistant or Record Definition Editor. It is worth recording the name of each column of data when previewing the report.

As well as creating reports using criteria to limit the range of information within the file, reports can be further customised by adding extra variables (the term used within Sage for fields of information). By the same token unwanted fields or variables can be removed from the report.

Having established which reports are needed for your work, they can be generated on a periodic basis for analysis without the need to revisit the content of each report.

If necessary a new user can be created within Sage that has access rights limited to the Reports section of each module, this would allow data to be obtained in the form of reports but no part of Sage where changes to the actual could be reached.

Custom Reports

Beyond the standard report templates there is the facility for users to create their own reports using the Sage Report Wizard.

The wizard can be started from the main toolbar by clicking on the Reports button.

This opens Report Designer, a tool which allows users to view and edit existing reports and create brand new reports. To design a new report click on the “New” report button -  - or select “New” from the “File” menu.

The Report Wizard can also be opened by clicking on the “New” button on the “Layouts” dialog box.

The Report Wizard tool is a “step by step” wizard enabling the user to select exactly what data goes into a report. It can open up data that is held within Sage but not normally created as part of standard report, so it can give the user a higher degree of control over the content of the report.

For example; Sage holds a large amount of data related to transactions in two files, Audit_Headers and Audit_Splits. Normally these files are not directly accessible through Sage but they can be recreated as reports. These files can then form the basis of the data needed to perform an audit of the system as the transaction history can be recreated from these files.

The second screen of the wizard (after the Introduction screen) provides the choice of the type of document to be created;- Reports, Layouts, Letter and Label. The only useful document types for generating data for IDEA are the “Reports”.

The “Reports” tab will also display the options for the area or module on which the report will be based. Each different area will allow access to different tables which underlie the information within Sage.

For example, the Customer reports will allow access to the data in the Audit_Header, Audit_Split, Audit_Usage, Sales_Ledger and Tax_Code tables, whereas, by selecting the Supplier reports the tables that can be accessed are the Audit_Header, Audit_Split, Audit_Usage, Tax_Code and Purchase_Ledger.

It would probably take trial and error to find which report type will allow the access you require and it may be that different report types will need to be used to get all the required data.

After selecting the report type, the next screen will show what “variables” or fields of information can be included in your report;- this is also where you discover what tables are available.

(Sage Line 100 has more “obtuse” variable names)

Use the “Add>” button to add the required fields of information from the tables into your report – note that the fields will be recreated in the order you select them so it is worth taking notes at this point. If the name of the field does not clearly describe what information it holds, the “Variable Info” button can reveal a (very) short description. Once all the variables you need have been added to the report move onto the next screen.

The “Variable Sizes” screen is where the size of the field on the output to the printer can be controlled. If saving the file as text file or as a delimited file these settings should not make any difference to the about so the screen can be skipped.

The next screen, “Groups”, is where the user can specify how the information in the report is grouped together. This is simply a case of selecting one or more variables. E.g if a date field is selected all the records from the same date will appear together in the report. Once again, this is optional if the data is to be used within IDEA as the data records can be re-arranged post importation.

Similar to “Groups” the next screen, “Sorts” allows you define the order of the data, any groupings already set on the previous screens are available to control the order of the report (ascending or descending) and additional variables can be added.

The ”Totals” screen allows a grand total for any numeric variables to be added to end of the report. Again this is not something that is needed in the data to be imported into IDEA but it can be useful to preview this to be used as a control total.

On the “Criteria” screen users are given the opportunity to set which parameters can be used to limit the scope of the report. Here there is a much wider range of parameters that are available when using standard reports.

Finally “Print Options” are the settings for the printer to be used for the report, as the report is to be saved as a file these are not relevant. Click on the Finish button and the report will be displayed in layout form with an alternate view of “preview“  tab available.  Click on the “preview” tab before trying to save the report or you will only save the report layout.

As before, this can be saved in a number of formats, select Save As from the File menu and then select a location to save to and either Text files or Comma Separated Files as the Save as type option.

ODBC Imports

Open Database Connectivity (ODBC) is a programming interface that enables programs to access data in database management systems that use Structured Query Language (SQL) as a data access standard. ODBC is a standard feature of the Windows operating system and can be utilised by IDEA to import data.

The nature of ODBC is quite technical so assistance may be required to set it up.

To access data from a particular application, an “ODBC driver” is required from the manufacturer of the software and this is used to create a “data source”. A “data source” will link to a specific location and locate the data files used within the application.

The ODBC driver installer from the manufacturer will install and create a data source for you, the installer may be included along with the main Sage installation files, it may be an added extra you have to install from the Sage CD-ROM or it may be something that you need to obtain from the manufacturer as a separate component (Note: there may be a charge for this).

In the case of Sage Line 50 v8, within the application folder there is a subfolder called ODBC32 that contains an executable to set up the driver on a workstation. This creates a data source with a default link to where Sage expects to find some company data. The file path used by the ODBC link has to be modified to suit each “company” set up within Sage – the ODBC link can only be configured to look at one “company” at a time. The file path can be modified using the Data Sources (ODBC) section of Administrative Tools found in Control Panel.

Once established an ODBC link will allow you use ImportAssistant to view the data tables that Sage uses, choose which ones you need and import them into IDEA. There is also an advanced facility to add a “query” to filter which records are imported.

To access the Sage data via the ODBC link you must have “Financial Controller” or “Manager” permissions, that is, the user name and password of a user with this level of permission if you are not one yourself.

IDEA will utilise ODBC to create a “read-only” link to the data tables, this means that no changes can be made to source data. However, once an ODBC data source has been created it can be used by other applications and these may be able to have “read and write” with the obvious data integrity implications.

It is recommended that a copy or archive of the data to be interrogated is used rather than linking directly to “live” data.

The Future

The latest version of Sage Line 50 (version 10) has a “Send to” option that allows users to send many screens and reports directly to Microsoft Office and in particular Microsoft Excel. As organisations migrate to the new version of Sage this facility will make acquiring data easier.

There are also plans to create a “custom import” method as an add-in feature for IDEA, this would probably use a Sage back up file to access the data ensuring that work can continue without interference. However, due to the variations of data structures found in the different versions of Sage this might take some time to create.