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

Report Designer - Show Qty & Unit £ In Customer Activity

Report design issues and solutions.
Post Reply
verity.cymru
User
User
Posts: 10
Joined: 17 Jun 2016, 09:27
Sage Version: v2014 20 UK/Europe/Africa

Report Designer - Show Qty & Unit £ In Customer Activity

Post by verity.cymru » 10 Jan 2017, 15:22

Hi

I'm trying to add to an existing report that prints out all Customer Activity for a set period. I've set a filter so that the SR Sales Receipts and Carriage do not show, but I'm having trouble getting the below information to show. Any ideas? I've tried adding Joins but just when I think I might have it the report hangs and I have to abandon the report preview.

I want to show;
Invoice Number
Invoice Date
Description
Qty of above Item on Invoice
Unit Price of line

Thanks

Verity

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

Re: Report Designer - Show Qty & Unit £ In Customer Activity

Post by brucedenney » 10 Jan 2017, 15:37

I think you will have some issues because you are trying to get information from the invoice items table that relates to information in the audit split table and there is no link between these tables.

What is the objective of the report?
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.

verity.cymru
User
User
Posts: 10
Joined: 17 Jun 2016, 09:27
Sage Version: v2014 20 UK/Europe/Africa

Re: Report Designer - Show Qty & Unit £ In Customer Activity

Post by verity.cymru » 11 Jan 2017, 09:42

It's to gain a quick snapshot of what has been sold historically and at what price. We sell more special order goods than we do stocked so it isn't feasible to set up product codes with price lists specific to that particular customer for everything they buy.

A scenario would be, customer calls for a mechanical seal they have little knowledge of but was supplied previously. We then have to scroll back through the customer's activity to find the goods. From there we can work backwards to reorder. If we knew a time frame they previously had the goods, or could filter by description, it would save a lot of time. Our Sage isn't compatible with the Excel we use so I can't even rely on that to use filters, conditional formatting etc.

Thanks

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

Re: Report Designer - Show Qty & Unit £ In Customer Activity

Post by brucedenney » 12 Jan 2017, 10:06

You could do it, just don't do it on the audit trail, do it as an invoice report.

Only the 64bit version of Excel is incompatible with Sage and all other 32 Applications, you can resolve the issue by uninstalling it and reinstalling the 32 bit version.

There is also a separate 64bit odbc driver available for V23 of Sage 50.

The issue you will face with the report is that it will not be particularly quick to run. Especially as time progresses and more and more items get added.

My solution for this would be to make a spreadsheet that lists all the items on all the invoices in an excel spreadsheet, you would update it daily as a scheduled task. To use it you would have an autofilter on the Account reference, enter the account reference and you will see everything they bought, the price they paid how many yada yada yada
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 12 guests