ERPNext V14查询报表空过滤字段引发KeyError问题求助

问题场景 #

开发查询报表时，纯SQL查询在过滤字段填值时正常运行，但字段为空时触发「KeyError: 'account'」，需要实现过滤字段为空时自动忽略对应WHERE条件，且不将字段设为必填项。

推荐解决方案：Python脚本动态构建查询 #

相比纯SQL，用Script Report的Python脚本能更灵活处理动态条件，彻底避免KeyError问题：

def execute(filters=None):
    filters = filters or {}
    conditions = []
    params = {}

    # 仅当account过滤字段有值时，添加对应条件
    if filters.get("account"):
        conditions.append("ja.account = %(account)s")
        params["account"] = filters["account"]

    # 基础SQL语句
    sql = """
        SELECT
            j.company as company,
            ja.account as account,
            ja.debit as debit,
            ja.credit as credit
        FROM `tabJournal Entry` as j
        INNER JOIN `tabJournal Entry Account` as ja ON ja.parent = j.name
    """

    # 拼接WHERE条件（如果有）
    if conditions:
        sql += " WHERE " + " AND ".join(conditions)

    # 执行查询并返回结果
    result = frappe.db.sql(sql, params, as_dict=True)
    columns = [
        {"label": "公司", "fieldname": "company", "fieldtype": "Link", "options": "Company"},
        {"label": "账户", "fieldname": "account", "fieldtype": "Link", "options": "Account"},
        {"label": "借方", "fieldname": "debit", "fieldtype": "Currency"},
        {"label": "贷方", "fieldname": "credit", "fieldtype": "Currency"},
    ]
    return columns, result

配置步骤 #

1. 在ERPNext中创建Script Report（而非Query Report）
2. 将上述代码填入「Script」标签页
3. 在「Filters」标签页添加account字段，取消勾选「必填」选项

备选：纯SQL兼容方案（需注意变量存在性） #

如果坚持用Query Report，可尝试以下SQL，但需确保ERPNext在字段为空时仍传递%(account)s变量（部分场景可能仍会触发KeyError，因此不推荐）：

SELECT
    j.company as company,
    ja.account as account,
    ja.debit as debit,
    ja.credit as credit
FROM `tabJournal Entry` as j
INNER JOIN `tabJournal Entry Account` as ja ON ja.parent = j.name
WHERE
    (%(account)s IS NULL OR ja.account = %(account)s)

注意事项 #

• 纯SQL方案需确保过滤字段的默认值设置为NULL
• 若仍出现KeyError，说明ERPNext未传递空值变量，此时必须改用Script Report方案

内容的提问来源于stack exchange，提问作者Haseeb