Share this content
0
453

Extracting Sage data by department using ODBC

Can the Query be customised to include data by department as well as nominal code?

I'm familiar with the ability of Excel to pull out data from Sage using the ODBC driver while running MS Query. Typically I would look at debits and credits for each nominal code for each month. A new client has organsised their nominal structure to include departments. When I try to set up a Query to deal with this (I am not a database specialist) I receive an error message saying "the query wizard cannot continue because it could not join the tables in your query". Is it possible to link these tables manually, and if so then how? 

The end goal is a tab in excel that shows the movements in each nominal code for each month for each department. It would be simplest to have a tab for each department.

Replies

Please login or register to join the discussion.

avatar
13th Sep 2017 14:48

One route:

Pull in the Transaction number, Date, Nominal code, Details, Department number, Department name (if desired), and Amount from the Audit journal. This can be done straight from an ODBC query or via Power Query (Get and Transform in Excel 2016). The latter is much better if data cleansing, formatting, etc. is required.

Create a pivot table with Date, Nominal, Transaction number (to stop data being amalgamated), Details and Amount. Filter on department number with filter set to All.

Use a Timeline or slicer to filter your dates.

Under Pivot table options, use "Show Report Filter Pages" to split by department onto separate sheets.

It doesn't take as long as it sounds and each month you just need to refresh and change the date slicer.

Thanks (1)
avatar
By tobtim
to paulwakefield1
13th Sep 2017 15:12

Hi Paul,

I've thanked you but have still to try what you suggest. I must confess that the instructions seem in shorthand to me, for example I couldn't use the Power Query option without a wizard and I'm non-plussed about what detail to enter in the dialogue boxes.

The option of creating a pivot table sounds promising, I may need to source some local help in this regard.

I will update you with progress when/if made!

Cheers

Thanks (0)
avatar
to tobtim
13th Sep 2017 15:47

It's always difficult to know at what level to pitch it!
Probably now is not the moment to learn Power Query (but do investigate it at some point - it is powerful and generally very easy to use and, as it is non destructive, allows room for experimentation. Simon Hurst has a number of articles on the subject).

Your Query is going to be something along the lines of :

Connection string: Driver={Sage Line 50 v21};UID=manager;DIR=C:\[User]\Documents\Accounts\COMPANY.001\ACCDATA;

Commend text: SELECT AUDIT_JOURNAL.TRAN_NUMBER, AUDIT_JOURNAL.DATE, AUDIT_JOURNAL.NOMINAL_CODE, AUDIT_JOURNAL.DETAILS, AUDIT_JOURNAL.DEPT_NUMBER, AUDIT_JOURNAL.DEPT_NAME, AUDIT_JOURNAL.AMOUNT,
FROM AUDIT_JOURNAL AUDIT_JOURNAL
ORDER BY AUDIT_JOURNAL.TRAN_NUMBER

This should bring in a table of all your data.
Click in the table and, from the Insert ribbon, choose "Pivot Table". A Create Pivot table dialog will appear - Just "OK" this.

A new sheet will appear with a PivotTable Fields column on the right hand side. If it does not, make sure the "Field List" button in the "PivotTable Tools Analyze" tab is highlighted.

At this stage, i would suggest you go into the Design tab that is alongside the Analyze tab and change the "Report layout" to "Tabular form".

From the top part of the PivotTable Fields column, drag Date to the "Rows" section below it near the bottom of the column. Repeat for Nominal code, Details and Tran Number.
Drag "Amount" into the "Values" section.
Drag Dept Number into the Filters section.
If "Year" and "Qtrs" has appeared in the rows section, remove them by dragging them into the main area of the spreadsheet.
You will now have a rather messy Pivot table. Right click on the heading of the Date column and select "Ungroup".
Right click on Tran Number and Details headings and untick Subtotal.

Right click an item in the Amount column, select Number format and choose a suitable one. This will format the whole column. You could also double click the column header and give it a better name than "Sum of Amount".

We're getting there.

Lastly, click anywhere in the pivot table, go to the Insert tab and select Timeline. Date should be your only option. Tick and OK. You can now use this to select the month(s) you are interested in. If it is too long, on the right hand side of the Timeline that now appears, change months to years, reduce the number of years and then switch it back to Months.

You now have a pivot table with all transactions by Nominal code for the month.

To analyse it by department, click in the pivot table analyze tab and, using the drop down arrow next to Options on the left hand side, choose "Report filter pages". All being well this will create a new sheet for each department.

All done.

Thanks (3)
avatar
13th Oct 2017 17:05

There is nothing special about Power Query. In fact, MS has repacked Power Pivot- with some new functions. I personally like "Joins" and "Unpivot" features. Unless, you are going to do some heavy work, the standard functions are pretty straight ward. Click a menu (Get and Transform) and just follow the wizard as you do with other excel's standard menu.

Thanks (0)
Share this content