Combining 2 Reports in Sage 50 Accounts

Hello...

I'm fairly new to creating reports in Sage 50 Accounts, and I have a question that hopefully someone more knowledgable might be able to help me with please...

I have 2 reports that work fine. I am trying to combine the 2 reports into one...

One report shows INVOICE_ITEM.DESCRIPTION and INVOICE_ITEM.QUANTITY(Sum), for Invoices from a given date (which is set by Criteria).

The other report shows STOCK.DESCRIPTION and STOCK.QUANTITY_LAST_STOCK_TAKE, for all items where the Quantity Of Last Stock Take does not equal zero.

Both reports group by STOCK.STOCK_CAT.

So ideally I would like the report to look like this:

Stock Category:

 Invoice/Stock Item Description:    Total Invoice Item Quantity:    Quantity Last Stock Take:

The problem I have is this: If I add STOCK.QUANTITY_LAST_STOCK_TAKE to the first report, it still only returns INVOICE_ITEM.DESCRIPTION's from the given Invoice Date range, whereas I want it to also include ANY other items that have a Stock Take Quantity, regardless of whether they fit the Invoice Date criteria... then group them all by STOCK.STOCK_CAT.

So to summarise, it needs to show:

Items that fit the Invoice Date criteria, OR:
Items that have a Quantity Last Stock Take <> 0, OR:
Both.

Thanks in advance for any help!

 

Running Sage 50 Accounts 2008 Version 14.0.12.153 on:

Windows XP 32 bit
Windows 7  32 bit
windows 7  64 bit

Comments

OR criteria

taxrebel | | Permalink

That logic is too complex for the criteria section.  You will need to define it as a filter, which means you can't use run time parameters.  You'll need to edit the report whenever the date range changes.

Duncan

Create the whole report from the STOCK file?

David Carter | | Permalink

(This may or may not be helpful.)

I assume you are trying to relate stockholding to previous sales in order to calculate the number of weeks' stock.

It would be easiest, I think, to ignore Invoice_Item.  Instead use the STOCK table and the QUANTITY_SOLD fields. It has 12 of them, from Month1 to Month12.

I don't know how easy it is to export the STOCK table into Excel.  If you're stuck, our product BVXL does this as standard.

David Carter (Blue Valleys Software)

 

 

Combining 2 Reports in Sage 50 Accounts

richiejames | | Permalink

Thanks Duncan... I was hoping there would be another way, but thanks for confirming my suspicions!

Combining 2 Reports in Sage 50 Accounts

richiejames | | Permalink

Thanks for your suggestions David... our report is a makeshift 'buying guide of required stock'. As we deal in perishables, our Invoices are often created before our goods have even been sourced. The purpose of the combined report would be to show what needs to be purchased for the following days deliveries by totalling the Invoice Items for that day, and also to show what stock we have left over from the daily stock take.

I'll fiddle around with the data exported to Sage, but we might end up just keeping 2 separate reports... just a few more sheets of paper to search through I suppose :)

Report stock or sales

tbayliss | | Permalink

This is impossible for the Sage reporting tool. You have a data query to extract sales between the two dates and a second query of products with a last stock take. But you then need to do something in SQL database jargon called a FULL OUTER JOIN on the product code. An external reporting tool querying the Sage data via ODBC is needed - Microsoft Access could do it or Crystal.

I did something similar for someone who wanted to look at total sales for the last 3 months compared with current stock levels and then calculating how many days worth of stock he had based on these recent sales.

-- Tony Bayliss, Senator System Consultants

Ideal world scenario - would this work?

David Carter | | Permalink

 RichieJames, do you  mind if I pick your brain a bit?   I have a couple of distributor customers and am writing stock re-order reports for them at the moment.

So far I’ve used Tony’s method – work out the Sales made over the last 2 months, then divide them into the Quantity in Stock to get the number of days’ stock.  But this isn’t going to work for you since you have to deliver next day. 

As I understand it, your system is as follows:  You make out the invoice as soon as you receive an order.  So at the end of the day you have a sheaf of invoices which represent the day’s orders.   You add up the total quantity of each product required.  Then you do a physical count of what you have in stock.  Then you deduct that from the quantity required to give you the quantity you need to order from each supplier.

REPORT REQUIRED

In an ideal world scenario for today 15th July,  I imagine that after order cut-off (4 p.m.?) you would output the following report from Sage into Excel.

Column A – Product Name

Column B – Total Quantity invoiced today 15th July (from INVOICE_ITEM)

Column C  - Supplier Name

Column D – Stocktake quantity as counted at 3 p.m. 15th July

Column E – Quantity Required (i.e. B-D)

You then generate one Excel worksheet for each supplier, and email it off to them as their purchase order for today.

Would this work for you, do you think?

David Carter (Blue Valleys Software)

Combining 2 Reports in Sage 50 Accounts

richiejames | | Permalink

Yes David that's about the size of it, although it's actually simpler... we don't need separate reports for different suppliers, as they're all based in one physical location at a wholesale market, our buyer just selects the most suitable supplier there and then. If I can manage to do this through Sage itself I'll stick with that for now, even if it does mean changing the date from within the designer each time... which reminds me...

Combining 2 Reports in Sage 50 Accounts

richiejames | | Permalink

I've tried shifting the Date Range selection from Criteria to Filter (just for a quick solution), as in for example "INVOICE_DATE = 20/07/2011"... but depending on which section I put the filter in, such as Detail / Header / whole report, it either returns no data, or still returns all invoice data regardless of the date.

Why would this not work, is there something I'm missing?

Syntax?

taxrebel | | Permalink

I think you need to:

Remove the invoice date and stock take criteria.

Add a filter to the report (not one section) using Data>Filters...

(INVOICE.INVOICE_DATE >= 01/06/11 AND INVOICE.INVOICE_DATE <= 30/06/11) OR STOCK.QUANTITY_LAST_STOCK_TAKE <> 0
You may need to check the date format - I can't recall how Sage wants it.

How do you get the stock count into Sage?

David Carter | | Permalink

 

Richiejames,

Thanks for this.  Apologies that I am not giving you any help on your original problem because I don't know the Report Designer, but can you answer one more question?

If you are doing a daily stock-count, how many products do you count and how long does it take each day to key the figures into Sage?

Presumably it takes quite a while each day to key the stock count into Sage.  (Or is there some way of entering them en masse, e.g via File - Import?)  

And, thinking aloud, is there any point in keying them into Sage at all, if the end result is going to be a report in Excel?  Why not record the stock balances in Excel in a worksheet, then import the invoice quantities into the worksheet from Sage, then subtract one from the other to get the order quantity?

But if you have some quick way of getting the stock count into Sage, doing it all in Sage will not be a problem.

Thanks in anticipation

David

 

Tom Hartopp's picture

Sales Order Book Review

Tom Hartopp | | Permalink

Richiejames,

I don't wish to overtly advertise but I have noted your thread and my company markets a 3rd party integrated solution that has its own sales order, purchase order and inventory control processes that might align to your situation. I'll avoid providing any lengthy explanation here in line with my initial comment but if you have any interest in looking at this I can provide you with a web link if you drop me an email - tom.hartopp at pclw.co.uk.

Add comment
Log in or register to post comments
Group: Sage 50 Accounts discussion group
An independent forum for Sage users to share ideas, tips and gripes