Script Report

Frappe gives you several ways to build a report, and picking the heaviest one first is a common waste of an afternoon. The Report DocType’s type field lists Report Builder, Query Report, Script Report and Custom Report.

Choosing between the report types

Report Builder is a saved list view: pick a DocType, choose columns (child table fields included), add filters, group and sort. No code. If the question is “show me open Sales Orders by customer with these columns”, start here.

Query Report is a SQL select whose result becomes the report. It’s the right tool when one query answers the question.

Script Report runs Python. You can call several queries, loop, bucket values by date, merge data from different DocTypes and return rows in whatever shape the reader needs. You can also return a chart, a message and summary figures above the table.

Before writing any of them, check what ERPNext already ships. Accounts Receivable, for example, ages outstanding invoices with more options than most custom versions end up with.

Standard and non-standard

With developer mode on, a Script Report saved with Is Standard set to Yes and a Module chosen gets its own folder in that module, report/<report_name>/, containing three files: a .py with execute, a .js with filters, and a .json with the report record. That folder is part of a Frappe app and goes into Git like any other code.

A non-standard Script Report keeps its Python in the Report record itself. That code runs through the same safe_exec sandbox as a Server Script, so it needs server_script_enabled on the bench and inherits the same restrictions.

An example execute function

# acme_custom/acme_custom/report/overdue_by_customer/overdue_by_customer.py
import frappe
from frappe import _
from frappe.utils import date_diff, flt, getdate


def execute(filters=None):
    filters = frappe._dict(filters or {})
    as_of = getdate(filters.as_of_date)

    columns = [
        {"fieldname": "customer", "label": _("Customer"), "fieldtype": "Link", "options": "Customer", "width": 220},
        {"fieldname": "days_1_30", "label": _("1-30 Days"), "fieldtype": "Currency", "width": 130},
        {"fieldname": "days_31_60", "label": _("31-60 Days"), "fieldtype": "Currency", "width": 130},
        {"fieldname": "days_over_60", "label": _("Over 60 Days"), "fieldtype": "Currency", "width": 130},
    ]

    invoices = frappe.get_list(
        "Sales Invoice",
        filters={"docstatus": 1, "company": filters.company, "outstanding_amount": [">", 0], "due_date": ["<", as_of]},
        fields=["customer", "due_date", "outstanding_amount"],
    )

    rows = {}
    for inv in invoices:
        row = rows.setdefault(inv.customer, {"customer": inv.customer, "days_1_30": 0, "days_31_60": 0, "days_over_60": 0})
        days = date_diff(as_of, inv.due_date)
        bucket = "days_1_30" if days <= 30 else "days_31_60" if days <= 60 else "days_over_60"
        row[bucket] += flt(inv.outstanding_amount)

    return columns, list(rows.values())

The matching .js file registers company and as_of_date in frappe.query_reports["Overdue by Customer"].

Things that catch people out

• Permissions. frappe.get_list applies the user’s permissions. frappe.get_all and raw frappe.db.sql skip them, so a report built on either shows every record to anyone who can open the report. Role access to the report is set separately on the Report record.
• Return order. execute can return up to six values in a fixed order: columns, data, message, chart, report summary and a flag to skip the total row. Put a chart second and the table breaks.
• Slow reports. If a Script Report runs longer than 15 seconds, Frappe switches it to a prepared report, which then runs as a background job, unless automation is disabled on the record.

erpfly writes reports as standard Script Reports inside the app, so each one arrives in the pull request with its filters and column definitions visible. The accounting module and ERPNext customization pages show the kinds of reports finance teams usually ask for.