Skip to Content
avatar image
Former Member

inventory reports

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Former Member
    Jul 15, 2009 at 09:25 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded