/frappe-impl-reports

name: frappe-impl-reports
description: >
  Use when building Script Reports, Query Reports, dashboard charts, or Number Cards in ERPNext.
  Prevents empty report output from wrong column definitions, broken filters, and unoptimized SQL in large datasets.
  Covers Report Builder, Script Report (Python + JS), Query Report, Report filters, dashboard Chart DocType, Number Card, report permissions.
  Keywords: report, Script Report, Query Report, dashboard, chart, Number Card, filters, columns, execute, get_data, create report, custom report, dashboard chart, report empty, no data showing..
license: MIT
compatibility: "Claude Code, Claude.ai Projects, Claude API. Frappe v14-v16."
metadata:
  author: OpenAEC-Foundation
  version: "2.0"

Frappe Report Building

Quick Reference

| Report Type | Best For | Access | Files | |---|---|---|---| | Query Report | Simple SQL queries | System Manager only | SQL in DocType or `.py` | | Script Report | Complex logic, charts | Administrator + Dev Mode | `.py` + `.js` | | Report Builder | End-user ad-hoc reports | Any permitted user | UI only | | Prepared Report | Large datasets (>100k rows) | Same as source report | Background job |

Decision Tree: Which Report Type?

Need a report?
├─ End user builds it themselves? → Report Builder
├─ Simple SQL with no Python logic? → Query Report
├─ Complex logic / charts / summary? → Script Report
│   └─ Dataset > 100k rows or timeout? → Add prepared_report = True
└─ Real-time KPI on workspace? → Number Card or Dashboard Chart

1. Creating a Script Report

File Structure

my_app/my_module/report/sales_summary/
├── sales_summary.json    # Report DocType definition
├── sales_summary.py      # Python: execute() function
└── sales_summary.js      # JavaScript: filters + config

ALWAYS create via Desk: Report > New > Script Report > set "Is Standard = Yes" in Developer Mode.

Python: The execute() Function

# sales_summary.py
import frappe
from frappe import _

def execute(filters=None):
    columns = get_columns()
    data = get_data(filters)
    chart = get_chart(data)
    report_summary = get_summary(data)
    return columns, data, None, chart, report_summary

def get_columns():
    return [
        {"fieldname": "customer", "label": _("Customer"), "fieldtype": "Link",
         "options": "Customer", "width": 200},
        {"fieldname": "total", "label": _("Total"), "fieldtype": "Currency",
         "options": "currency", "width": 120},
        {"fieldname": "qty", "label": _("Qty"), "fieldtype": "Int", "width": 80},
        {"fieldname": "posting_date", "label": _("Date"), "fieldtype": "Date", "width": 100},
    ]

def get_data(filters):
    conditions = get_conditions(filters)
    return frappe.db.sql("""
        SELECT
            si.customer, SUM(si.grand_total) as total,
            SUM(si.total_qty) as qty, si.posting_date
        FROM `tabSales Invoice` si
        WHERE si.docstatus = 1 {conditions}
        GROUP BY si.customer
        ORDER BY total DESC
    """.format(conditions=conditions), filters, as_dict=True)

def get_conditions(filters):
    conditions = ""
    if filters.get("from_date"):
        conditions += " AND si.posting_date >= %(from_date)s"
    if filters.get("to_date"):
        conditions += " AND si.posting_date <= %(to_date)s"
    if filters.get("company"):
        conditions += " AND si.company = %(company)s"
    return conditions

**Return value order** (positional — ALWAYS maintain this order):

| Position | Name | Type | Required | |---|---|---|---| | 1 | `columns` | list[dict] | YES | | 2 | `data` | list[dict] or list[list] | YES | | 3 | `message` | str or None | NO | | 4 | `chart` | dict or None | NO | | 5 | `report_summary` | list[dict] or None | NO | | 6 | `skip_total_rows` | bool | NO |

JavaScript: Filters

// sales_summary.js
frappe.query_reports["Sales Summary"] = {
    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),
            reqd: 1
        },
        {
            fieldname: "to_date",
            label: __("To Date"),
            fieldtype: "Date",
            default: frappe.datetime.get_today(),
            reqd: 1
        },
        {
            fieldname: "customer_group",
            label: __("Customer Group"),
            fieldtype: "Link",
            options: "Customer Group",
            depends_on: "eval:doc.company"
        }
    ],
    formatter: function(value, row, column, data, default_formatter) {
        value = default_formatter(value, row, column, data);
        if (column.fieldname === "total" && data.total > 100000) {
            value = "<span style='color:green;font-weight:bold'>" + value + "</span>";
        }
        return value;
    }
};

2. Creating a Query Report

Query Reports use raw SQL. ALWAYS use the legacy column format in SQL aliases:

SELECT
    `tabWork Order`.name AS "Work Order:Link/Work Order:200",
    `tabWork Order`.creation AS "Date:Date:120",
    `tabWork Order`.company AS "Company:Link/Company:150",
    `tabWork Order`.qty AS "Qty:Int:80",
    `tabWork Order`.grand_total AS "Total:Currency:120"
FROM `tabWork Order`
WHERE `tabWork Order`.docstatus = 1
ORDER BY `tabWork Order`.creation DESC

**Column format**: `"Label:Fieldtype/Options:Width"`

Use `%(filter_name)s` for filter variables in WHERE clauses.

3. Adding Charts to Reports

Return a chart dict as the 4th element from `execute()`:

def get_chart(data):
    labels = [d.customer for d in data[:10]]
    values = [d.total for d in data[:10]]
    return {
        "data": {