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.
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
- - - - - - - - - - - - - - - - - - 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
-
- User
- Posts: 2
- Joined: 04 Nov 2015, 12:25
- Sage Version: v2015 21 UK/Europe/Africa
- brucedenney
- Site Admin
- Posts: 4619
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: customer sales per product category
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.
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.
-
- User
- Posts: 2
- Joined: 04 Nov 2015, 12:25
- Sage Version: v2015 21 UK/Europe/Africa
Re: customer sales per product category
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.
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.
- brucedenney
- Site Admin
- Posts: 4619
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: customer sales per product category
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
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.
Who is online
Users browsing this forum: No registered users and 19 guests