Empty Filter in a Query Report shows no data

I am trying to write a query report in ERPNext and Im using FrappeCloud

I have written a condition WHERE cus.name = item.parent and cus.territory = %(territory)s

and have created the filter called territory in the report, but what happens is that the report will show zero results until I select a value in the filter.

I want to make it in a way that if no value has been selected in the filter, then it should show all the records.

I have tried alot of things but due to limited knowledge in coding and sql have reached to no solution. Kindly need help to resolve this.


WHERE cus.name = item.parent AND (cus.territory = %(territory)s OR cus.territory IS NULL)

This will return all records where cus.name equals item.parent and cus.territory is either equal to the selected value in the filter or NULL . If no value is selected in the filter, then the cus.territory IS NULL clause will be true for all records, and all records will be returned.

This still doesn’t work. the form stays on loading untill I enter a value in the filter.

Actually I have tried this clause multiple times in different ways. Seems like erpnext has blocked this in someway that a value must be entered in order to load records.


Can anyone with some experience on Frappe Cloud help me with this. Its a requirements which I am not able to achieve.