Sage Instant Accounts - Sales by product report ?

Discussion in 'Accounts & Finance' started by Spapro, Mar 25, 2014.

Thread Status:
Not open for further replies.
  1. Spapro

    Spapro UKBF Regular Free Member

    256 19
    Hi,

    we use Sage Instant Accounts 2012 and need help getting some product sales data out.

    We do not have sage cover and the excel integration thing has never really worked for us (even with some time on the phone with Sage support when we first installed the 2012 version).

    Basically I want to run a report which simply shows how many of each product we have sold for a given date range - say the last 12 months last financial year or whatever. Not sales by customer but a simple list that shows all our products and the number sold - ie. product A sold 10, product B sold 14, product C sold 35 etc, etc.

    Looking in products, then reports there are virtually no reports there.

    Can anyone with a good working knowledge of Sage possibly give some advice or provide a report file we can drop in somewhere to get the data we need ?

    Thanks in advance for any advice/help.
     
    Posted: Mar 25, 2014 By: Spapro Member since: Nov 21, 2009
    #1
  2. johndon68

    johndon68 UKBF Big Shot Full Member - Verified Business

    3,379 617
    I'm not sure if the report exists in Instant but look in Invoicing -> Reports -> Invoice Details and, if it exists, there will be a report there called 'Product Sales By Customer'. If it is not there, a report would need to be created...

    John
     
    Posted: Mar 25, 2014 By: johndon68 Member since: Nov 2, 2009
    #2
  3. bro0010

    bro0010 UKBF Contributor Free Member

    30 8
    We have a crude Excel file that allows you to connect through to sales invoice items and pull the data back into a QueryTable using the Sage ODBC driver. From your description I think the layout below might be the sort of report you were looking for.

    Inv Start Date: 01/01/2010
    Inv End Date: 31/12/2011

    STOCK_CODE DESCRIPTION Sum of QUANTITY UNIT_OF_SALE Sum of NET_AMOUNT
    BOARD001 Whiteboard - Drywipe (900 x 1200) 18 Each 332.94
    BOARD002 Whiteboard - Drywipe (1000 x 1500) 34 Each 665.72
    BOOKS001 A4 Ledger Book - 5 Column 87 Each 643.03
    BOOKS002 A4 Carbon Copy Book - Triplicate 126 Each 332.59


    If this would be of any interest to you please let me know and I'll happily send you the file.

    P.S. Sorry, the formatting has been lost from the table. Hopefully you'll still get the idea.
     
    Last edited: Mar 25, 2014
    Posted: Mar 25, 2014 By: bro0010 Member since: May 8, 2013
    #3
  4. Spapro

    Spapro UKBF Regular Free Member

    256 19
    Thanks John for responding.

    That report isn't there. In Invoicing -> Reports -> Invoice Details area we have:
    Invoices Summary
    Invoices Posted
    Invoices Not Posted

    Do you have a copy of the Product Sales by Customer Report file I can add in ?

    I would need to edit it so we don't get it by customer but one long list - just want a summary of everything sold to everyone (by product with a quantity).

    Thanks again John.
     
    Posted: Mar 26, 2014 By: Spapro Member since: Nov 21, 2009
    #4
  5. Spapro

    Spapro UKBF Regular Free Member

    256 19
    Thanks, that looks just the job - I will private mail you so you can email the file, much appreciated.
     
    Posted: Mar 26, 2014 By: Spapro Member since: Nov 21, 2009
    #5
  6. johndon68

    johndon68 UKBF Big Shot Full Member - Verified Business

    3,379 617
    Unfortunately, that won't work as each report has an internal, uneditable, flag that tells it whether it was created in Sage 50 or Sage Instant so a 50 report won't be recognised by Instant, the report would have to be created from scratch...

    John
     
    Posted: Mar 26, 2014 By: johndon68 Member since: Nov 2, 2009
    #6
  7. Spapro

    Spapro UKBF Regular Free Member

    256 19
    Think we have found a report so may be all sorted !

    I think the report we can use/export to excel is the 'Stock Sales' report which can be found in:
    Customer ->
    Invoice List ->
    Reports ->
    My Invoice Reports -> 'Stock Sales' report

    not sure if this is standard or something we added/created when messing around with Sage on the phone a year ago !
     
    Posted: Mar 26, 2014 By: Spapro Member since: Nov 21, 2009
    #7
  8. johndon68

    johndon68 UKBF Big Shot Full Member - Verified Business

    3,379 617
    As it is under 'My Invoice Reports' it is not a standard report, it has either been created from scratch or is an edited version of a standard report.

    John
     
    Posted: Mar 26, 2014 By: johndon68 Member since: Nov 2, 2009
    #8
  9. Spapro

    Spapro UKBF Regular Free Member

    256 19
    Run the Stock Sales report which looked great at first glance.

    Having looked closer the Quantity of each product is wrong (the 'Amount' field looks correct as an net total £ figure).

    Some of the lower volume sales parts look correct - ie sold 1 or 6 or whatever.

    One part we have sold we think 515 and it is showing 15 ???

    will pm bro0010 and try his excel file with ODBC Sage driver
     
    Posted: Mar 26, 2014 By: Spapro Member since: Nov 21, 2009
    #9
  10. johndon68

    johndon68 UKBF Big Shot Full Member - Verified Business

    3,379 617

    Impossible to say why without seeing the actual report layout.

    John
     
    Posted: Mar 26, 2014 By: johndon68 Member since: Nov 2, 2009
    #10
  11. Spapro

    Spapro UKBF Regular Free Member

    256 19
    A big thank you to bro0010 who sent me his excel report with ODBC Sage data links.

    We have actually sorted this in the the Sage report:
    Customer -> Invoice List -> Reports -> My Invoice Reports -> 'Stock Sales' report

    but we had to edit the report template to get it working (using the info gleaned from bro0010's excel report !)

    We had to amend the function on the 'Invoice_Item.quantity' field to Sum (it was set to None).

    This now works correctly with correct quantity totals shown.

    Thanks again to bro0010 for all his help - very much appreciated !
     
    Posted: Mar 27, 2014 By: Spapro Member since: Nov 21, 2009
    #11
  12. bro0010

    bro0010 UKBF Contributor Free Member

    30 8
    You're very welcome.

    If you ever feel the need / urge to explore your raw data in other areas you can use the same Excel file to do that too.

    Glad you got things sorted.

    Regards,
     
    Posted: Mar 27, 2014 By: bro0010 Member since: May 8, 2013
    #12
Thread Status:
Not open for further replies.