Query Reports use raw SQL. ALWAYS use the legacy column format in SQL aliases:
SELECT
`tabWork Order`.name AS "Work Order:Link/Work Order:200",
`tabWork Order`.creation AS "Date:Date:120",
`tabWork Order`.company AS "Company:Link/Company:150",
`tabWork Order`.qty AS "Qty:Int:80",
`tabWork Order`.grand_total AS "Total:Currency:120"
FROM `tabWork Order`
WHERE `tabWork Order`.docstatus =1ORDERBY `tabWork Order`.creation DESC
Column format: "Label:Fieldtype/Options:Width"
Use %(filter_name)s for filter variables in WHERE clauses.
3. Adding Charts to Reports
Return a chart dict as the 4th element from execute():
defget_chart(data):
labels = [d.customer for d in data[:10]]
values = [d.total for d in data[:10]]
return {
"data": {
"labels": labels,
"datasets": [{"name": _("Revenue"), "values": values}]
},
"type": "bar", # bar | line | pie | donut | percentage"colors": ["#7cd6fd"],
"barOptions": {"stacked": False}, # for bar charts"height": 300
}
Chart types: bar, line, pie, donut, percentage.
For multi-dataset charts (e.g., comparing periods):
Source types: Report, Group By, Custom (whitelisted method).
Group By Chart
{"chart_name":"Invoices by Status","chart_type":"Group By","document_type":"Sales Invoice","group_by_type":"Count","group_by_based_on":"status","type":"Donut","filters_json":"{\"docstatus\": 1}"}
8. Building a Dashboard
Dashboards combine multiple charts and Number Cards:
{"name":"Sales Dashboard","module":"Selling","charts":[{"chart":"Monthly Revenue","width":"Full"},{"chart":"Invoices by Status","width":"Half"},{"chart":"Top Customers","width":"Half"}],"cards":[{"card":"Total Revenue"},{"card":"Open Orders"}]}
9. Performance Optimization
ALWAYS add indexes on columns used in WHERE/GROUP BY (frappe.model.utils.add_index)
ALWAYS use as_dict=True in frappe.db.sql() — matches column fieldnames
NEVER use SELECT * — specify exact columns
NEVER load full documents (frappe.get_doc) inside report loops — use SQL
Use frappe.qb (query builder) for parameterized queries in v14+
For reports > 50k rows, ALWAYS enable prepared_report: true
ALWAYS filter by docstatus to exclude draft/cancelled documents
10. Common Patterns
Date Range Filter Pattern
if filters.get("from_date") and filters.get("to_date"):
conditions += " AND posting_date BETWEEN %(from_date)s AND %(to_date)s"
Multi-Currency Pattern
{"fieldname": "amount", "label": _("Amount"), "fieldtype": "Currency",
"options": "currency", "width": 120}
# "options": "currency" means use the row's "currency" field for formatting
Group By with Totals Pattern
data = frappe.db.sql("""
SELECT customer, COUNT(*) as count, SUM(grand_total) as total
FROM `tabSales Invoice`
WHERE docstatus = 1 {conditions}
GROUP BY customer WITH ROLLUP
""".format(conditions=conditions), filters, as_dict=True)
