SageForum for UK versions of Sage 50- Help and support.

After hours of searching and trying to edit existing reports/build one myself, I've decided to post for help.

I need a report, that I will export to excel, with customer sales per product category.

Although there are 999 categories in sage, we only use around 20 for reporting our product types, so I'm not sure what is the best way to filter them so I'm open to suggestions. Obviously, we don't want a report with all 999 categories so maybe something else could be used to identify the product types. We will happily edit the product records to suit.

Ideally, I'm hoping for a final table similar to the below:

Acc Ref, Name, Category 1, Category 2, Category 3, Category 4, Category 5, Category 6,
CUSTOME1, Name1, £10,203, £8,050, £5,566, £0, £4,500, £575,
CUSTOME2, Name2, £5,684, £6,783, £654, £245, £644, £4,336,
CUSTOME3, Name3, £0, £4,564, £4,643, £4,533, £6,489, £455,
CUSTOME4, Name4, £629, £548, £365, £3,566, £0, £260,

Any help would be appreciated.

If you have a recent version of Excel, you could do it all from Excel getting the data via ODBC, it is not that difficult to do when you know how, but it needs a bit of pivot table work and some ODBC.

Essentially you dump all the invoice data into a big table in excel through ODBC then build a pivot table off that which lets you slice and dice the data.

DataSheet
==========
(Invoice), %Date%, CustAcct, Name, (ProductCode),(Qty),(Unit Price)ProductCat, LineTotal

The stuff in brackets is just extra things you may want later, the Date is something you didn't mention, but I suspect is a criteria you want

Pivot Table
==========
Acc ref on Left, Category on Top, Sum Of LineTotal is the main Data and You probably want the Date as a filter on top.

VBA
====
I would do a bit of VBA to update the ODBC so as to make life easier when you change versions and to get rid of the dialogue popups

-----

It might be easier to pay for half an hour of my time to do it for you, and another half hour to explain it if you want to fully understand how it is done and how you can reuse it to do other things.

Thanks for your reply.

I just found a tutorial video online for anybody else that wants to have a go at this (you tube title: Sage50 V2015 ODBC link to Microsoft Excel)

As for me, I'd like a quote for you to do it, so please send me a message.

As I said half an hour to do it and another half an hour to explain it all if you want to understand it.

I charge by the tenth of an hours at £6 per tenth of an hour ex VAT.

Fill in an account application and I will call you to sort it out today.

http://www.makingithappen.co.uk/apply.htm