/frappe-syntax-reports

name: frappe-syntax-reports
description: >
  Use when building Query Reports, Script Reports, or configuring Report Builder, including chart data integration.
  Prevents report errors from wrong column definitions, missing permissions, and incorrect data formatting.
  Covers Query Report (SQL-based), Script Report (Python-based), Report Builder, report columns definition, filters, chart_data, report permissions, prepared_report.
  Keywords: Query Report, Script Report, Report Builder, report columns, filters, chart_data, frappe.query_report, prepared_report, report columns, how to build report, report not showing data, chart in report..
license: MIT
compatibility: "Claude Code, Claude.ai Projects, Claude API. Frappe v14-v16."
metadata:
  author: OpenAEC-Foundation
  version: "2.0"

Reports: Query, Script & Report Builder

Quick Reference

Report Types at a Glance

| Type | Code Required | Use Case | Permission | |------|--------------|----------|------------| | Report Builder | None | Simple single-DocType listing with filters, group by | Any user | | Query Report | SQL only | Direct SQL queries, legacy column format | System Manager | | Script Report (Standard) | Python + JS | Complex logic, charts, summaries, trees | Administrator + Developer Mode | | Script Report (Custom) | Python in UI | Quick custom reports without app deployment | System Manager |

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

| Fieldtype | Options Required | Notes | |-----------|-----------------|-------| | `Data` | No | Plain text | | `Link` | DocType name | Clickable link to document | | `Dynamic Link` | Fieldname holding DocType | Pair with a column containing DocType | | `Currency` | Currency field or code | Fieldname in row that holds currency | | `Float` | No | Decimal number | | `Int` | No | Integer | | `Percent` | No | Shows percentage bar | | `Date` | No | Date display | | `Datetime` | No | Date + time | | `Check` | No | Boolean checkbox | | `Select` | No | Dropdown value | | `Text` | No | Long text | | `HTML` | No | Raw HTML rendering |

Supported Filter Fieldtypes

| Fieldtype | Options | Behavior | |-----------|---------|----------| | `Link` | DocType name | Autocomplete from DocType | | `Select` | Newline-separated values | Dropdown with fixed options | | `Date` | — | Date picker | | `DateRange` | — | Returns `[from_date, to_date]` list | | `Check` | — | Boolean toggle | | `Dynamic Link` | Fieldname of Link filter | Depends on another filter value | | `Data` | — | Free text input | | `Int` | — | Numeric input | | `MultiSelectList` | DocType name | Multiple value selection |

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