Empty Filter in a Query Report shows no data

ERPNext
1

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.

Regards,

2

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.

3

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.

Regards,

4

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

Regards,