Example: How to add a Total row in Query Report of ERPNext

total_in_query_report.sql

select
  item.name as "ID:Link/Item:120", 
  item.item_name as "Item Name::120", 
  item_price.price_list_name as "Price List::80",
  item_price.ref_currency as "Currency::40", 
  item_price.ref_rate as "Rate:Currency:80",
  item.description as "Description::160",
  item.item_group as "Item Group:Link/Item Group:100",
  item.brand as "Brand::100"
from `tabItem` item, `tabItem Price` item_price
where
  item_price.parent = item.name
union
select 
  "" as "ID::120", 
  "Total" as "Item Name::120",
  "" as "Price List::80",
  "" as "Currency::40", 
  sum(item_price.ref_rate) as "Rate:Currency:80",
  "" as "Description::160",
  "" as "Item Group:Link/Item Group:100",
  "" as "Brand::100"
from `tabItem` item, `tabItem Price` item_price
where
  item_price.parent = item.name

resulting screenshot:

This is very good example, but when filtering by any field the total will not show up accordingly. seems short coming? How to show it up?