Need more, need help now?
- - - - - - - - - - - - - - - - - - pay-as-you-go support - no contract - tenth of an hour billing - expert help - fast service - no call queues
Need integration?
- - - - - - - - - - - - - - - - - - with your shipping system - website - invoicing system - crm - cms - manufacturing - order import - back to back orders..
Need a report?
- - - - - - - - - - - - - - - - - - Excel reporting that pulls data from Sage - custom layouts - layouts that change adapt to your brands and/or for drop shipping.
Want web hosting?
- - - - - - - - - - - - - - - - - - Your own domain name - email - a shop - wordpress - woo commerce - ticket systems - help desks - forums - portals

customer sales per product category

Report design issues and solutions.
Post Reply
MidlandImports
User
User
Posts: 2
Joined: 04 Nov 2015, 12:25
Sage Version: v2015 21 UK/Europe/Africa

customer sales per product category

Post by MidlandImports » 04 Nov 2015, 13:34

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.

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4619
Joined: 28 Feb 2006, 09:56
Sage Version: v28 UK/Europe/Africa

Re: customer sales per product category

Post by brucedenney » 04 Nov 2015, 17:30

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.
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.

MidlandImports
User
User
Posts: 2
Joined: 04 Nov 2015, 12:25
Sage Version: v2015 21 UK/Europe/Africa

Re: customer sales per product category

Post by MidlandImports » 05 Nov 2015, 10:46

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.

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4619
Joined: 28 Feb 2006, 09:56
Sage Version: v28 UK/Europe/Africa

Re: customer sales per product category

Post by brucedenney » 05 Nov 2015, 11:40

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
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.

Post Reply

Who is online

Users browsing this forum: No registered users and 19 guests