Query Report | Error When Filter Value Is Empty

Frappe Framework
1

trying to create a Query Report works well when I provide any value in the filter field but the same report doesn’t work when there is no or empty value. I applied a condition to check “null” but it didn’t help. Can anyone please guide me on how to resolve this?

SQL QUERY

SELECT 
     u.full_name
    ,u.username
FROM tabUser as u
WHERE ( 
    %(myfilter_name)s is null 
    OR u.full_name LIKE concat("%%",%(myfilter_name)s,"%%")
    )

QUERY REPORT:

image
image1034×646 23.9 KB

IT WORKS WHEN THE VALUE IN THE FILTER FIELD:

image
image1329×478 26.1 KB

IT DOESN’T WORK WHEN THE VALUE IS EMPTY

image
image1091×578 19.6 KB

2

Please check the comment: Adding Filters in Query Report - #2 by NCP

Reference: Adding Filters in Query Report - #4 by NCP

3

thank you @NCP for the reply, I followed the link you posted but unfortunately, it doesn’t work for empty values.

Based on your answer, I must mark “Mandatory” in the filter field. However, let’s assume I mark it as mandatory and reload the report page. It doesn’t show an error when filter field is empty which is good and achieved 50% of my goal, but the report does not show any data unless I enter some value in the filter field whereas I am expecting all the records whenever filter value is empty.

image
image1444×687 25.5 KB

I also tried to write a script to set the default value for the filter field which works for a default value but again it does not show any data when it is empty so this solution also doesn’t work.

Below is the script I wrote for the default value:

frappe.query_reports['Applicant Profile Report 02 By SQL Query']={
	"filters":[
		{ 	"fieldname":"filter_name",
			"label": __("My Filter Name"),
			"fieldtype": varchar(100),
			"width":"80",
			"default":"aa",
		},
	]
}

finally, the goal is not achieved yet, The Report should show all the records when filter value is empty.

4

that only possible using the custom server script report, that for, you have to develop in the custom app.

5

to achieve like or containing condition in the query report, I did a workaround although I am not sure if that is best practice or not but I cannot find a proper solution on this problem.

so I set % sign in the default value and the report result shows all the records on load however, again if I remove % sign the report gets empty.

below is the code to set a default value.

frappe.query_reports['Applicant Profile Report 02 By SQL Query']={
	"filters":[
		{ 	"fieldname":"filter_name",
			"label": __("My Filter Name"),
			"fieldtype": varchar(100),
			"width":"80",
			"default":"%",
		},
	]
}

I trust to achieve this I must go for the script report however, I hope for the best, if someone can suggest for query report.