Exporting Invoices to Excel

[doinktheclown]

Hi

I'm just wondering if it is possible to do the following. I am looking to export a selection of invoices for a certain customer in order to put together a list of the items they have taken over the last year, and the quantities and costs.

Is this possible, or is there a better way to do this?

Thanks in advance

[brucedenney]

Well...

The way I would do it, is to create a linked Excel spreadsheet that pulls all the invoice details out of the invoicing module and makes a spreadsheet out of them, then use a pivot table, to flex that data.

It is one of those things I could do in half an hour, but if you don't know odbc,excel,pivot tables, then you could spend days on on a very interesting and useful learning curve.

[rjb878]

Quite easy to do using the ODBC connection and Excel, shouldn't take long at all.

1. Make sure SageLine50v** (dependant on version number) ODBC driver has correct path configured for data files in ODBC Data Sources panel
2. In Excel, go to Data > Import > From Other Sources > MS Query, select "SageLine50v**" under Databases
3. Under available tables select INVOICE_ITEM, pull the following fields : Invoice Number, Stock Code, Description (if needed), Quantity, Foreign Unit Price (if you use multiple currencies!)
4. Repeat step 3 again on sheet 2, this select INVOICE table, select INVOICE_NUMBER, ACCOUNT_REF
5. Use VLOOKUP formula to add Account Ref to the first sheet using the data on the second sheet

The above only takes a few minutes, then you can easily filter purchases by customer (or any other data you decide to import) and analyse the data using Excel formulas from there.

[brucedenney]

I would add both tables to the one query and join them in the query, then you do not need the vlookup.

I would also set the path and version using VBA as then when you upgrade you version of Sage or move the data it is very easy to maintain.

You say a certain customer, so you need to filter to just one customer. The filtering of the customer could be done in the query, as the query when run against all your sage data could return a very large data set this would reduce the amount of data returned and make Excel happier. You could do this as a parameter on the query and you could set that in a cell in the spreadsheet.

I am pretty sure you would also want to have a date range in there, again done in the query as parameters and set from cells on a sheet.

The other issue is that this doesn't give you the cost.

There are 2 types of costs you could use, the individual cost on the specific transaction from the Goods Out in the stock transaction table or one of the general "Cost" prices from the stock record. While in there you might also want to add in other things such as stock categories. The tables may need to be linked in as outers, because some products eg S1 or any deleted products, do not exist in the stock table, indeed, you need to think about how you want to handle the cost prices in these cases, or do you want to exclude them altogether?

Again the cost price could be linked in the query rather than having multiple spreadsheets and vlookups, keeping the load in Excel down.

You probably are interested in the margin so I would add that in on the right of the returned data, this formula should them copy down automatically.

You could then summarise the information using a pivot table, you could then flex the data a number of ways, compare profitability of different customers/products etc.

As always, the devil is in the detail.