Help with Query Report on Stock Balance with Variant Attribute

ERPNext Reports
1

Hi all,
I’m setting up a query report for a client on ERPNext to show the inventory of items in their warehouse. The client wants something a lot more detailed than what already exists on ERPNext(Stock Balance Report and Stock Ledger Report) i.e. they want to be able to filter the items based on Item Variant Attributes in their warehouse.
So far, I created this query (the length, colour, width and thickness are all attributes for item variants)

SELECT 
	sletab.item_code as "Item Code::100",
	itemtab.item_name as "Item Name::100",
	sletab.warehouse as "Warehouse::100",
	SUM(sletab.actual_qty) as "Available Quantity::150",
	batchtab.batch_id as "Batch No::150",
	(SELECT attribute_value FROM `tabItem Variant Attribute` WHERE attribute = "Length" AND parent = 	
		sletab.item_code) as "Length::100",
	(SELECT attribute_value FROM `tabItem Variant Attribute` WHERE attribute = "Colour" AND parent = 	
		sletab.item_code) as "Colour::100",
	(SELECT attribute_value FROM `tabItem Variant Attribute` WHERE attribute = "Width (m)" AND parent = 	
		sletab.item_code) as "Width::100",
	(SELECT attribute_value FROM `tabItem Variant Attribute` WHERE attribute = "Thickness (mm)" AND parent = 	
		sletab.item_code) as "Thickness::100" 
	
FROM 
	(SELECT * FROM `tabStock Ledger Entry` ORDER BY creation DESC) as sletab
INNER JOIN
	`tabItem` as itemtab ON sletab.item_code = itemtab.item_code
INNER JOIN
	`tabItem Variant Attribute` as itemvarianttab ON sletab.item_code = itemvarianttab.parent
LEFT JOIN
	`tabBatch` as batchtab ON sletab.item_code = batchtab.item
GROUP BY 
	sletab.item_code, sletab.warehouse;

which produces this

stock_inventory2
stock_inventory21213×379 26.1 KB

So, like it’s shown above I ended up with an incorrect Balance and I can’t seem to find the solution to this and I would be grateful if someone has an alternative method to achieve my goal or point me in the right direction

Thanks

1 Like
2

Try to add it in the delivered script reports for stock balance & contribute it back as a modification or a new report.

3

Hi @Pawan
Thanks for the reply.
I would love to (and would also be easier to) create a script report but, some variant parameters there are not generic and I believe it might just be something small I am missing in the sql query.

Thanks again.

4

Done

https://github.com/frappe/erpnext/pull/11048

1 Like