Skip to Content
0
Former Member
Oct 31, 2012 at 06:36 AM

Creating a Inventory report in SAP B1

55 Views

Dear All,

I want to create an Inventory Report with following columns. I tried the same by using OINM table

Item No. Item Name WareHouse April 2012 May 2012 June 2012 Opening Recd Issue Closing Opening Recd Issue Closing Opening Recd Issue Closing 1 ITEM 1 WH1 100 1500 1250 350 100 1500 1250 350 100 1500 1250 350 WH2 200 850 750 300 200 850 750 300 200 850 750 300 WH3 300 750 650 400 300 750 650 400 300 750 650 400 WH4 400 250 300 350 400 250 300 350 400 250 300 350 WH5 250 100 200 150 250 100 200 150 250 100 200 150 Total 1250 3450 3150 1550 1250 3450 3150 1550 1250 3450 3150 1550 2 ITEM 2 WH1 100 1500 1250 350 100 1500 1250 350 100 1500 1250 350 WH2 200 850 750 300 200 850 750 300 200 850 750 300 WH3 300 750 650 400 300 750 650 400 300 750 650 400 WH4 400 250 300 350 400 250 300 350 400 250 300 350 WH5 250 100 200 150 250 100 200 150 250 100 200 150 Total 1250 3450 3150 1550 1250 3450 3150 1550 1250 3450 3150 1550

I have used following query but I am unable to get the opening balance. Kindly help me to get the desired report.

SELECT

ItemCode,

Dscription,

Case

When month(docdate) = '1' then 'January'

When month(docdate) = '2' then 'February'

When month(docdate) = '3' then 'March'

When month(docdate) = '4' then 'April'

When month(docdate) = '5' then 'May'

When month(docdate) = '6' then 'June'

When month(docdate) = '7' then 'July'

When month(docdate) = '8' then 'August'

When month(docdate) = '9' then 'September'

When month(docdate) = '10' then 'October'

When month(docdate) = '11' then 'November'

When month(docdate) = '12' then 'December'

End as [Month],

Warehouse,

sum(InQty) as [Recd],

sum(OutQty) as [Issued],

(sum(InQty)-sum(OutQty)) as [Closing]

FROM OINM

WHERE DocDate between '20120401' and '20120630'

GROUP BY month(docdate),ItemCode, Dscription,Warehouse

order by Itemcode,MONTH(docdate),Warehouse

Appreciating your help in advance.

Thank you.

Vimal Rupera