frappe-syntax-reports

# 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

```python
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)

```python
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)

```sql
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)

```javascript
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

```python
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

```python
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

| Source Type | Required Fields | How It Works |
|----