Reports: Query, Script & Report Builder

Quick Reference

Report Types at a Glance

Script Report execute() Return Values

def execute(filters=None):
    columns = [...]   # List of dicts
    data = [...]      # List of dicts or lists
    message = "..."   # Optional: HTML message above report
    chart = {...}     # Optional: chart configuration
    report_summary = [...]  # Optional: summary cards
    skip_total_row = False  # Optional: suppress auto-total
    return columns, data, message, chart, report_summary, skip_total_row

Column Definition (Dict Format)

columns = [
    {
        "fieldname": "customer",
        "label": _("Customer"),
        "fieldtype": "Link",
        "options": "Customer",
        "width": 200
    },
    {
        "fieldname": "amount",
        "label": _("Amount"),
        "fieldtype": "Currency",
        "options": "currency",  # field in row holding currency code
        "width": 120
    }
]

Query Report Column Format (Legacy String)

SELECT
  name as "Sales Order:Link/Sales Order:200",
  customer as "Customer:Link/Customer:180",
  grand_total as "Total:Currency:120",
  transaction_date as "Date:Date:100"
FROM `tabSales Order`
WHERE docstatus = 1

Format: "Label:Fieldtype/Options:Width" — Options only needed for Link, Dynamic Link, Currency.

Filter Definition (JS)

frappe.query_reports["My Report"] = {
    filters: [
        {
            fieldname: "company",
            label: __("Company"),
            fieldtype: "Link",
            options: "Company",
            default: frappe.defaults.get_user_default("company"),
            reqd: 1
        },
        {
            fieldname: "from_date",
            label: __("From Date"),
            fieldtype: "Date",
            default: frappe.datetime.add_months(frappe.datetime.get_today(), -1)
        },
        {
            fieldname: "status",
            label: __("Status"),
            fieldtype: "Select",
            options: "\nDraft\nSubmitted\nCancelled"
        }
    ]
};

Decision Tree: Which Report Type?

Need a report?
├─ Simple list/group of one DocType → Report Builder
│   (no code, UI-only, supports Group By with Count/Sum/Avg)
├─ Direct SQL query, no Python logic needed → Query Report
│   (SQL in Report doc, column format in aliases)
├─ Complex logic, calculations, charts → Script Report (Standard)
│   (Python .py + JS .js files, requires Developer Mode)
└─ Quick one-off with Python but no app deploy → Script Report (Custom)
    (Python in Report doc UI, System Manager can create)

Script Report returns what?
├─ Just data → return columns, data
├─ Data + chart → return columns, data, None, chart
├─ Data + summary → return columns, data, None, None, report_summary
├─ Data + message → return columns, data, message
└─ Everything → return columns, data, message, chart, report_summary, skip_total_row

Supported Fieldtypes for Columns

Supported Filter Fieldtypes

Chart Data Format

chart = {
    "data": {
        "labels": ["Jan", "Feb", "Mar", "Apr"],
        "datasets": [
            {"name": _("Revenue"), "values": [100, 200, 150, 300]},
            {"name": _("Expense"), "values": [80, 150, 120, 250]}
        ]
    },
    "type": "bar",        # bar, line, pie, donut, percentage
    "fieldtype": "Currency",
    "options": "currency",
    "currency": "USD",
    "colors": ["#5e64ff", "#ffa00a"]  # Optional custom colors
}

Report Summary Format

report_summary = [
    {
        "value": total_revenue,
        "label": _("Total Revenue"),
        "datatype": "Currency",
        "currency": "USD",
        "indicator": "Green"   # Green, Blue, Orange, Red
    },
    {
        "value": total_count,
        "label": _("Total Orders"),
        "datatype": "Int",
        "indicator": "Blue"
    }
]

Prepared Reports

For reports processing large datasets, enable Prepared Report to run asynchronously:

1. Set prepared_report = 1 in the Report document
2. User clicks "Generate New Report" — runs in background via enqueue()
3. Results stored in file; user downloads or views when ready
4. ALWAYS use for reports that query > 100k rows or take > 30 seconds

Number Cards

Custom method signature:

@frappe.whitelist()
def get_total_active_users(filters=None):
    return frappe.db.count("User", {"enabled": 1})

Dashboard Charts

Dashboard Chart Source hook in hooks.py:

dashboard_chart_source = [
    "myapp.dashboard_chart_source.get_chart_data"
]

Critical Rules

• ALWAYS define columns as list of dicts with fieldname, label, fieldtype. The legacy string format is ONLY for Query Report SQL aliases.
• NEVER return None for columns or data in execute() — ALWAYS return empty lists [].
• ALWAYS use _(...) for translatable labels in columns and report_summary.
• NEVER use frappe.db.sql with user-supplied filter values directly in f-strings — ALWAYS pass as parameters: frappe.db.sql(query, filters, as_dict=True).
• ALWAYS set Reference DocType on the Report document — it controls user access permissions.
• NEVER omit width in column definitions — columns without width render poorly.
• ALWAYS match datasets[].values length to labels length in chart data — mismatched lengths cause chart rendering errors.

See Also

• references/query-report.md — Complete Query Report API
• references/script-report.md — Script Report JS API
• references/examples.md — Working report examples
• references/anti-patterns.md — Common report mistakes
• references/dashboard.md — Number Cards, Dashboard Charts