Warehouse report in Item group wise

[Former Member]

Dear Experts,

In Warehouse report i need the breakup in item group wise.

Now Warehouse report show all item's and item's details. But now i need all group along with that group item details.

Ex:

Group 1

Item A - 50

Item B - 70

Item C - 120

.

.

.

Item N - 100

Group 2

Item AA - 50

Item BB - 70

Item CC - 120

.

.

.

Item NN - 100

Group 2

Item AA - 50

Item BB - 70

Item CC - 120

.

.

.

Item NN - 100

How to get this report. any solution

Thanks and Regards,

Chandru

[Former Member · Accepted solution]

Try the following query as you will not be able to manipulate or change the standard warehouse report to your requirements:

SELECT T2.[ItmsGrpNam], T0.[WhsCode], MAX(T0.[ItemCode]) AS [ItemCode], MAX(T1.[ItemName]) AS [Item Name], SUM(T0.[OnHand]) AS [OnHand] FROM OITW T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITB T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod GROUP BY T2.[ItmsGrpNam], T0.[WhsCode] ORDER BY T2.[ItmsGrpNam], T0.[WhsCode]

Kind regards

Peter

[Former Member · Accepted solution]

Hi Peter,

I need all item in that group. In your query only shows maximum item in that particular group. But i need all items in all groups.

Thanks and regards,

Chandru

[former_member583013 · Accepted solution]

Thiya,

SELECT T2.ItmsGrpNam, T0.ItemCode, SUM(T0.OnHand) [OnHand\]

FROM [dbo\].[OITW\] T0 INNER JOIN [dbo\].[OITM\] T1 ON T0.ItemCode = T1.ItemCode INNER JOIN [dbo\].[OITB\] T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod GROUP BY T2.ItmsGrpNam, T0.ItemCode ORDER BY T2.ItmsGrpNam, T0.ItemCode

[Former Member · Accepted solution]

Hai Suda,

I need a report like Inventory posting list. Group name below item details.

Thanks and regards,

Chandru

[Former Member · Accepted solution]

Hi Thiya,

In this case, you need to create an XL Report or a Crystal Report. Query can only produce a line report without anything that "below" the normal lines.

[former_member1190345]

Hi Thiya,

You may not be able to get the exact report as needed by you bu the clsest you will be getting would be thru the item list report form the Invetory Reports list where you will get both the Item Group and the Item no. with description and stock.

Hope this helps

Nagesh