It's easy with an Excel-based report-writer I don't think you are going to get this information out of standard Sage. On the other hand, if you link Sage to an Excel-based report-writer which can read Sage datafiles, it's a piece of cake.
I'm an IT consultant and for reporting out of Sage we use a product called BVXL from Blue Valleys ( www.blue-valleys.com ). Most of the data you need for sales and margin analysis is contained in the Sage sales invoice item file. However, from memory I think this file doesn't hold the product cost, so we'd have to pull the product cost in directly from the Stock record, but it's very easy to do with BVXL.
Again from memory, I think the Sage Stock record only stores a Weighted Average Cost rather than a Standard Cost, so you may find the same product having different cost prices and messing up your margin figures. If that's a problem we can set up a user-defined file in BVXL which holds Standard Costs.
BVXL is pretty cheap (£600 ) and you'd have to pay for a day or two's consultancy, but your client would get all the sales and profitability analysis he wants - all of it in nice and easy-to-use Excel!
If you want to talk further, send me an email at davidDOTcarterATmooseDOTcoDOTuk Regards David Carter
Me. I specialise in reporting with Excel and have written numerous pivot table tutorials for this site. I do quite a lot of work in advanced use of pivot tables; if your people know them already I doubt if they will learn much from a standard course.
Anyway, I find that training courses with customers often turn out to be half consultancy because they really want help on how to handle the particular layout of their own data.
If you want to discuss, send me an email at davidDOTcarterATmooseDOTcoDOTuk Regards David Carter
Yes It's a good quality database, pretty accurate so far. It gives phone number, name of senior person etc. My cold calling technique is pretty crappy but you can't blame that on the database.
I've spent some time segmenting it by company size (number of employees) and type of business (ISIC code).
The only problem for me has been the time spent in trying to handle 1.4 million records. In the end I managed to pull out all the businesses in each county (about 35000 records apiece) and saved them as individual Excel files. Then started the segmenting exercise.
Don't get a UK- or US- written package The banking system seems to be quite different in Europe. I don't know Belgium in particular, but they don't seem to have cheques in Europe. The paying company instructs the bank to pay the payee (bit like BACS or a Giro Credit). So when you talk about Bank Reconciliations they've no idea what you're talking about.
So I would go for a package written in Europe. There's a very fine Dutch accounts package I worked with a few years back called AccountView. They have representatives in the UK you could talk to, I think.
the old Copy and Paste did it Thanks for the suggestions, guys. I have a 512mb machine, so I suppose a 1.4gb database is bound to be a problem. Every suggestion including downloading SQL Express ended up with the PC grinding to a halt due to lack of memory.
In the end I managed to sort the database by County. Then slowly, ever so slowly, I managed to highlight about 25,000 records a time and copied and pasted them into separate Excel files. Took most of the evening but I only wanted 3 counties plus London. Thanks again, and maybe I ought to spend that £30 on 4gig of RAM (I can't believe this - now in the old days........). David
PS Looking at the Yell site, I notice that they now break up the UK directory into 3 separate CSV files, so maybe other people have been complaining. Great value though.
I’ve been engaged on just this project for a client of mine over the last 6 months. They’ve had Navision for 2 years and found the reporting side very difficult – at this company they call it “No Vision” not Navision !!
They had already bought Jet so we got it working. Jet is OK, but I’d class it as a fairly techie product (originally developed for the IT department at IKEA, apparently).
The problem with Navision is that the data is spread all over the place in numerous data tables. Jet links you to these tables but you still need to know the structure of the underlying database if you are going to get any reports out of it. A determined accounts department can probably get Jet to work in the end but the Navision database is a stinker – lots of tables, lots of joins, etc.
At this company the Sales department needed reports as well and they didn’t have a hope in hell of getting Jet to work, so for them I tried a product called BVXL from Blue Valleys
BVXL is far superior to Jet (IMHO) because it allows me to create data marts. I can poke around in the Navision database, find the fields I need, then pull them into a new data table (the “data mart”). The users run all their reports in Excel off the data mart – effectively it is an intermediate database which shields them from the raw Navision database.
Data marts are the way you tame these enormous transaction processing databases and make them accessible to non-IT experts. Here’s a link to the entry on data marts in Wikipedia http://en.wikipedia.org/wiki/Data_mart
I know my way around the Navision database pretty well now. If you want to discuss further, give me a call on my email : davidDOTcarterATmoose.co.uk
Good luck!
David Carter (ex-Consultant IT Editor, AccountingWEB)
To my knowledge Sage 50 does not have a Customer Special Prices facility. This feature is not usually available in entry-level software. You will need to upgrade to a mid-range package such as Sage 200, Iris Enterprise, etc.
My answers
Here's some possibles
Several possibilities:
1) DBSage from Pendragon Systems
http://pendragonsystems.com/products.htm
2) BVXL from Blue Valleys
http://www.blue-valleys.com/
3) Optegra Financials
Don't know the website; email Jeremy Robbins:
[email protected]
Of the three, BVXL will certainly do the job. I use it myself and it's excellent.
Never heard of anyone linking Crystal to Sage; maybe a bit of a sledgehammer to crack a nut.
David
It's easy with an Excel-based report-writer
I don't think you are going to get this information out of standard Sage. On the other hand, if you link Sage to an Excel-based report-writer which can read Sage datafiles, it's a piece of cake.
I'm an IT consultant and for reporting out of Sage we use a product called BVXL from Blue Valleys ( www.blue-valleys.com ). Most of the data you need for sales and margin analysis is contained in the Sage sales invoice item file. However, from memory I think this file doesn't hold the product cost, so we'd have to pull the product cost in directly from the Stock record, but it's very easy to do with BVXL.
Again from memory, I think the Sage Stock record only stores a Weighted Average Cost rather than a Standard Cost, so you may find the same product having different cost prices and messing up your margin figures. If that's a problem we can set up a user-defined file in BVXL which holds Standard Costs.
BVXL is pretty cheap (£600 ) and you'd have to pay for a day or two's consultancy, but your client would get all the sales and profitability analysis he wants - all of it in nice and easy-to-use Excel!
If you want to talk further, send me an email at davidDOTcarterATmooseDOTcoDOTuk Regards David Carter
Me. I specialise in reporting with Excel and have written numerous pivot table tutorials for this site. I do quite a lot of work in advanced use of pivot tables; if your people know them already I doubt if they will learn much from a standard course.
Anyway, I find that training courses with customers often turn out to be half consultancy because they really want help on how to handle the particular layout of their own data.
If you want to discuss, send me an email at davidDOTcarterATmooseDOTcoDOTuk Regards David Carter
Yes
It's a good quality database, pretty accurate so far. It gives phone number, name of senior person etc. My cold calling technique is pretty crappy but you can't blame that on the database.
I've spent some time segmenting it by company size (number of employees) and type of business (ISIC code).
The only problem for me has been the time spent in trying to handle 1.4 million records. In the end I managed to pull out all the businesses in each county (about 35000 records apiece) and saved them as individual Excel files. Then started the segmenting exercise.
But at £84 or whatever it's a gift, Buy it.
Don't get a UK- or US- written package
The banking system seems to be quite different in Europe. I don't know Belgium in particular, but they don't seem to have cheques in Europe. The paying company instructs the bank to pay the payee (bit like BACS or a Giro Credit). So when you talk about Bank Reconciliations they've no idea what you're talking about.
So I would go for a package written in Europe. There's a very fine Dutch accounts package I worked with a few years back called AccountView. They have representatives in the UK you could talk to, I think.
Excel does it automatically
As Nilesh says, you don't have to update this list. Excel looks at everything in the column and makes the list from that.
Just add the extra items to your spreadsheet; they will be added to the list automatically.
Tell client to Save in Excel 2003 format
Tell your client to set his copy of Excel to save workbooks in Excel 2003 format.
Instructions in this link:
Excel 2007 tip #1: Make your data visible to other Excel users by saving in Excel 97-2003 format. By David Carter
https://www.accountingweb.co.uk/cgi-bin/item.cgi?id=169364&d=1032&h=1033&f=1026&dateformat=%25o%20%25B%20%25Y
the old Copy and Paste did it
Thanks for the suggestions, guys. I have a 512mb machine, so I suppose a 1.4gb database is bound to be a problem. Every suggestion including downloading SQL Express ended up with the PC grinding to a halt due to lack of memory.
In the end I managed to sort the database by County. Then slowly, ever so slowly, I managed to highlight about 25,000 records a time and copied and pasted them into separate Excel files. Took most of the evening but I only wanted 3 counties plus London. Thanks again, and maybe I ought to spend that £30 on 4gig of RAM (I can't believe this - now in the old days........). David
PS Looking at the Yell site, I notice that they now break up the UK directory into 3 separate CSV files, so maybe other people have been complaining. Great value though.
BVXL will give you data marts
Hi A.V.
I’ve been engaged on just this project for a client of mine over the last 6 months. They’ve had Navision for 2 years and found the reporting side very difficult – at this company they call it “No Vision” not Navision !!
They had already bought Jet so we got it working. Jet is OK, but I’d class it as a fairly techie product (originally developed for the IT department at IKEA, apparently).
The problem with Navision is that the data is spread all over the place in numerous data tables. Jet links you to these tables but you still need to know the structure of the underlying database if you are going to get any reports out of it. A determined accounts department can probably get Jet to work in the end but the Navision database is a stinker – lots of tables, lots of joins, etc.
At this company the Sales department needed reports as well and they didn’t have a hope in hell of getting Jet to work, so for them I tried a product called BVXL from Blue Valleys
BVXL is far superior to Jet (IMHO) because it allows me to create data marts. I can poke around in the Navision database, find the fields I need, then pull them into a new data table (the “data mart”). The users run all their reports in Excel off the data mart – effectively it is an intermediate database which shields them from the raw Navision database.
Data marts are the way you tame these enormous transaction processing databases and make them accessible to non-IT experts. Here’s a link to the entry on data marts in Wikipedia http://en.wikipedia.org/wiki/Data_mart
I know my way around the Navision database pretty well now. If you want to discuss further, give me a call on my email : davidDOTcarterATmoose.co.uk
Good luck!
David Carter (ex-Consultant IT Editor, AccountingWEB)
PS This article I did last year shows you how to import data from Navision into Excel via ODBC. But of course you still have to master the underlying database: https://www.accountingweb.co.uk/cgi-bin/item.cgi?id=173995
not possible?
I don't think this is possible.
To my knowledge Sage 50 does not have a Customer Special Prices facility. This feature is not usually available in entry-level software. You will need to upgrade to a mid-range package such as Sage 200, Iris Enterprise, etc.