Inventory Report

[Former Member]

Hi Guys

I am looking into a report where i will display the cumulative qty of the stock in a single cell and then the actual warehouses that have the stock inside them,so i will for example have a value of 5 in the report but the allocation could be 2 in warehouse 1, 2 in warehouse 2 and 1 in warehouse 1.

How can i concatenate the warehouses so the report displays the 3 warehouses in one cell?

Or how can i get the specific item that i call up to display 3 rows(one per warehouse) with the actual qty in each warehouse?

Thanks

[Former Member · Accepted solution]

Is this something you need:

SELECT T0.ItemCode, T0.ItemName, T0.OnHand, T1.OnHand u2018Warehouse 1 On handu2019, T2.OnHand u2018Warehouse 2 On handu2019, T3.OnHand u2018Warehouse 3 On handu2019

FROM DBO.OITM T0

INNER JOIN DBO.OITW T1 ON T1.ITEMCODE = T0.ITEMCODE AND T1.WHSCODE = 'WHS1'

INNER JOIN DBO.OITW T2 ON T2.ITEMCODE = T0.ITEMCODE AND T2.WHSCODE = 'WHS2'

INNER JOIN DBO.OITW T3 ON T3.ITEMCODE = T1.ITEMCODE AND T3.WHSCODE = 'WHS3'

Thanks,

Gordon

[Former Member · Accepted solution]

Hi Gordon

The problem is that i have in excess of 50 warehouses and cannot do that for each warehouse, i need to only reflect the specific warehouses that have stock and depict those, so the different items would have different locations.

i would then have to do over 50 inner joins.

[Former Member · Accepted solution]

That would be simple then:

SELECT T0.ItemCode, T0.ItemName, T1.OnHand, T2.WhsName

FROM DBO.OITM T0

INNER JOIN DBO.OITW T1 ON T1.ITEMCODE = T0.ITEMCODE AND T1.OnHand > 0

INNER JOIN DBO.OWHS T2 ON T2.WhsCode = T1.WhsCode

WHERE T0.ItemCode = '[%0\]'