cancel
Showing results for 
Search instead for 
Did you mean: 

inventory reports

Former Member
0 Kudos

hi experts,

I would like to create an inventory report using Crystal Reports 2008 which looks exactly the same as Inventory Audit Report in SAP Business One 2007A

Report requirement:

List down all the items with its Item Code, Description, Quantity and Item Cost (easy right?)

The only condition:

I would like to filter my report by FromDate and ToDate.

For example:

Current date 15.07.2009

If I run the report by putting in 01.01.2009 to 31.01.2009, the result will show me:

item, with its respective info and most importantly the Quantity and Item Cost as at 31.01.2009 (similar to closing stock)

Any query for above statement?

Please help.

Thank you.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi,

Check the following query and modify it accordingly

SELECT N0.ItemCode,N0.Dscription,
SUM(N0.InQty-N0.OutQty), 
(SELECT SUM(N1.TransValue)/CASE 
WHEN SUM(N0.InQty-N0.OutQty) = 0 THEN 1
WHEN SUM(N0.InQty-N0.OutQty) <> 0 THEN 
SUM(N0.InQty-N0.OutQty)
END
  FROM OINM N1 where N1.DocDate <= N0.DocDate)
FROM OINM N0 WHERE N0.DocDate BETWEEN '20080101' and '20090717' 
GROUP BY 
N0.ItemCode,N0.Dscription,N0.DocDate

Hope it helps,

Vasu Natari.

Former Member
0 Kudos

hi vasu,

Thank you for answering my question, but i can't understand the query below and what is it for:

WHEN SUM(T0.InQty-T0.OutQty) = 0 THEN 1

WHEN SUM(T0.InQty-T0.OutQty) <> 0 THEN

SUM(T0.InQty-T0.OutQty)

I just want the report to list out all my items with ItemCode, Dscription, OnHand, ItemCost

as at my filtering date.

Please advice.

Former Member
0 Kudos

sorry, i understood already

Answers (0)