Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Tutorial: use MSQuery to create an analysis-ready SALES file in Sage

by
26th Sep 2007
Save content
Have you found this content useful? Use the button above to save it to your profile.

Continuing his series on live-linking Excel to Sage data, David Carter shows you how to use MS Query to pull in data from the invoice data tables. If you use Sage for sales invoicing, this will give you the data you need to report on sales and margins by product and customer

Up to now we’ve concentrated on getting data out of Sage to produce accounting reports. For this, most of the data is held in the audit_journal table.

However, if you want to report on product sales, the data is held in the invoice and invoice_item tables.

In this tutorial we’ll extract from Sage Line 50 (or Instant) all the data you need to produce reports of sales by customer, sales by product by customer, margins by customer and by product, sales by area or market segment, etc etc. And remember, the huge advantage of importing data into Excel rather than exporting it out of Sage is that, once you've created your reports, to run them next month you simply have to press the Refresh button and they update automatically.

Before you start

  • In order to link Excel direct to your Sage data, you should first have set up Sage as a Data Source in Excel. If you haven’t yet done this, follow the instructions in the first tutorial in the series Tutorial: How to set up MS Query with Sage
  • This tutorial assumes you have some prior acquaintance with MS Query. If you’ve never used Query before, it might be worth going through Tutorial #3: How to set up an analysis ready file in Sage. This explained how to import an accounting data file into Sage. It assumes no knowledge of Query and explains in a lot more detail.
  • If you’ve never dealt with sales reporting before, you might find it useful to read the first Northwind Tutorial . This creates a sales orders database from several data tables, and explains the theory behind it.

    In the attached Excel worksheet I’ve prepared a list of the fields we need to create an analysis-ready sales file in Sage. Click here to download it now.

    1. Contents of the analysis-ready sales file
    The first part of the list contains 23 essential fields; the second part (24-33) contains fields that might be useful to you later on and are worth investigating. This tutorial is concerned only with the essential fields 1-23.

    Looking at the top, the first field we want to extract is called Invoice Number, and we are going to take it from the Invoice_Item data table. In Excel the full name Invoice_Number would make the column too wide, so we are going to call it INVNO instead.

    Fields 16 and 17 (Total Cost and Total Margin) aren’t stored in Sage at all; we will have to create them as calculated fields in MS Query.

    Finally, at the bottom are the Filter options and Sort orders we want to set. We’ll only include invoices dated on or after 1 January 2006, and we will also exclude any zero value fields that contain comments, delivery instructions etc.

    We will sort the invoices into reverse order, so that as new invoices are created, they appear at the top of the screen in Excel.

    Don’t worry too much about the list right now. Any problems should resolve themselves as you work through the tutorial.

    Finally, a point about nomenclature. I call this an “analysis-ready file”, whereas in the BI (Business Intelligence) world it would be called a “datamart”. They are both the same thing – a subset of the data which can be used immediately to create reports.

    2. Live link to Excel via Import External Data
    Now start up Excel. Open a blank workbook. Click onto cell A1.

    Select: Data–Import External Data–New Database Query
    [Excel 2000 or earlier: Get External Data–Create Database Query].

    In the Choose Data Source box, you see a list of data sources. You can either run this off the Sage Demo Company or off your own company data. Choose whichever you want. If you use the Demo company, username = MANAGER, password = blank.

    You now see the Query Wizard, Choose Columns box. Inside it are the Sage data tables, ACCRUAL, AUDIT_HEADER and so on.

    3. Start with the lowest level table
    The analysis-ready file derives from four tables – Invoice, Invoice_Item, Sales_Ledger and Stock. We’ll start with the lowest level table and work our way up.

    The lowest level is Invoice_Item table, which holds the item lines of each invoice, so scroll down until you get to INVOICE_ITEM then click on the + to open it. Move the following fields across to the right hand column.

    Invoice Number
    Item Number
    Stock Code
    Description
    Unit of Sale
    Unit Price
    Quantity
    Full Net Amount
    Discount Amount
    Net Amount

    Click Next. The Query Wizard–Filter Data screen appears. We don’t want to apply any filters at this stage, so click Next.

    4. Sort and Finish
    The Query Wizard–Sort Order box appears. From the drop down select INVOIC£_NUMBER (first one). Change the sort order from Ascending to Descending. Next.

    Note: Descending sort order means is that every time a new invoice is posted into Sage, it will appear at the top of the Daybook in Excel.

    The Query Wizard–Finish box appears. At top left it defaults to Return Data to Microsoft Excel. Change this to View Data or Edit Query in Microsoft Query. Then Finish.

    You see the Microsoft Query screen. Maximise the screen.

    Notice that in column A, Invoice_Number shows the latest invoice numbers first.

    Click anywhere onto the data. Then go to the bottom of the file (Ctrl-Down Arrow).

    The number of records is at the bottom left. My version (the demo company Stationery and Computer Mart) says 603 records. Make a note of this number.

    Go back to the top of the file (Ctrl-Up Arrow).

    5. Add the INVOICE table
    The Invoice_Item table is at top left.

    From main menu, select Table–Add Tables. From the list, select INVOICE. Then Close.

    You now have to make a join between these two tables, based on a common field. The common field between Invoice_Item and Invoice is INVOICE_NUMBER. Therefore:

    Scroll down each table and display the common field INVOICE_NUMBER.

    Then drag and drop INVOICE_NUMBER from one table onto INVOICE_NUMBER in the other table. Afterwards you should see a black line between the two showing the join.

    6. After every join, check the number of records
    It all seems simple enough, doesn’t it? However, you have no idea how the programmers have put together this database. It is so easy both to lose and to duplicate records when you make joins. So play safe – be paranoid.

    WHENEVER YOU MAKE A JOIN, IT IS ESSENTIAL TO CHECK THAT YOU STILL HAVE THE SAME NUMBER OF RECORDS.

    This is why we started with the lowest level records, in this case the invoice item lines. We counted the records – 603. As we make joins to a new table, we must keep checking that it stays at 603 (or whatever number you have).

    Click on the left exclamation mark icon at the top to run the Query (or just F9 key).

    Now click onto the data in the bottom half of the screen - Ctrl-Down Arrow to bottom of file.

    It should still say 603.

    7. Add the SALES_LEDGER table
    Now add the SALES_LEDGER table.

    Join Invoices and Sales_Ledger via the common field ACCOUNT_REF.

    Exclamation mark icon (F9) to run the Query.

    Check the number of records. It’s still 603.

    8. Add the STOCK table
    Now add the STOCK table.

    Join Invoice_Item and Stock via the common field STOCK CODE

    Exclamation mark to run the query.

    Check the number of records. On my version it’s gone down to 594.

    9. Problem – S3 records are lost
    We have a problem with the Stock table join. On my version all the records with S3 as the Stock Code disappeared.

    Presumably this is because S3 is not actually a Stock Item, and so when you try to join it to the Stock Table, there’s no record to link to and it disappears.

    Double click onto the black line linking the Invoice_Item and Stock tables. This displays the Joins box.

    There are 3 “Join includes” options and Query defaults to the first. Logically, the second option ought to work, but when I click on it and Add, I get the message “Can’t have outer joins if there are more than two tables in the Query”.

    Unless there is someone out there who knows how to get around this (if you do, please add a Comment to this article!), it looks like we are going to lose any S3 records from our reports.

    Possible alternative: It seems to be impossible to include S3 records in product sales reports because Sage requires a Stock Code. As an alternative, perhaps you could use non-stock items instead of S3. Go into the Products menu-New. Then create a Stock Item called something like SERVICE, and change the Item Type field from the default Stock Item to Non Stock or Service item.]

    10. Add fields from the Invoice table
    We’ll now go into the Invoice table and insert fields 2-5 of the Analysis-ready file printout.

    In the Invoice table, drag INVOICE OR CREDIT and drop it down to the right of the INVOICE_NUMBER column heading, just on top of ITEM_NUMBER but to the left.

    The INVOICE_OR_CREDIT column appears full of Invoice. (If it remains blank, click the left Exclamation mark icon at the top to run a query).

    Now insert INVOICE_DATE, ACCOUNT_REF and NAME.

    Check that, from the left, there are now 14 Field Names in the same order as the list in the Analysis-Ready file printout (i.e. Invoice_Number to Net_Amount).

    Now check again that you haven’t lost any records. Ctrl-Down Arrow – I’ve still got 594 records, so all I’ve lost are the S3 records. Ctrl-Up Arrow to return to top of file.

    11. Fields: 15-17. Add Cost and Margin as calculated fields
    Scroll to the far right of the columns. The last one is NET_AMOUNT.

    Sage doesn’t record the Total Cost or Total Margin (ie Gross Profit) value of the invoice line. We will have to add these ourselves as calculated fields.

    Sage holds two Cost Prices in the Stock record – Average Cost Price and Last Purchase Price. We’ll use average cost price. Go into the Stock table, find AVERAGE_COST_PRICE and double click on it. It appears next to NET_AMOUNT.

    We’ll now add the first calculated field. Basically, you just type the formula into the column heading, so:

    Click onto the empty column heading to the right of AVERAGE_COST_PRICE.

    Type in: Quantity*Average_Cost_Price (ie quantity multiplied by cost price).

    Click the exclamation mark to run the query. You now see the total cost value of the line.

    If you get the error message “Cannot find column”, it means you have mis-typed the formula. Double click on the column heading to see exactly what you typed in, then correct it.

    Now for total margin. In the next empty column heading, type: Net_Amount-Quantity*Average_Cost_Price

    Run the Query.

    You now see the total margin for each line. (Check it is correct by looking at a line with quantity greater than 1).

    Each transaction line now has all the details we need.

    12. Fields 18-24. Add the analysis dimension fields
    In order to be able to summarise your reports, it is necessary to bring over analysis dimension fields like Stock Category, Customer Analysis 1, 2 and 3, and so on. These should go the right of the invoice details.

    From the Stock table, select STOCK_CAT and STOCK_CAT_NAME (just highlight and double click).

    From the Sales_Ledger table, select ANALYSIS 1, ANALYSIS 2, ANALYSIS 3.

    Finally, from Invoice_Item, select NOMINAL_CODE (you will need this to reconcile back to Sales in the P&L)

    F9 to run the Query.

    Again, to be absolutely sure, check that you haven’t lost any more records.

    13. Change column headings
    In the column headings, most of the Sage field names make the column too wide. As a rule of thumb, except for description fields, I always try to make a column heading 8 digits max.

    Look at the “Column Heading” column in the Analysis-ready file printout. The Column Heading we want for Invoice_Number is INVNO

    Back in Query, double click on the column heading of the first field, Invoice_Number.

    The column is highlighted and the Edit Column box appears.

    The second Column Heading box is empty. If you leave it empty, the Field name at the top will be used as the column heading. We’ll insert our own column headings instead.

    In the second box, type: INVNO then click OK. INVNO is the new heading.

    Now amend ALL the column headings from INVNO to NMCD.

    F9 or exclamation icon.

    After you’ve done them all, scroll left until you see the INVNO column.

    14. Put in a date filter
    On your own system there may be several years’ worth of data. You may only want to report this year’s sales, plus maybe 2006 for last year comparisons.

    We’ll put in a filter to bring in only records from January 1st 2006 onward (feel free to change this if you wish).

    We’ll filtering on INVDATE. So click anywhere in the INVDATE column.

    From main menu, select Criteria–Add Criteria.

    In the Field box, INVOICE:INVOICE_DATE should appear.

    In the Operator box, change the value to: is greater than or equal to

    Make the value: 2006-01-01 Add. Close

    A new box appears across the centre of the screen. At the left it says: Criteria field Invoice_Date Value #01/01/2006#

    Close the Add Criteria box. Click on the left exclamation icon.

    Query searches out and removes any orders dated after 1 January 2006.

    We’ll also remove any zero value fields which contain comments, delivery notes etc.

    Click on the TOT_LIST column (i.e total value at list price), then select the Criteria–Add Criteria menu option.

    Make the bottom two boxes to read: does not equal 0

    15. Save the Query, and display the data in Excel
    Now save the query. From the main menu, choose: File–Save As.

    Change the name of the file to: Query Sage sales analysis file (or similar)

    Now from main menu: File–Return Data to Microsoft Office Excel (at the bottom).

    The Import Data box says $A$1. OK.

    The data is imported. The columns from left to right should be:
    INVNO, INV_CR, INVDATE, CUSTNO, CUSTNAME,LN, PARTNO, PARTDESC, UOM, PRICE, QTY, TOT_LIST, DISC_ANT, TOT_NET, AVECP, TOT_COST, TOT_MGN, CAT, CATNAME, ANAL1, ANAL2, ANAL3, NMCD

    We have our analysis-ready sales file. In the next session we’ll use pivot tables to create some management reports.

    Now choose File-Save as: "Sage Sales Analysis" and Close.

    END OF SESSION

    Note: If you want to get a feel for what sort of analysis you can do with pivot tables, go to the Five-minute pivot table tips series The exercises are very simple, and use a very similar dataset to this one.

    Previous tutorials in the Sage & MSQuery series

  • Tutorial MSQuery and Sage #1: How to Set up Import External Data
  • Tutorial MSQuery and Sage #2: Import Balances into Excel
  • Tutorial: MSQuery and Sage #3: Set up your analysis-ready file
  • Tutorial: MSQuery and Sage #4: Add the missing pieces of data
  • Tutorial: MSQuery and Sage #5: Add nominal categories
  • Tutorial: MSQuery and Sage #6: Add customer and supplier names

Related material
David Carter's reporting tutorials and pivot table tips are archived in AccountingWEB's Management Reporting section. Key articles include:

Subscribe to the ExcelZone newswire
Subscribe to the ExcelZone NewswireTo ensure you don't miss any of David Carter's Excel reporting tutorials, click the button below to subscribe to the free fortnightly ExcelZone newswire. The subscribe function will take you back to the AccountingWEB home page after it adds your name to the subscription list.

Replies (4)

Please login or register to join the discussion.

avatar
By User deleted
19th Aug 2008 10:52

All about the correct JOIN's ...
Dong

Cannot recall whether L50 allows service invoices to be raised (i.e. without stock). If it does not allow service invoices then it should only be possible to generate an invoice containing stock items - ergo the query will never drop records because it will always find the relationship

The point is that to be on the safe side one wants all invoices to appear irrespective of whether they contain a stock item. It is no good having things disappear because one of the tables doesn't contain a related item.

This is all down to the correct JOINS - so really it is nothing to do with missing stock items because the query should be sufficiently robust to accomodate this

There is always the possibility that L50 allows stock items to be deleted after invoices have been generated - in which case L50 is at fault but then that should not really come as a surprise to anyone

Thanks (0)
avatar
By dunvegan
16th Aug 2008 00:53

Missing s3 records
Below are the s3 records and none are stock related items. Surely this is the "simple" reason why they disappeared when you added the stock table?

Consultancy Fee
Installation of six user network
Installation of computer system
Repairs
Network Installation

Thanks (0)
avatar
By User deleted
26th Sep 2007 17:01

General .....
One assumes that a Parent/Child relationship exists between Invoice and Invoice_Item.

With this in mind the JOIN should really start at the top level (Invoice) and cascade down; by adopting this approach one can perform LEFT, RIGHT (INNER/OUTER joins) and use a LEFT JOIN between Invoice and Invoice_Item (i.e. all Invoice records irrespective of whether there are Invoice_Item child records)

Explanation - in theory it should be possible to have an Invoice record without an Invoice_Item record - but not the other way round

The reason S3 records are being lost is probably because of the wrong starting point - Invoice_Item instead of Invoice; this in turn is causing the JOIN problem

Once again the reason for 'dropping' records is probably the wrong starting point

See the following example (only a few fields) with all 4 tables joined

SELECT INVOICE.INVOICE_DATE, INVOICE.INVOICE_NUMBER, INVOICE_ITEM.NET_AMOUNT, INVOICE_ITEM.TAX_AMOUNT, STOCK.DESCRIPTION, SALES_LEDGER.NAME
FROM ((INVOICE LEFT JOIN INVOICE_ITEM ON INVOICE.INVOICE_NUMBER = INVOICE_ITEM.INVOICE_NUMBER) LEFT JOIN STOCK ON INVOICE_ITEM.STOCK_CODE = STOCK.STOCK_CODE) LEFT JOIN SALES_LEDGER ON INVOICE.ACCOUNT_REF = SALES_LEDGER.ACCOUNT_REF
ORDER BY INVOICE.INVOICE_DATE DESC;

Just copy & paste the above into Access - it should also work in other apps provided there is somewhere to enter SQL

Thanks (0)
By bro0010
11th Jul 2011 21:43

Workaround for missing S3 records

A generic way of working around the lack of being able to outer join when there are more than two tables is to do a union query.  The union adds a query including all the tables except the stock table with the additional criterion of INVOICE_ITEM.STOCK_CODE=S3.  This query would have to include dummy column entries for any stock table columns in the main query.  Unfortunately the Sage ODBC driver doesn't seem to support this construct. Plan B then is to have a completely separate querytable immediately below the first one where the combined output mimics what a single union query would have achieved.  Of course, now you have to refresh two querytables, not one. :(

Thanks (0)