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