Frappe Standard Script Report Controller

Frappe Standard Script Report — Python Controller

Companion to frappe-standard-script-report-schema (.json) and frappe-standard-script-report-view (.js). This skill covers the .py controller only.

Golden Rule

execute(filters=None) must return (columns, data) — a list of column definitions and a list of row dicts.

Skeleton

# Copyright (c) 2026, <your company> and contributors
# For license information, please see license.txt

from __future__ import annotations

import frappe
from frappe import _


def execute(filters=None):
    filters = frappe._dict(filters or {})
    columns = get_columns()
    data = get_data(filters)
    return columns, data


def get_columns():
    return [
        {
            "fieldname": "name",
            "label": _("Document"),
            "fieldtype": "Link",
            "options": "Sales Invoice",
            "width": 180,
        },
        {
            "fieldname": "customer",
            "label": _("Customer"),
            "fieldtype": "Link",
            "options": "Customer",
            "width": 200,
        },
        {
            "fieldname": "grand_total",
            "label": _("Grand Total"),
            "fieldtype": "Currency",
            "width": 130,
        },
    ]


def get_data(filters):
    return frappe.get_all(
        "Sales Invoice",
        filters={"docstatus": 1},
        fields=["name", "customer", "grand_total"],
        order_by="posting_date desc",
    )

The execute() Contract

def execute(filters=None) -> tuple[list, list]:
    filters = frappe._dict(filters or {})  # always normalise
    ...
    return columns, data

Always wrap filters with frappe._dict(filters or {}) — this allows safe attribute access (filters.company) and safe .get() calls without KeyError.

Columns

List of column definition dicts (see Columns below).

Data

List of row dicts — one key per fieldname declared in columns. Extra keys in the row dict are silently ignored by the UI. Rows can also be plain lists when frappe.db.sql(..., as_list=1) is used; in that case each row is positionally aligned with the columns list.

Extended return values (out of scope)

execute() may return up to four values: columns, data, message, chart. The message (HTML string) and chart (chart config dict) are shown above/below the table. These are rarely needed and not covered here.

Columns

Column dict properties

Fieldtype reference

Link example

{
    "fieldname": "customer",
    "label": _("Customer"),
    "fieldtype": "Link",
    "options": "Customer",
    "width": 200,
}

Dynamic Link example

The options value is the fieldname of the column that holds the doctype name. Set width: 0 on the type column to hide it while keeping it available for resolution:

{"fieldname": "doc_type", "label": _("Doc Type"), "fieldtype": "Link",         "options": "DocType",   "width": 0},
{"fieldname": "doc_name", "label": _("Document"),  "fieldtype": "Dynamic Link", "options": "doc_type",  "width": 180},

Currency vs Float

Use Currency for monetary amounts — it picks up the active currency symbol in the UI and respects convertible: "amount". Use Float for non-monetary decimals.

Hidden metadata columns

Use hidden: 1 to pass structured data to the JS formatter without displaying it. You can attach an arbitrary meta key for the JS side to read:

{
    "fieldname": "_meta",
    "fieldtype": "Data",
    "hidden": 1,
    "meta": {"evaluations": [...]},  # arbitrary payload, accessible in JS formatter
}

Report-level metadata (_meta convention)

Frappe reports have no native mechanism for returning metadata about the report as a whole (as opposed to per-row values). The established convention is to carry report-level metadata in a hidden _meta column defined in get_columns(), with the payload attached to the column definition itself (not to any row). The JS formatter reads this from report.columns.

def get_columns():
    meta = {
        "currency": frappe.defaults.get_global_default("currency"),
        "company": frappe.defaults.get_global_default("company"),
        # any other report-wide values the JS layer needs
    }

    return [
        {
            "fieldname": "_meta",
            "label": "_meta",
            "fieldtype": "Data",
            "hidden": 1,
            "meta": meta,
        },
        # ... rest of columns
    ]

On the JS side (formatter or onload):

const meta = report.columns.find(c => c.fieldname === "_meta")?.meta ?? {};

Do not populate _meta values in individual row dicts — the payload lives on the column definition, not on rows. This keeps per-row data clean and avoids sending the same metadata payload for every row.

Legacy string format

You may encounter _("Customer") + ":Link/Customer:150" in older ERPNext code. This "Label:fieldtype/options:width" shorthand still works but is not recommended for new code. Use dicts.

Fetching Data

Preference order — use the highest-level API that can express the query:

1. frappe.get_all — always prefer first; handles single-doctype queries with filters, ordering, and pagination with zero boilerplate.
2. frappe.qb — use when you need joins, aggregations, or composable dynamic conditions that frappe.get_all cannot express; fully injection-safe without raw SQL strings.
3. frappe.db.sql + Jinja — last resort only; reserve for queries that frappe.qb cannot express (e.g. complex subqueries, CTEs, or database-specific functions).

1. frappe.get_all — ORM-style ✅ Prefer first

Best for simple queries on a single doctype. Returns a list of frappe._dict objects.

def get_data(filters):
    f = {"docstatus": 1}

    if filters.company:
        f["company"] = filters.company

    if filters.from_date and filters.to_date:
        f["posting_date"] = ["between", [filters.from_date, filters.to_date]]
    elif filters.from_date:
        f["posting_date"] = [">=", filters.from_date]

    return frappe.get_all(
        "Sales Invoice",
        filters=f,
        fields=["name", "customer", "posting_date", "grand_total"],
        order_by="posting_date desc",
    )

Filter operators

2. frappe.qb — Query Builder ✅ Prefer over Raw SQL

Use when you need joins, aggregations, or composable dynamic conditions that frappe.get_all cannot express. Fully injection-safe — no raw SQL strings, no Jinja required. Powered by PyPika.

from frappe.query_builder.functions import Coalesce, Sum


def get_data(filters):
    si = frappe.qb.DocType("Sales Invoice")
    sii = frappe.qb.DocType("Sales Invoice Item")

    query = (
        frappe.qb.from_(si)
        .join(sii)
        .on(sii.parent == si.name)
        .select(
            si.name,
            si.customer,
            si.posting_date,
            Sum(sii.amount).as_("total_amount"),
        )
        .where(si.docstatus == 1)
        .groupby(si.name)
        .orderby(si.posting_date, order=frappe.qb.desc)
    )

    if filters.company:
        query = query.where(si.company == filters.company)

    if filters.from_date:
        query = query.where(si.posting_date >= filters.from_date)

    if filters.to_date:
        query = query.where(si.posting_date <= filters.to_date)

    return query.run(as_dict=1)

Common functions from frappe.query_builder.functions: Sum, Count, Avg, Max, Min, Date, Coalesce, IfNull, Concat_ws.

Use .left_join() for outer joins and .inner_join() (or .join()) for inner joins.

3. frappe.db.sql — Raw SQL ⚠️ Last resort

Use only when frappe.qb cannot express the query — e.g. CTEs, window functions, complex subqueries, or database-specific SQL functions. For everything else, prefer frappe.get_all or frappe.qb.

Use Jinja templating for conditional SQL structure, and parameterized values (%(x)s) for all user-supplied data — never embed filter values directly into the SQL string via f-strings or % formatting.

Build the SQL with frappe.render_template, passing a params dict as context. Jinja {%- if %} blocks include or exclude SQL clauses; %(param)s placeholders use different delimiters and are left untouched by Jinja, then filled by frappe.db.sql.

def get_data(filters):
    params = {}

    if filters.company:
        params["company"] = filters.company

    if filters.from_date and filters.to_date:
        params["from_date"] = filters.from_date
        params["to_date"] = filters.to_date

    sql = frappe.render_template(
        """
        SELECT
            si.name,
            si.customer,
            si.posting_date,
            SUM(sii.amount) AS total_amount
        FROM `tabSales Invoice` si
        JOIN `tabSales Invoice Item` sii ON sii.parent = si.name
        WHERE si.docstatus = 1
        {%- if company %}
        AND si.company = %(company)s
        {%- endif %}
        {%- if from_date and to_date %}
        AND si.posting_date BETWEEN %(from_date)s AND %(to_date)s
        {%- endif %}
        GROUP BY si.name
        ORDER BY si.posting_date DESC
        """,
        params,
    )

    return frappe.db.sql(sql, params, as_dict=1)

Jinja {%- if company %} checks the key in params; %(company)s is a SQL placeholder — Jinja passes it through unchanged.
as_dict=1 returns dicts keyed by the column alias — aligns directly with report column fieldname values.
as_list=1 returns flat lists — columns and rows must be positionally aligned.

Permission-Aware Queries

build_match_conditions generates an SQL fragment that enforces Frappe User Permissions (e.g., a user scoped to a single company). It returns a non-empty string when restrictions apply, or an empty string when the user has full access.

Since build_match_conditions returns an already-escaped SQL fragment (not user input), it can be safely injected via Jinja {{ match_conditions }}.

from frappe.desk.reportview import build_match_conditions


def get_data(filters):
    params = {}

    if filters.company:
        params["company"] = filters.company

    match_conditions = build_match_conditions("Sales Invoice")

    sql = frappe.render_template(
        """
        SELECT si.name, si.customer, si.grand_total
        FROM `tabSales Invoice` si
        WHERE si.docstatus = 1
        {%- if company %}
        AND si.company = %(company)s
        {%- endif %}
        {%- if match_conditions %}
        AND {{ match_conditions }}
        {%- endif %}
        """,
        {**params, "match_conditions": match_conditions},
    )

    return frappe.db.sql(sql, params, as_dict=1)

build_match_conditions returns values already escaped — injecting via {{ match_conditions }} is safe.
Do not put raw user-supplied filter values into Jinja {{ }} — always use %(param)s for those.
When using frappe.get_list (instead of frappe.get_all), match conditions are applied automatically; build_match_conditions is only needed with frappe.db.sql or frappe.qb.

Reusable get_columns()

When two reports share most of the same columns, export get_columns from the base report and compose in the extended report. Keep get_columns side-effect-free and filter-independent so it can be imported safely.

Base report (export get_columns)

# sales_invoice_base/sales_invoice_base.py

def get_columns():
    return [
        {"fieldname": "name",         "label": _("Invoice"),      "fieldtype": "Link",     "options": "Sales Invoice", "width": 180},
        {"fieldname": "customer",     "label": _("Customer"),     "fieldtype": "Link",     "options": "Customer",      "width": 200},
        {"fieldname": "posting_date", "label": _("Date"),         "fieldtype": "Date",     "width": 110},
        {"fieldname": "grand_total",  "label": _("Grand Total"),  "fieldtype": "Currency", "width": 130},
    ]

Extended report — append columns

from .sales_invoice_base import get_columns as get_base_columns


def get_columns():
    return get_base_columns() + [
        {"fieldname": "commission_amount", "label": _("Commission"), "fieldtype": "Currency", "width": 130},
        {"fieldname": "commission_rate",   "label": _("Rate %"),     "fieldtype": "Percent",  "width": 90},
    ]

Extended report — filter out columns

from .sales_invoice_base import get_columns as get_base_columns

_EXCLUDE = {"grand_total"}


def get_columns():
    return [c for c in get_base_columns() if c["fieldname"] not in _EXCLUDE]

Extended report — insert at a specific position

from .sales_invoice_base import get_columns as get_base_columns


def get_columns():
    base = get_base_columns()
    # Insert after first column
    return base[:1] + [
        {"fieldname": "department", "label": _("Department"), "fieldtype": "Link", "options": "Department", "width": 140},
    ] + base[1:]