Add filters to custom query report

Frappe Framework Customization
,
1

how i can add filters to custom query report?

2 Likes
2
  1. Create a file name with the report _name.js (e.g. sales_order_analytics.js)
  2. Add filter code as below in js file

frappe.query_reports[“Sales Order Analytics”] = {
“filters”: [

	{
		"fieldname":"from_date",
		"label": __("From Date"),
		"fieldtype": "Date",
		"width": "80",
		"default": frappe.datetime.month_start()
	},
	{
		"fieldname":"to_date",
		"label": __("To Date"),
		"fieldtype": "Date",
		"width": "80",
		"default": frappe.datetime.month_end()
	}
]

}

  1. Use in your query report
    select * from tabSales Order where date_time >= %(from_date)s and date_time <= %(to_date)s
2 Likes
3

thanks @jignesh_shah

i will try it now , i get idea of add filters but where i can create this file as your example under doctype/sales_order or under report file ?

4

@jignesh_shah
i find folder of my query report after i changed developer_mode=1 after that crate .js file as you explained above exactly and add filter start_date and use it in condition of my query
but i get this error
Traceback (most recent call last):
File “/home/frappe/frappe-bench/apps/frappe/frappe/app.py”, line 62, in application
response = frappe.handler.handle()
File “/home/frappe/frappe-bench/apps/frappe/frappe/handler.py”, line 22, in handle
data = execute_cmd(cmd)
File “/home/frappe/frappe-bench/apps/frappe/frappe/handler.py”, line 53, in execute_cmd
return frappe.call(method, **frappe.form_dict)
File “/home/frappe/frappe-bench/apps/frappe/frappe/init.py”, line 939, in call
return fn(*args, **newargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/desk/query_report.py”, line 90, in run
result = [list(t) for t in frappe.db.sql(report.query, filters)]
File “/home/frappe/frappe-bench/apps/frappe/frappe/database.py”, line 166, in sql
self._cursor.execute(query, values)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/cursors.py”, line 163, in execute
query = self.mogrify(query, args)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/cursors.py”, line 142, in mogrify
query = query % self._escape_args(args, conn)
KeyError: ‘start_date’

any help please?

5

share your filter file code and sql query

6

ok i will share it and explain solution of my issue after add some filters to be clear for all

regards

7

my solution as Mr @jignesh_shah explain her Add filters to custom query report - #2 by jignesh_shah

my problem was when i create new custom report i cant find its file location under reports folder when i change developer_mode:1 in site_config.json then created folder automatically when i created custom report .

9

Mr @jignesh_shah
I have a question i need show all data if no filter is selected , How i can do that?

thanks

10

Try something to ignore where condition, may be as below…

select * from tabSales Order where
date_time >= COALESCE(%(from_date)s, date_time)

11

that’s mean replace condition value with another value , i want ignore condition if no filter selected
Is that possible?

12

Hello @Akram_Mutaher
Where did you create the filter file js? Under which directory?
Regards
Bilal

13

erpnext/Module_Name/report/My_Report_Name/My_Report_Name.js

14

Thank you a lot.
If you can do it because it will help us in creating query report, can you please share your report_name.js?

Regards
Bilal

15

Do you try it with other filter type like data filed type like customer or order name

16

how do i create a report_name.js file ??

17

I’m trying to create a filter for the query report I built. When I go to the backend in the module/report, I don’t see the query report I built.

19

You have to set Standard Report as Yes

20

@jignesh_shah
I’am creating like that its good .
But when add another condition like link datatype from doctype has series name filter is not working .

21

Mr @Akram_Mutaher

Have you resolved this? I have exactly same issue and wondering if you can help?

thanks