Tutorial: MS Query and Sage #3 - Set up your analysis-ready file, By David Carter
It’s now time to get serious. Once you have used Microsoft Query to set up your own analysis-ready file in Excel, you will be able to use the power of pivot tables to produce pretty well any report you want. David Carter shows how.
In the last tutorial we brought over Sage Line 50 nominal balances into Excel. But if you are serious about improving your reports, you really need to bring over transactions at the detailed level. You can then use the power of pivot tables to produce all sorts of sophisticated summary reports.
If you want to bring over transactions from an accounts package, a good place to start is the Audit Trail report, because this report contains most detail at the transaction level. Sage has an on-screen audit trail report in the Financials module (Modules-Financials). Go to it now.
1. The Sage Financials screen
This is a nice-looking screen, and you can easily export it into Excel (File-Send To Excel).
However, as I explained in a recent article, the people who write accounts packages are mainly technicians who understand very little about reporting. This seems to be a particular problem with Sage. So many of its reports have something wrong with them when they get into Excel. Sometimes I think Sage does it deliberately.
For example, in this report Sage decided to make all amounts positive. So an invoice and a credit note for £100 apiece are both exported into Excel as 100.00 and all report totals are false. Another defect affects dates. They come over into Excel as text fields rather than proper dates, so you can’t group them to calculate monthly totals.
And essential fields are omitted too. So the screen shows code numbers for customers, suppliers, nominal accounts and departments, but without any accompanying descriptions these will be meaningless to most users. And while there are nominal account codes with which we can produce a trial balance, there are no nominal categories for producing a profit and loss report. And so on, and so on.
2. Create an “analysis-ready” file
In this tutorial we will take the on-screen audit trail as the basis, but correct the dates and numbers, make sure that code numbers always have a description, and add the grouping codes. At the end we will have constructed an “analysis-ready” file which can be used for producing reports.
It will take a little time, but the rewards will be great. For whenever a new transaction is entered into Sage, it will automatically be added to the analysis-ready file and every report in Excel will be dynamically updated.
3. Open the Demo company Stationery and Computer Mart UK
We’ll run the tutorial on the Sage’s demonstration company – Stationery and Computer Mart UK. This holds only a few thousand records and shouldn’t slow your machine down.
Having seen how it’s done, you can then set up an analysis-ready file on your own company data.
Remember that in order to do this exercise you must previously have set up MS Query and the Sage ODBC drivers as explained in the first article of the series. The second article introduced you to the basics of Query, and you should have worked through that too.
Industry insightsView more
Start up Excel. Open a new workbook. Click onto cell A1.
From main menu choose (Excel 2003): Data–Import External Data–New Database Query
(Excel 2000 or Excel 97): Data–Get External Data–Create Database Query
The Choose Data Source box appears. [If it doesn’t, you need first to set up Query and Sage ODBC. Go through article one of this series].
Sage Demo Company is in the list of data sources. Highlight it and click OK.
In the Sage ODBC Connect screen, the UserID = MANAGER, Password – leave blank. OK.
The Query Wizard–Choose Columns dialogue box appears, with ACCRUAL, AUDIT HEADER etc visible. In the list of tables at the left, scroll down until you get to AUDIT JOURNAL. Click the + sign next to it.
4. The AUDIT_JOURNAL table
The 16 fields below are the ones I want to put into my analysis ready file. Twelve are contained in this AUDIT_JOURNAL table. However four of them (Period, AcctName, Nominal Name, Nominal Category) are stored in other tables.
..PERIOD [stored in the PERIOD table]
..ACCTNAME [stored in the PURCHASE_LEDGER table]
..NOMINAL NAME [stored in the NOMINAL_LEDGER table]
..NOMINAL CATEGORY [stored in the CATEGORIES table]
We will select the 12 now and add the other 4 later. Select the 12 fields by double clicking on them. They move into the right-hand box.
Make sure that all 12 fields are sorted into the same order as I’ve listed them above. (To move a field in the list, highlight it, then click on the up or down arrows next to the box).
Click Next. The Query Wizard-Filter Data screen appears. We don’t want to apply any filters at this stage, so click Next.
5. Apply SORT
The Query Wizard-Sort Order box appears. From the drop down select TRAN_NUMBER. Change the sort order from Ascending to Descending. Next. (Descending sort order means is that every time a new transaction is posted into Sage, it will appear at the top of the transaction list in Excel.)
The Query Wizard–Finish box appears. At top left it defaults to Return Data to Microsoft Excel. We want to do this now, so select Finish.
The Import Data box asks where we want to place the data. It should say: Existing worksheet = $A$1. Click OK.
A list of transactions appears, with columns TRAN_NUMBER, TYPE, DATE, DATE_ENTERED, ACCOUNTREF, DETAILS, NOMINALCODE, AMOUNT, DEPT_NUMBER, DEPT_NAME,INV_REF, EXTRA_REF.
It is very similar to the list on the Financials screen. Make sure that there are 12 columns and you have selected all the fields.
One early piece of good news: the Amounts in column H are both positive and negative.
6. Linking to other tables with “Joins”
We need 4 fields which were not held in the AUDIT_JOURNAL table. They are:
PERIOD [stored in the PERIOD table]
ACCTNAME [stored in the PURCHASE_LEDGER table]
NOMINAL NAME [stored in the NOMINAL_LEDGER table]
NOMINAL CATEGORY [stored in the CATEGORIES table]
We’ll start with the NOMINAL NAME field. Right click to display the options menu and select Edit Query, which takes you into the Query wizard. Hit Next three times to get into the Finish box. Now choose View Data or Edit Query in Microsoft Query. Finish.
You see the Query screen with the data. Maximise the screen. Notice that in column A the Tran Number shows the latest transaction numbers first.
We are now going to make some joins. A big danger when you make joins is that you start to lose records and don’t notice it. We need to keep track of the number of records.
Click anywhere onto the data. Then go to the bottom of the file (Ctrl-Down Arrow). Make a note of the number of records at bottom left. My version says 3254 records.
Go back to the top of the file (Ctrl-Up Arrow).
7. Make a join to the NOMINAL_LEDGER table
The AUDIT_JOURNAL table is at top left. From main menu, select Table–Add Tables.
Find NOMINAL_LEDGER in the list. Highlight it, then double click to drop it down next to the AUDIT_JOURNAL table. The hourglass may run for a bit. Close.
In AUDIT_JOURNAL, scroll down to NOMINAL_CODE and highlight it.
With your left mouse button depressed, drag the mouse over the ACCOUNT_REF at the top of the NOMINAL_LEDGER table, then let go the mouse.
A line appears between NOMINAL_CODE and ACCOUNT_REF in the two tables, indicating that a join has been made.
In the NOMINAL_LEDGER table, scroll down to NAME.
Drag and drop NAME down to the right of the NOMINAL_CODE column heading. A blank column appears between NOMINAL_CODE and AMOUNT.
Now click the left exclamation mark icon at the top to run the query. The blank column fills with the names of the nominal accounts.
You must now check that you haven’t lost any records. Ctrl-Down Arrow to go the bottom of the file. There are still 3254 records. Good. Ctrl-Up Arrow to return to the top of the file.
8. Try to add the PURCHASE_LEDGER table
We’ve successfully added the Nominal Code description to the data. However, this is the only join that is going to work.
Let’s see what happens when we try to add the customer or supplier name to ACCOUNT_REF.
There is a difficulty here. Sage holds Customer names and details in one table (called SALES_LEDGER) and Supplier names and details in another (PURCHASE_LEDGER).
So any ACCTNAME field has to be populated from the SALES_LEDGER table if it is a sales invoice, and from the PURCHASE_LEDGER table if it is a purchase invoice.
Since it is (I think) more important to have the Supplier name than the Customer Name, we’ll try pulling down the PURCHASE_LEDGER table. Therefore:
Select: Table-Add Table). Scroll down the list of tables to find PURCHASE.LEDGER. Double click to select. It appears to the right of AUDIT.JOURNAL and NOMINAL.LEDGER. Close.
Before making the join, you need make more space in the top half of the screen. Click on the horizontal line at the top of the data and drag the data downwards.
Make a join between AUDIT_JOURNAL and PURCHASE_LEDGER. Use the ACCOUNTREF field in both tables.
Once you’ve made the join and there’s a line between the two tables, scroll down PURCHASE_LEDGER and find NAME. Drag and drop NAME to the right of the ACCOUNT_REF column heading.
A blank column is inserted. Click the left exclamation icon to run the query. The blank column is populated with names, so everything seems to have worked.
However, now use Ctrl-Down Arrow to check the number of records at the bottom of the file. On my version it says that there are now only 875 records in the database whereas it started with 3254.
Query has removed many transactions, for example all the journals. Although this join seemed to have worked successfully, it didn’t. You have to be very careful when making joins.
We will delete this new NAME column. Highlight the column by clicking on the NAME column heading, then press the DELETE key. Left Exclamation mark to run query.
Ctrl-Down Arrow again to go the end of the file. There are still only 875 records. We have to remove the PURCHASE_LEDGER table completely to restore all the transactions.
Click anywhere on the PURCHASE_LEDGER table. Then from the main menu chose Table – Remove Table.
Click the left Exclamation mark icon to run the query. Now check how many records there are.
9. No joins possible for the PERIOD and CATEGORY Tables
When preparing this tutorial I tried making joins for the PERIOD and CATEGORY tables. Neither worked. So don’t bother to try either of these yourself. For the record, PERIOD did seem to work, but the period numbers were wrong. CATEGORY just set the hour glass running for the next 20 minutes until I had to crash out with Ctrl-Alt Delete.
So, we’ve managed to insert only 1 of the 4 extra fields we need. Not so good, but there is still hope. For the time being, however, move on to the next section.
10. Filter out deleted records
Sage allows you to amend incorrect transactions. To maintain the audit trail, Sage creates a new (corrected) transaction, but it also keeps the original faulty transaction on file.
Sage flags this faulty transaction as Deleted and excludes it from any reports. We also need to exclude any deleted transactions. To do this, from main menu, select Criteria–Add Criteria.
In the Field box, scroll down the drop down list and select the AUDIT_JOURNAL.DELETED field.
In the Operator box, the value should be “equals”.
In the Values box type: 0 (zero).
Now click on Add.
A new box appears across the centre of the screen. At the left it says: Criteria field DELETED FLAG Value 0
Click on Close to close the box. Then click on the left exclamation icon.
Query searches out and removes any Deleted transactions (I don’t think there are any Deleted transactions in the Demonstration company, but there may be some in your own system. A Deleted record has value = 1, a live record has value = 0.)
11. Filter out balance sheet accounts
Personally, I like to get rid of balance sheet records. There is little point in storing thousands of Debtors Control and Creditors Control records and most of the reports I write relate to the P&L.
So from main menu, select Criteria – Add Criteria.
In the Field box, click on the drop down list and select AUDIT_JOURNAL.NOMINAL_CODE.
In the Operator box, change “equals” to “is greater than or equal to”.
Now click the Values button at bottom right. From the list of account codes, select 4000. OK. Now click on Add.
A new box appears across the centre of the screen. At the left it says: Criteria field NOMINAL_CODE, Value >= .
Close. Then click on the left exclamation icon to run the query.
An Enter Parameter Value box appears, suggesting 4000. Type in 4000. OK. The previous nominal codes disappear and now the list starts with 4000.
To see how many transactions have been removed, click on the data and Ctrl-Down Arrow. My version says there are now 1016 records.
12. Change the column headings.
Finally, we’ll tidy up the column headings. Double click on the column heading TRAN_NUMBER. The column is highlighted and the Edit Column box appears.
Click onto the second (empty) box – Column Heading.
Type TRAN and click OK. TRAN is the new heading, but the column may go blank. If it does, click the exclamation icon).
Now set the column headings as follows (some don't need to be changed):
TRAN_NUMBER = TRAN
DATE_ENTERED = DATENT
ACCOUNT_REF = ACCTNO
NOMINAL CODE = NOMCD
NAME = NOMNAME
DEPT_NUMBER = DEP
EXTRA_REF = XREF
Left exclamation icon to show all the data.
Now save the query. From main menu choose: File–Save As. Change the file name to: Query from Sage DemoCo analysis ready.
Now select File–Cancel and Return to MS Office Excel.
The column headings haven't changed. We will have to re-run the query, so close down Excel WITHOUT saving.
14. Re-start the Query
Start up Excel. Open a new workbook. Click onto cell A1. From main menu (Excel 2003): Data–Import External Data–Import Data.
(Excel 97 or 2000): Data–Get External Data–Run Database Query.
The Select Data Source box appears. In the list of My Data Sources, highlight Query from Sage DemoCo analysis ready. Open. OK.
Import Data box – OK. Type in 4000 Click the “use this value/reference for future refreshes” box.
The data appears. It’s tidier with the new column headings, but we are still missing those 3 extra fields. But even though we can’t get at them in Query, we can have a second bite at the cherry and try getting hold of them in Excel. We will do this in the next article.
In the meantime, however, even without all the data fields it is still possible to see the potential benefits of “live linking” with ODBC and Query. We’ll finish by setting up a pivot table in Excel. Then we’ll go into the Sage Demonstration company, enter a purchase invoice, and see what happens.
15. Set up a pivot table by department
Firstly, find the tab name at bottom left (it’s probably Sheet1). Double click on it, and rename this worksheet MASTER.
Now click anywhere on the data. From the main menu select: Data – Pivot Table and PivotChart report.
The Pivot Table Wizard, Step 1 of 3, screen appears. Click on Next
Step 2 of 3 The Range box should say: $A$1:$M$1017 or similar. Click on Next.
Step 3 of 3: Click on Layout
The COLUMN-ROW-DATA box appears. To the right are the column headings of the spreadsheet
Drag and drop these fields from the list.
DEPT_NAME into the COLUMN area
NOMCD into the ROW area
NOMNAME into the ROW area below NOMCD
AMOUNT into the DATA area
Now click on OK, then Finish
The Pivot Table is created in a new worksheet.
Excel has automatically created subtotals for NOMCD, which make the screen difficult to read. To remove them:
Place your mouse pointer over the grey field button “NOMCD” in cell A4.
Double left click the mouse. The “Pivot Table Field” box appears.
In “Subtotals” at the left, change from Automatic to None. OK The subtotals are removed.
Finally, tidy up the numbers. Format them all to two decimal places, and make negative numbers red.
This pivot table is handy if you analyse costs by department. The first column contains items that haven’t been given a department code. You can check if this is correct.
16. Enter a new purchase invoice into Sage
Looking at this pivot table, cell E11 contains the value of Materials Purchased (Nominal code 5000) by the Installations department. Right now it is blank, i.e. zero.
We are going to enter a purchase invoice for £10,000 into Sage and analyse it to the Materials Purchased account, and the Installations department.
Minimise Excel. Then start up Sage. Go into the demo company Stationery and Computer Mart. (File – Open – Open Demo Data – MANAGER)
From main menu: Modules – Suppliers – Invoice. Choose any supplier, make the reference fields anything you like, N/C = 5000, Dep = 6, Details = “my new invoice”, Net = 10000, VAT=1750. Save.
17. See the new invoice in Excel
Now switch back into Excel. Go into the MASTER worksheet.
Nothing has changed yet. Right click for menu. At the bottom select: Refresh Data. OK.
Your new invoice is added at the top of the screen.
Now go into the pivot table worksheet. Nothing has changed.
Right click on the data for menu. Refresh Data. The 10000 now appears in cell E11.
Double click on E11. Excel generates a drill down sheet showing you details of the new invoice.
That’s OK for now. Close down, and save the Excel file as: analysisready.xls.
END OF SESSION
Previous tutorials in the Sage & MSQuery series
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
To 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.