I wanted to know the total qty issued for production in sales orders , so I created a query report like below , what the below report does is , it sums up all the qty field in all the production orders created so far and displays the result in the issued field .
select
`tabSales Order`.`name` as "Sales Order:Link/Sales Order:120",
`tabSales Order`.`customer` as "Customer:Link/Customer:120",
`tabSales Order Item`.item_code as "Item:Link/Item:120",
`tabSales Order Item`.item_name as "Des:Link/Item:120",
`tabSales Order Item`.qty as "Qty:Float:100",
`tabSales Order Item`.delivered_qty as "Delivered Qty:Float:100",
(`tabSales Order Item`.qty - ifnull(`tabSales Order Item`.delivered_qty, 0)) as "Qty to Deliver:Float:140",
`tabSales Order Item`.date as "Sch date:Date:100",
`tabItem`.thumbnail as "Cut wt:Data:60",
`tabProduct master`.grade as "Grade:Data:100",
`tabProduct master`.dieno as "Die no:Data:100",
`tabProduct master`.hammer as "Hammer:Data:50",
`tabProduct master`.rm_idl_cs as "Idl sec:Data:60",
`tabProduct master`.rm_alt_cs as "Alt sec:Data:60",
SUM(CASE WHEN `tabSales Order`.`name` = `tabProduction Order`.`sales_order` and `tabSales Order Item`.`item_code` = `tabProduction Order`.`production_item`
THEN `tabProduction Order`.`qty`
END) as "Issued:Int:80"
from
`tabSales Order`
JOIN `tabSales Order Item`
LEFT JOIN `tabItem` ON `tabItem`.`item_code` = `tabSales Order Item`.`item_code`
LEFT JOIN `tabProduct master` ON `tabProduct master`.`part_no` = `tabSales Order Item`.`item_code`
LEFT JOIN `tabProduction Order` ON (`tabSales Order Item`.`item_code` = `tabProduction Order`.`production_item` and `tabSales Order`.`name` = `tabProduction Order`.`sales_order`)
where
`tabSales Order Item`.`parent` = `tabSales Order`.`name`
and `tabSales Order`.docstatus = 1
and `tabSales Order`.status not in ("Stopped", "Closed")
and ifnull(`tabSales Order Item`.delivered_qty,0) < ifnull(`tabSales Order Item`.qty,0)
order by `tabSales Order`.transaction_date asc
Does the query report generate errors? If yes, can you post the traceback?
And I’m not completely sure if it’ll solve your problem, name in `tabSales Order`.`name` might not need the reverse single quote. I think `tabSales Order`.name will probably work.
@Pawan . I do have more than 1000 sales order and around 1200 production orders , but the above query displays only one row adding all the qty field in all the production orders ?
Please guide me .
@littlehera. No traceback . I tried your suggestion but does not work .
@Pawan . Thanks for continued support , now the I depreciated the where clause and checked the joints everything is fine , please help me .
select
`tabSales Order`.`name` as "Sales Order:Link/Sales Order:120",
`tabSales Order`.`customer` as "Customer:Link/Customer:120",
`tabSales Order Item`.item_code as "Item:Link/Item:120",
`tabSales Order Item`.item_name as "Des:Link/Item:120",
`tabSales Order Item`.qty as "Qty:Float:100",
`tabSales Order Item`.delivered_qty as "Delivered Qty:Float:100",
(`tabSales Order Item`.qty - ifnull(`tabSales Order Item`.delivered_qty, 0)) as "Qty to Deliver:Float:140",
`tabSales Order Item`.date as "Sch date:Date:100",
`tabItem`.thumbnail as "Cut wt:Data:60",
`tabProduct master`.grade as "Grade:Data:100",
`tabProduct master`.dieno as "Die no:Data:100",
`tabProduct master`.hammer as "Hammer:Data:50",
`tabProduct master`.rm_idl_cs as "Idl sec:Data:60",
`tabProduct master`.rm_alt_cs as "Alt sec:Data:60",
SUM(CASE WHEN `tabSales Order`.`name` = `tabProduction Order`.`sales_order` and `tabSales Order Item`.`item_code` = `tabProduction Order`.`production_item`
THEN `tabProduction Order`.`qty`
END) as "Issued:Int:80"
from
`tabSales Order`
LEFT JOIN `tabSales Order Item` ON ( `tabSales Order Item`.`parent` = `tabSales Order`.`name`
and `tabSales Order`.docstatus = 1 and `tabSales Order`.status not in ("Stopped", "Closed") and ifnull(`tabSales Order Item`.delivered_qty,0) < ifnull(`tabSales Order Item`.qty,0))
LEFT JOIN `tabItem` ON `tabItem`.`item_code` = `tabSales Order Item`.`item_code`
LEFT JOIN `tabProduct master` ON `tabProduct master`.`part_no` = `tabSales Order Item`.`item_code`
LEFT JOIN `tabProduction Order` ON (`tabSales Order Item`.`item_code` = `tabProduction Order`.`production_item` and `tabSales Order`.`name` = `tabProduction Order`.`sales_order`)
Can you try removing: SUM(CASE WHEN `tabSales Order`.`name` = `tabProduction Order`.`sales_order` and `tabSales Order Item`.`item_code` = `tabProduction Order`.`production_item` THEN `tabProduction Order`.`qty` END) as "Issued:Int:80"
just to check if the SUM function and it’s WHEN clause is causing the problem?
If not, maybe there’s something wrong with the JOIN clauses and their conditions that’s why its only returning 1 row.
@littlehera . Yes I did this already , this causes the problem I know very well .
SUM(CASE WHEN `tabSales Order`.`name`=`tabProduction Order`.`sales_order`and`tabSales Order Item`.`item_code`=`tabProduction Order`.`production_item`
THEN `tabProduction Order`.`qty`
END) as "Issued:Int:80"
but I want to know the sum of qty issued for production against a sales order , this is a very important report for me . How do I achieve this ?
@Muthu, what I usually do is, I create a separate python method which will compute for the sum so that the conditions in the sum query will not affect the rest of the SQL statement. But I also use Custom Reports for this one since it’s more flexible than Query reports.
You might need to restructure the SQL Statement or you might need to make a Custom Report to replace this one.
@littlehera Thanks for continued support . I do not have any clue with the python method , I only know SQL statements and bit of js , can you please help me when you get some time ?
