List Items under the Item Group tree

ERPNext
,
1

Hello.

I is it possible to list Items under their respective Item Group in the Item Group tree?

2

I cannot think of a standard report that does this.

However, if you want to create your own Query Report, here’s some SQL to use. This shows all the Item Groups, sorted by their position in the tree, with the associated Items.


WITH RECURSIVE cteItemGroup ( group_id, group_name, parent_id, group_level, lft)
AS (
	SELECT
		name		AS group_id,
		name		AS group_name,
		parent		AS parent_id,
		1			AS group_level,
		lft
	FROM
		`tabItem Group`
	WHERE
		parent IS NULL
	AND is_group = 1
  
  	UNION ALL

	SELECT
		someGroup.name,
		someGroup.name,
		someGroup.parent,
		cteItemGroup.group_level + 1,
		someGroup.lft
	FROM
		`tabItem Group` 	AS someGroup
	INNER JOIN 
		cteItemGroup
	ON
		someGroup.parent = cteItemGroup.group_id
	)

SELECT
	 CONCAT(SPACE(group_level * 10), group_id)		AS ItemGroup_Indented
	,group_id										AS ItemGroup
	,group_level									AS ItemGroupLevel
	,tabItem.item_code								AS ItemCode
	,tabItem.item_name
FROM
	cteItemGroup
LEFT JOIN
	tabItem
ON
	tabItem.item_group = cteItemGroup.group_id
ORDER BY
	lft
3

Thanks a lot. I settled for report itself.