Inventory History Report

[Former Member]

Hi SAP consultants,

I have a client that would like a report on inventory that shows the following information about their inventory. These are the required columns.

1. Opening balance as at a given date.

2. Total amount bought in the period.

3. Total amount sold in the period.

4. Closing balance at the end of the given period.

The report should display this information per item.

Kindly assist in generating this report.

Kind regards,

Andrew.

[Former Member · Accepted solution]

Hi!

Try this

Declare @FromDate Datetime
Declare @ToDate Datetime
Declare @Group nvarchar(10)
 
Set @FromDate = (Select min(S0.Docdate) from OINM S0 where S0.Docdate >='[%0]')
Set @ToDate = (Select max(S1.Docdate) from OINM s1 where S1.Docdate <='[%1]')
 Set @Group = (Select Max(s2.ItmsGrpCod) from OITB S2 Where S2.ItmsGrpNam = '[%2]')

Select 
a.Itemcode,
a.Dscription,
sum(a.[Opening Balance]) as [Opening Balance],
sum(a.[IN]) as [IN],
Sum(a.OUT) as OUT,
((sum(a.[Opening Balance]) + sum(a.[IN])) - Sum(a.OUT)) as Closing from(
 
Select
N1.Itemcode,
N1.Dscription,
(sum(N1.inqty)-sum(n1.outqty)) as [Opening Balance],
0 as [IN],
0 as OUT
 
From OINM N1
Where
N1.DocDate < @FromDate
Group By
N1.ItemCode,N1.Dscription
 
Union All
select
N1.Itemcode,
N1.Dscription,
0 as [Opening Balance],
sum(N1.inqty) as [IN],
0 as OUT
 
From OINM N1
Where
N1.DocDate >= @FromDate and N1.DocDate <= @ToDate and
N1.Inqty >0
Group By
N1.ItemCode,N1.Dscription
 
Union All
select
N1.Itemcode,
N1.Dscription,
0 as [Opening Balance],
0 as [IN],
sum(N1.outqty) as OUT
 
From OINM N1
Where
N1.DocDate >= @FromDate and N1.DocDate <=@ToDate and
N1.OutQty > 0
Group By
N1.ItemCode,N1.Dscription)a, OITM I1
where
a.ItemCode=I1.ItemCode and
I1.ItmsGrpCod = @Group
 
Group By
a.Itemcode,a.Dscription

[Former Member · Accepted solution]

Hi Suraj,

Thank you. The report is as required.

Regards,

Andrew

[Former Member]

Try this one, it may be faster especially when more than one user need it:

Declare @FromDate Datetime
Declare @ToDate Datetime
Declare @Group nvarchar(10)
 
Set @FromDate = (Select min(S0.Docdate) from dbo.OINM S0 where S0.Docdate >='[%0]')
Set @ToDate = (Select max(S1.Docdate) from dbo.OINM s1 where S1.Docdate <='[%1]')
Set @Group = (Select Max(s2.ItmsGrpCod) from dbo.OITB S2 Where S2.ItmsGrpNam = '[%2]')
 
Select 
a.Itemcode,
max(a.Dscription),
sum(a.[Opening Balance]) as [Opening Balance],
sum(a.[IN]) as [IN],
sum(a.OUT) as OUT,
((sum(a.[Opening Balance]) + sum(a.[IN])) - Sum(a.OUT)) as Closing
from(
Select
N1.Itemcode,
N1.Dscription,
(sum(N1.inqty)-sum(n1.outqty)) as [Opening Balance],
0 as [IN],
0 as OUT

From dbo.OINM N1
Where
N1.DocDate < @FromDate
Group By
N1.ItemCode,N1.Dscription
 
Union All
select
N1.Itemcode,
N1.Dscription,
0 as [Opening Balance],
sum(N1.inqty) as [IN],
0 as OUT
 
From dbo.OINM N1
Where
N1.DocDate >= @FromDate and N1.DocDate <= @ToDate and
N1.Inqty >0
Group By
N1.ItemCode,N1.Dscription
 
Union All
select
N1.Itemcode,
N1.Dscription,
0 as [Opening Balance],
0 as [IN],
sum(N1.outqty) as OUT
 
From dbo.OINM N1
Where
N1.DocDate >= @FromDate and N1.DocDate <=@ToDate and
N1.OutQty > 0
Group By
N1.ItemCode,N1.Dscription) a, dbo.OITM I1
where
a.ItemCode=I1.ItemCode and
I1.ItmsGrpCod = @Group 
Group By
a.Itemcode
Having sum(a.[Opening Balance]) + sum(a.[IN]) + sum(a.OUT) > 0
Order By a.Itemcode

I have made sure only one line for one item in case you changed your item descriptions during the time frame. Also the all 0 items have been taken out.

Thanks,

Gordon

[Former Member]

Hi,

Thank you for your feedback. However, the client would like the report to go a step further, such that should he select dates for the start of the year and the end of the year, the report will break down the figures into the different periods/months. Therefore each month will have the four columns (Opening, In, Out and Closing).

Regards,

Andrew.

[Former Member]

That would not be possible by query. You may try XL Reporter.

[Former Member]

Hi!

Try this

Declare @FromDate Datetime
Declare @ToDate Datetime

Set @FromDate = (Select min(S0.Docdate) from OINM S0 where S0.Docdate >='[%0]')
Set @ToDate = (Select max(S1.Docdate) from OINM s1 where S1.Docdate <='[%1]')

Select 
a.Itemcode,
sum(a.[Opening Balance]) as [Opening Balance],
sum(a.[IN]) as [IN],
Sum(a.OUT) as OUT,
((sum(a.[Opening Balance]) + sum(a.[IN])) - Sum(a.OUT)) as Closing from(

Select
N1.Itemcode,
sum(N1.Transvalue) as [Opening Balance],
0 as [IN],
0 as OUT

From OINM N1
Where
N1.DocDate < @FromDate
Group By
N1.ItemCode

Union All
select
N1.Itemcode,
0 as [Opening Balance],
sum(N1.TransValue) as [IN],
0 as OUT

From OINM N1
Where
N1.DocDate >= @FromDate and N1.DocDate <= @ToDate and
N1.Inqty >0
Group By
N1.ItemCode

Union All
select
N1.Itemcode,
0 as [Opening Balance],
0 as [IN],
sum(N1.Transvalue) as OUT

From OINM N1
Where
N1.DocDate >= @FromDate and N1.DocDate <=@ToDate and
N1.OutQty > 0
Group By
N1.ItemCode)a

Group By
a.Itemcode

[Former Member]

Hi Suraj,

Thank you for your helpful reply. It is accurate but is it possible to adjust the report such that it shows the physical quantities rather than the values?

Regards,

Andrew.

[Former Member]

Hi!

Try this

Declare @FromDate Datetime
Declare @ToDate Datetime
 
Set @FromDate = (Select min(S0.Docdate) from OINM S0 where S0.Docdate >='[%0]')
Set @ToDate = (Select max(S1.Docdate) from OINM s1 where S1.Docdate <='[%1]')
 
Select 
a.Itemcode,
sum(a.[Opening Balance]) as [Opening Balance],
sum(a.[IN]) as [IN],
Sum(a.OUT) as OUT,
((sum(a.[Opening Balance]) + sum(a.[IN])) - Sum(a.OUT)) as Closing from(
 
Select
N1.Itemcode,
(sum(N1.inqty)-sum(n1.outqty)) as [Opening Balance],
0 as [IN],
0 as OUT
 
From OINM N1
Where
N1.DocDate < @FromDate
Group By
N1.ItemCode
 
Union All
select
N1.Itemcode,
0 as [Opening Balance],
sum(N1.inqty) as [IN],
0 as OUT
 
From OINM N1
Where
N1.DocDate >= @FromDate and N1.DocDate <= @ToDate and
N1.Inqty >0
Group By
N1.ItemCode
 
Union All
select
N1.Itemcode,
0 as [Opening Balance],
0 as [IN],
sum(N1.outqty) as OUT
 
From OINM N1
Where
N1.DocDate >= @FromDate and N1.DocDate <=@ToDate and
N1.OutQty > 0
Group By
N1.ItemCode)a
 
Group By
a.Itemcode

[Former Member]

Hi!

Please close the thread if issue solved !

[Former Member]

Hi Suraj,

Can the query be adjusted to display the item description as one of the columns. We would like to set a parameter such that the report is run per item group.

Kind regards,

Andrew