Mastering Reports in ERPNext
Frappe — — Ali Raza
A practical guide to creating, optimizing and trusting ERPNext reports — Query Reports, Script Reports and dashboards.
Every ERPNext implementation I have led — a hospital group, a municipal services department, an HR portal serving a few thousand employees — ended up being judged less on the forms people fill in and more on the reports management pulls out. Data entry is the cost; reports are the payoff. After building dozens of them on the Frappe Framework, this is the approach I keep coming back to.
Start by picking the right report type, because each has a ceiling. Report Builder is configuration-only and fine for flat lists — a filtered view of Patient Appointment or Employee Checkin. Query Reports let you write raw SQL against the MariaDB tables. Script Reports give you a full Python execute(filters) function, and they are what I reach for whenever there are computed columns, grouping, or real business logic.
Query Reports are faster to ship than people expect. The tables follow a simple convention — the Sales Invoice DocType lives in `tabSales Invoice` — and report filters map straight into the SQL as %(from_date)s placeholders. My habit is to prototype the query inside `bench mariadb` first, read the EXPLAIN output, and only then paste it into the Report doc.
Three Query Report pitfalls bite almost everyone once:
- Forgetting docstatus = 1. Submittable DocTypes keep drafts and cancelled documents in the same table, and your totals will quietly include them. - Joining child tables carelessly. Child rows carry parent and parenttype; join on both, or a child DocType reused across parents will leak rows. - Permissions. Query Reports run with full database access — role permissions and User Permissions on the underlying DocTypes are not applied to your SQL. On the healthcare project this mattered legally: a coordinator must never see another facility's patients, so the facility condition went into the WHERE clause driven by the session user, not into a filter the user could simply clear.
Script Reports are where ERPNext reporting gets genuinely powerful. The execute function returns columns and data, and defining columns as dicts pays off immediately: set fieldtype "Link" with options "Patient" and every cell becomes a clickable link into the record; set "Currency" and values format and total correctly. You can also return a chart and a report_summary, which turns one report into a small dashboard without touching a Workspace.
The municipal project taught me to report on workflow states, not just fields. Complaints moved through a Workflow — Received, Assigned, In Progress, Resolved — and management wanted aging per state. The catch is that only the current state lives in the workflow_state field. So I logged every transition into a small custom DocType from a DocType Event Server Script and reported off that log. Reconstructing history from tabVersion JSON at query time was slow and fragile by comparison.
Performance rules I enforce in code review:
- Never call frappe.get_doc inside the row loop. One frappe.get_all with an explicit fields list, or a single SQL join, replaces hundreds of queries. - Index your filter columns. A frappe.db.add_index on posting_date plus the main Link field has taken 40-second reports down to under two. - For genuinely heavy reports, enable Prepared Report on the Report doc. It runs in a background worker and stores the result, so users download it instead of hitting the request timeout.
Finally, treat reports as code. With developer mode on, set is_standard to Yes so the report exports as JSON plus .py and .js files under your custom app's module, then travels through git and `bench --site yoursite migrate` like everything else. Reports created ad hoc in production have a way of vanishing during upgrades, and "the report the auditor uses" is a terrible thing to lose.
Compressed into one sentence: prototype in SQL, promote to a Script Report when logic appears, enforce permissions inside the query itself, and version everything in your app. Do that, and ERPNext's reporting layer will carry a surprising share of your BI needs before you ever reach for an external tool.