Overview stock total amount and units on itemgroup

[former_member208987]

I have made an xl reporter that generates overview of total stock in all warehouses sorted on itemsgroup with total amount per itemgroup and total amount of units. Now the result is different from the stock report in SAP but that is not sorted on itemgroup and not usefull for reporting.

Now i want to create an query which gives the right data out of SBO.

But how do i get the sum / total of the itemgroups

thx mark

[Former Member · Accepted solution]

You may start with this:

SELECT T1.ItmsGrpNam, T0.WhsCode, T0.ItemCode, T0.OnHand

FROM dbo.OITW T0

INNER JOIN dbo.OITB T1 On T1.ItmsGrpCod = T0.ItmsGrpCod

WHERE T0.OnHand > 0

UNION ALL

SELECT T1.ItmsGrpNam, "", "", SUM(T0.OnHand)

FROM dbo.OITW T0

INNER JOIN dbo.OITB T1 On T1.ItmsGrpCod = T0.ItmsGrpCod

WHERE T0.OnHand > 0

GROUP BY T1.ItmsGrpNam

Thanks,

Gordon

[former_member208987 · Accepted solution]

Hi Gordon,

When i execute the query i get an error.

Mark

[Former Member · Accepted solution]

Updated:

SELECT T2.ItmsGrpNam, T0.WhsCode, T0.ItemCode, T0.OnHand

FROM dbo.OITW T0

INNER JOIN dbo.OITM T1 On T1.ItemCode = T0.ItemCode

INNER JOIN dbo.OITB T2 On T2.ItmsGrpCod = T1.ItmsGrpCod

WHERE T0.OnHand > 0

UNION ALL

SELECT T2.ItmsGrpNam, NULL,NULL, SUM(T0.OnHand) Total

FROM dbo.OITW T0

INNER JOIN dbo.OITM T1 On T1.ItemCode = T0.ItemCode

INNER JOIN dbo.OITB T2 On T2.ItmsGrpCod = T1.ItmsGrpCod

WHERE T0.OnHand > 0

GROUP BY T2.ItmsGrpNam

Order BY T2.ItmsGrpNam, T0.WhsCode

[former_member208987 · Accepted solution]

Hi Gordon,

That's nice but is it possible to get the following layout of the query. Only 3 columns.

itemgroupname1 total quantity stock total stock in amounts (financial)

itemgroupname2 total quantity stock total stock in amounts (financial)

itemgroupname3 total quantity stock total stock in amounts (financial)

itemgroupname4 total quantity stock total stock in amounts (financial)

TOTAL QUANTITY STOCK TOTAL STOCK IN AMOUNTS

Thanks Again

Mark

[Former Member · Accepted solution]

Do you need any details at all, or only total for each item group?

[former_member208987 · Accepted solution]

No i don't need details, just the totals of the itemgroupsnames

greetings

Mark

[Former Member · Accepted solution]

Try this:

SELECT T1.ItmsGrpNam, SUM(T0.OnHand) 'Total On Hand', SUM(T0.OnHand*Case WHEN T0.AVGPRICE = 0 THEN T0.LastPurPrc ELSE T0.AVGPRICE END) 'Total Amount'

FROM dbo.OITM T0

INNER JOIN dbo.OITB T1 On T1.ItmsGrpCod = T0.ItmsGrpCod

WHERE T0.OnHand > 0

GROUP BY T1.ItmsGrpNam

[former_member208987 · Accepted solution]

thanks again gordon for the help

mark