Filter in Query Report

ERPNext Reports
1

I have created a simple query report using

select 
 `item_code` as "Code", sum(`qty`) as "Qty" , sum(`Schm`) as "Scheme" 
from  `tabSales Invoice Item`
group by  item_code

Report works fine.

Now I want to add a filter. I added a filter using

frappe.query_reports["Trade Offer"] = {
"filters": [
{
"fieldname":"item_code",
"label": __("Item"),
"fieldtype": "Link",
"options": "Item",
"default": frappe.defaults.get_user_default("item_code")
},
]
}

Filter is showing up but when I select an item, it does not filter the data in report? An help?

2 Likes
2

You must update your custom sql query after change the filter value.

3

How to update the write custom query with filters? Any example @demo_ashraful

4

Updated KB section 4

https://frappe.io/kb/reports/how-to-make-query-report

1 Like
5

Now filter is working fine whenever I choose a value in item_code. However when item_code filter is empty on first load then it gives error.

   Traceback (innermost last):
  File "/home/erpnext/frappe-bench/apps/frappe/frappe/app.py", line 49, in application
    response = frappe.handler.handle()
  File "/home/erpnext/frappe-bench/apps/frappe/frappe/handler.py", line 66, in handle
    execute_cmd(cmd)
  File "/home/erpnext/frappe-bench/apps/frappe/frappe/handler.py", line 89, in execute_cmd
    ret = frappe.call(method, **frappe.form_dict)
  File "/home/erpnext/frappe-bench/apps/frappe/frappe/__init__.py", line 531, in call
    return fn(*args, **newargs)
  File "/home/erpnext/frappe-bench/apps/frappe/frappe/widgets/query_report.py", line 81, in run
    result = [list(t) for t in frappe.db.sql(report.query, filters)]
  File "/home/erpnext/frappe-bench/apps/frappe/frappe/database.py", line 110, in sql
    self._cursor.execute(query, values)
  File "/home/erpnext/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 185, in execute
    for key, item in args.iteritems())
 KeyError: 'item_code'
6

You will have to add a client side validation to stop this.

Ideally setting "reqd": 1 in filter should do it :smile: . Maybe you can send a pull-request.

1 Like
7

Is this possible that I can run query based on a condition?

if item_code=null
  return all products
else
  return selected products?

Or do I have to write a script report for this??

8

Script report is always better. As a hack, you can add % if there is no value and convert the condition to like.

1 Like
9

Script report is always better. As a hack, you can add % if there is no value and convert the condition to like.