I would like to insert a summary row in the bottom which displays sum of a partucular field in all the rows .I checked the check button in the report module for adding total but that does not work if a filter is applied . Could you guys help me with this ?
select
ste.production_order as "Production Order:Link/Production Order:120",
ste_item.item_code as "Item Code:Link/Item:120",
pro.qty as "Sch qty:Data:60",
pro.produced_qty as "Cut:Data:80",
ste.name as "Stock Entry:Data:80",
ste_item.transfer_qty as "Transfer Qty:Float:100",
ste_item.s_warehouse as "From Warehouse:Link/Warehouse:120",
ste_item.t_warehouse as "Target Warehouse:Link/Warehouse:120",
DATEDIFF(CURDATE(),posting_date) as "No of days"
from
`tabStock Entry` ste, `tabStock Entry Detail` ste_item, `tabProduction Order` pro
where
ifnull(ste.production_order, '') != '' and ste.name = ste_item.parent
and ste.production_order = pro.name and ste.docstatus = 1
and ste.purpose in ('Material Transfer', 'Manufacture')
By adding the below query I was able to achieve what I want ( this inserts a row in the bottom adding all the transferred qty ) but even this row disappears on filtering . So this is to do something with the .py file ? Any help guys ?
union
select 'sum', sum(transfer_qty)
from `tabStock Entry` ste, `tabStock Entry Detail` ste_item, `tabProduction Order` pro
where
ifnull(ste.production_order, '') != '' and ste.name = ste_item.parent
and ste.production_order = pro.name and ste.docstatus = 1
and ste.purpose in ('Material Transfer', 'Manufacture')
add_total_row() function in query_report.py file is responsible for adding the sum of a particular column in the end row. But the filter is applied on the frontend side, so you have to write function in js to add columns in the the end.