Query Report: Optional filter (mode_of_payment) causing KeyError

ERPNext
, ,
1

Hi all,

I’m building a Query Report in ERPNext (v15.67.0, Frappe v15.72.5).

Here’s my SQL:

SELECT
    si.customer_group AS `Customer Group`,
    sii.income_account AS `Income Account`,
    si.status AS `Payment Status`,
    pe.mode_of_payment AS `Mode of Payment`,
    SUM(si.grand_total) AS `Total Amount`
FROM `tabSales Invoice` si
LEFT JOIN `tabPayment Entry Reference` per
    ON per.reference_name = si.name
LEFT JOIN `tabSales Invoice Item` sii 
    ON sii.parent = si.name  
LEFT JOIN `tabPayment Entry` pe
    ON pe.name = per.parent
WHERE si.docstatus = 1
  AND si.posting_date BETWEEN %(from_date)s AND %(to_date)s
  AND (
        %(mode_of_payment)s IS NULL 
        OR %(mode_of_payment)s = '' 
        OR pe.mode_of_payment = %(mode_of_payment)s
      )
GROUP BY si.customer_group, sii.income_account, pe.mode_of_payment;

Problem

If I don’t select the mode_of_payment filter, I get this error:

KeyError: 'mode_of_payment'

because the filters JSON only includes from_date and to_date:

{"from_date":"2025-09-24","to_date":"2025-09-24"}

So Frappe fails when trying to substitute %(mode_of_payment)s.

What I tried

  • Wrapped condition in IS NULL OR = '' (shown above).
  • But since the key is missing entirely from the filters, it still crashes.

Question

How can I make mode_of_payment truly optional in a Query Report?

  • Do I need to always define it in the report JSON with a default ""?
  • Or is there a better way to handle optional filters in Query Reports without switching to Script Reports?
2

Interested