0

# STOK AUDIT REPORT

Mar 26 at 09:35 AM

38

I'm trying to do a stock audit for report for a product code to see the quantity sold in a certain time frame, but the report only gives me a total for that time frame, for example, 245 of "con256" between 22-3 to 24-3.

I would like a way of showing how many product sold per day in that time frame rather than the total sold over the entire time frame, I dont think the standard business one report can do this, so if the only way to create a query for this, but can I get it so show the breakdown per day. ie

22-3 con256 qty44

23-3 con256 qty 56

24-3 con256 qty 145

CHRIS FOWLER Mar 26 at 09:37 AM
0

Stock audit report I mean

Share
Johan Hakkesteegt Mar 27 at 06:54 AM
0

Hi Chris,

The Stock Audit Report shows exactly what you are describing, a separate entry for each quantity leaving (deliveries, etc.) and coming in (goods receipts, etc.).

In case you only get a single line for an item, as you described, then that item is not in any transactions during the given period. In other words, any and all transactions that led to the stock, as it is on 24-3, happened before 22-3.

22-3 to 24-3 is only 2 days, and 24-3 is a saturday. Please try selecting only this one item, and choose a bigger interval, say 1-3 to 31-3.

Regards,

Johan

Show 6 Share

Yeah I see what you mean, but the report doesnt break it down day by day which is what we want, so I guess its only able to be done via query

CHRIS FOWLER

The report will give you a line per transaction per item, within the given period. If you mean that you want to see the total quantity per item per day, within the given period, then yes you will need to write a query.

You may use this as a basis:

```select * from
(SELECT h.DocDate, r.ItemCode, SUM(r.Quantity) AS Quantity
FROM DLN1 r
INNER JOIN ODLN h ON r.DocEntry = h.DocEntry
GROUP BY r.ItemCode, h.DocDate) x
where x.DocDate = cast(floor(cast(GETDATE() as float)) as datetime)
order by 1, 2```

Regards,

Johan

Johan Hakkesteegt

thanks for your assistance, but this doesnt mean anything to me, can i paste this into and already saved query and run it?

CHRIS FOWLER

Sorry no, this is just a proof of concept if you will. You can replace an existing query with this code and run it as is, but it needs to be adapted a lot to give you what you need. For one thing it only shows Deliveries, where you will probably want to see all transactions that affect stock (stock transfers, goods receipts, etc). That means that a fully worked out query would mean this same query for each document type that affects stock, and some code around it to combine it all. Which is why I only provided this basic concept.

If you mean to use such a query only a couple of times a month or less, I recommend that you just export the Stock Audit Report to Excel, and use Excel's SUBTOTALS function to get the report you need.

If you need this report on a daily basis, I recommend that you ask your SAP Partner for help. You can even provide them the concept code above, to give them a starting point. They should have someone who knows SQL code.

Alternatively, one option is for you or a colleague or employee to learn SQL coding (its free and reasonably simple), and work on this concept and others yourself. Teach a man how to fish, and all.

And last but not least, if you need other tailor made reports like this one, or you need processes automated, I can put you in touch with a developer, who can build anything you need.

thats great, thanks so much for all your help.

its a simple small business, the query can just run a check on a certain product code that has been delivered over a time frame but just display the total quantity as a per day total rather than a combined total over the total time period.

my Sap partner is great but anything we ask is hours worth of work and its £150 per hour, so its hard to get little jobs done, we have spent around 5k making changes since our go live and the pot has run dry. lol

once again, many thanks for you assistance

CHRIS FOWLER

I certainly understand that, and in your case again I strong recommend that you learn SQL coding. Using the link I provided earlier, and the Query Generator tool found in the B1 client (Tools > Queries > Query Generator), I am confident you'll find it easier than expected to write at least a very simple query.

With the Query Generator, and some help from this forum, you will be able to create all those reports yourself. Additionally, the skill will allow you to start making more effective use of Formatted Searches. Once you've gotten the hang of it, your SAP Partner is going to start calling you why they haven't heard from you in such a long time ;-)

If you need custom addons, let me know. My guy only charges about 100€/h (85-90£), or I can put you on the road to building them yourself.