Hi everyone,
Can anyone guide me on how to implement a Query Report with optional parameters in ERPNext?
I’m facing an issue when running the report with filters. Specifically, when I leave an optional filter empty, it throws a KeyError.
SELECT
t1.sales_person,
SUM(CASE
WHEN t1.parenttype = ‘Sales Invoice’ THEN t1.allocated_amount
ELSE 0
END) AS billed_amount_invoice,
SUM(CASE
WHEN t1.parenttype = ‘Sales Order’ THEN t1.allocated_amount
ELSE 0
END) AS billed_amount_order,
SUM(CASE
WHEN t1.parenttype = ‘Sales Order’ THEN t1.allocated_amount
ELSE 0
END) -
SUM(CASE
WHEN t1.parenttype = ‘Sales Invoice’ THEN t1.allocated_amount
ELSE 0
END) AS pending_billing_amount
FROM
tabSales Team t1
WHERE
t1.parenttype IN (‘Sales Invoice’, ‘Sales Order’)
AND (%(sales_person)s IS NULL OR t1.sales_person LIKE %(sales_person)s)
GROUP BY
t1.sales_person;
How can I handle optional filters properly to avoid this error?
Thanks in advance!
AFAIK it’s not possible in query report. If you can, create a script report . Else try this hack.
Get a default value of ALL in the Sales Person filter,(i believe it is possible to add custom value in link field) and adjust your query with an or condition
@vijaywm Could you please assist me with how to add a custom value to a Link field?
frappe.query_reports[“Sales Person Target”] = {
filters: [
{
fieldname: “sales_person”,
label: “Sales Person”,
fieldtype: “Select”,
default: “All”,
options: [“Loading…”]
}
],
onload: function(report) {
frappe.db.get_list(“Sales Person”, {
fields: [“name”],
limit: 999
}).then(res => {
let sales_persons = res.map(r => r.name);
report.set_filter_options(“sales_person”, [“All”, …sales_persons]);
});
}
};
I tried this approach, but it’s not work
can you create a Sales Person named ‘All’… just for this report filter and set it as default
if not you can try with get_query and write some controller method to return with “All”… e.g.
{
fieldtype: "Link",
label: __("Mode of Payment"),
fieldname: "mode_of_payment",
options: "Mode of Payment",
get_query: function () {
return {
query: "erpnext.accounts.doctype.payment_order.payment_order.get_mop_query",
filters: { parent: frm.doc.name },
};
},
},
