Sage to Excel ODBC Link

I have just set up an ODBC link from Sage Line 50v16 to excel. I would like to set up a template that allows me to drop the transactional TB's of each of my companies 20 or so departments into a spreadsheet. This will need to be done on a monthly basis, so i would like to be able to refresh the date range in the spreadsheet to pull through updated data at each month end. When i have tried to do it, the criteria box from sage pops up and i am required to enter the dates along with the department numbers. So rather than refreshing the data once, i would have to refresh the data on each individual TB even if all the TB's are on the same sheet.

Hopefully this makes sense when reading it (!) and if so is there a way to only refresh the date range without having to update the department numbers??

Comments

Excel ODBC

A2ZDATA | | Permalink

Hi There

Have you considered using Alchemex for this report - and many others.

It is an add-on product which interrogates your data and displays it in Excel.

You can contact the Alchemex Australian Office by emailing Chris Millar

chris@alchemex.com.au

Chris is a very helpful guy and will point you in the right direction.

There are also some wonderful FREE tips and tricks avilable on their website.

All the best

Judi Bond

A2Z Data Ltd

New Zealand

Thanks for your response Judi

davidpaulbarrow | | Permalink

Thanks for your response Judi.

It took a colleague and I quite a while to set up the ODBC link (!) so if possible we would like to explore this avenue a bit further before attempting a new method.  We'll have a bit of a play around to see if we can do what we need to do via the ODBC link, im also trying to tap up a few former colleagues and associates for their knowledge, and if we cant get what we need via this method will have a look into the Alchemex.

Thanks again

David

Christop51's picture

Excel ODBC

Christop51 | | Permalink

David

If I understand your problem correctly:

Having applied a departmental filter, the separate TB's can be 'refreshed' into your workbook onto DATA sheets with one click. Using 'Refresh all'.

Assuming all 12 periods are refreshed, a simple macro which refers to a variable (the relevant period column) can copy/paste the relevant (current) period data onto a REPORT sheet.

I can send you a sample workbook that will do this

chris@compaccsys.co.uk

 

 

Chris

 

Thanks Chris.

davidpaulbarrow | | Permalink

Thanks Chris.

 

Problem is as follows:

We currently manually input circa 20 departmental TB's into a master spreadsheet

Just as time consuming as inputting the data is running all the TB's for each department

I would like to have one sheet in excel which pulls in all data for each individul TB, i.e

 

Dept 1       Dept 2      
Nominal Code Description Dr Cr Nominal Code Description Dr Cr
               
               
               
               
               

Once in this format i will link it how i need it etc

The problem i am having is when i try to refresh the data it asks me to re enter the criteria, including the department number, so i would still have to re-run each TB. Ideally I would like to do this just once, so the department number is fixed and i just re-enter the date range.

Does that make sense??

Any help or advice you can offer would be greatly appreciated.

David

Christop51's picture

Sage to Excel ODBC Link

Christop51 | | Permalink

 

David Thanks for clarifying. The tables containing NOMINAL and TRANSACTION data cannot be linked using the department. A workaround which will save you some time would be to export each department's transactional TB to a CSV fileThen either manually (or preferably via a macro) paste the transactional TB's into named ranges on your workbook.Using vlookup the data can then be linked to your above format. If you need further help with this, please let me know. Chris

 

Wrong table

taxrebel | | Permalink

There is no need to get department data from the transactions.

Turn on advanced budgeting - department in depth.  Then you can get all you need from the financial_budget table.  (You will need to link to nominal for the account descriptions).

Financial_budget contains monthly totals by department, including previous years.  (Actual and Budget).  Because of the table layout (periods are row defined not column defined) you will probably want to retrieve it as a pivot table in Excel.

 

Thanks for this taxrebel but

davidpaulbarrow | | Permalink

Thanks for this taxrebel but this still seems a bit more difficult than what I had in mind. The task is something someone in my team does so i want to make it as easy as possible for them to refresh the link.

Out of interest, where within Sage Line 50 is the report you have mentioned?? Ill have a look and see what results it gives.

Thanks

Report?

taxrebel | | Permalink

There is no report.  You said ODBC.  You would have to create an ODBC query to retrieve the data.  (If you really want a report you could create one in Report Designer.)

Once the query is defined in Excel it is dead easy to refresh.  Just hit the refresh button.

 

But thats my issue. I want my

davidpaulbarrow | | Permalink

But thats my issue. I want my excel sheet to show each of my departmental TB's which i can do via the ODBC link but when i go to refresh the data, for example to run the next months management accounts, I have to refresh each individual TB rather than just once because the criteria box asks me to re-define the department numbers . Apologies if this doesnt make complete sense, im relatively new to this but im sure there must be a way to do what im trying to do!

Hi David

Optegra | | Permalink

Hi David

OptegraDP will do a departmental P&L report with each department in a column and a total at the end (in about 20 seconds and 2 clicks of a mouse.)

If you need the balance sheet too, we can add this to the report

I can show you remotely on your data if you like?

Thanks

Jeremy

Optegra Financial

 

 

 

patvanaalst's picture

My Way...

patvanaalst | | Permalink

...Which I'm sure could be refined more...  Sorry if I'm unclear or long-winded!!!

 

I have an ODBC table with the following fields:

NOMINAL_CODE

DEPT_NUMBER

AMOUNT

DATE, DELETED_FLAG

 

I then have a couple of calculated fields on the end, italics refer to a cell with that information in and the capitalised phrases refer to cells in that column:

YEAR    =IF(DATE>=Last Year End Date,"Current Year","Prior Year")

PERIOD    =MONTH(DATE)

YTD    =IF(PERIOD<=Current Reporting Period,"YTD","")

STATEMENT    =IF(NOMINAL_CODE*1>=4000,"P&L","BS")

 

This way the ODBC table doesn't need to be updated beyond when you open the file as it brings all of the data into the file.  I then have a single cell with the current reporting period that drives the financial reports themselves.  This also allows for reproducing prior reports if needed.  You'd need to use either conditional sums or SUMIFS to extract the data from the table into the way you'd like to present it.

I think the crux of it is that you will either need to have one table with all data, and 20 worksheets extracting that data, which you'd only need to refresh once, or 20 tables feeding directly into each department report which would need refreshing 20 times.  The other issue is that mine is 29k rows at the moment as I can't get periods directly out of Sage to summarise better (I must look at this advanced budgeting!).

You'll also need to consider the calculation time of having one table.  PM me if you want me to look at your layout.  I'm not an expert but like to play!

Also, you shouldn't have to enter each department number though?  I guess it's been set up as a user defined filter?  I use Sage 50 at home but Sage 200 at work, 200 uses [] around the entry to trigger a user response but I can't remember for 50 right now.

 

I hope this gives you some ideas at least, good luck

Pat

Pats way

davidpaulbarrow | | Permalink

Thanks Pat.

Just so im clear, when you run the ODBC link do you run it on the nominal activity?? Im currently trying to do it based on the Transactional TB, but have thought about running the nominal activity and the using a pivot table.

Thanks
David

 

patvanaalst's picture

Audit_Journal

patvanaalst | | Permalink

I actually use the AUDIT_JOURNAL table as it has all of the required fields in it.

 

Thanks

Pat

I think the problem is that

taxrebel | | Permalink

I think the problem is that the original poster davidpaulbarrow has confused everyone by using the term ODBC.  Re-reading his post he is not using an ODBC query, he is importing a transaction TB report.

 

In order to achieve an import of all the departments in one 'hit' you would have to modify the transaction TB report (ie create a new version) to split a selected range of departments into individual pages rather than aggregating them.  Even then I suspect using the data in Excel would be tricky.

 

davidpaulbarrow I recommend you read up on Excel, ODBC and msquery.  Perhaps follow some Web based tutorials.  It's a useful skill to have.  Grabbing the data directly out of the underlying database is often easier than importing Sage reports into Excel.  Then you can follow some of the advice above about which tables to get the data from.

 

 

Solution to ODBC issues surprising

Dlynch456 | | Permalink

Hi I have windows 7, 64 bit using Office 2013, with Sage v20.

It looks like Sage adopts the 64 bit ODBC, but Excel uses 32 bit: I spent ages trying to find solution (uninstalling Office + Sage, reinstalling, trying different 32 or 64 bit) - everything pretty hopeless: 

However: I opened Access and that found the correct ODBC and I could connect to the SageDB. Then Excel found the Sage ODBC also, and I was able to connect OK through the standard connection. What a pain. But in the end it worked. If anyone wants more details contact me.

Microsoft new web site and office download methods are totally horrible: you cannot get what you want at all in some cases, and only going through technet + other routes. Hopeless. 

Plus, the Sage web site and ability of that system/service to help is very, very low. Thei black box approach is not helpful, and the help desk says "ODBC queries are email response only" Ha. Hopeless.

 

Add comment
Log in or register to post comments
Group: Sage 50 Accounts discussion group
An independent forum for Sage users to share ideas, tips and gripes