on 07-15-2009 9:53 AM
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.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
User | Count |
---|---|
105 | |
9 | |
7 | |
6 | |
5 | |
4 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.