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": {
            "labels": labels,
            "datasets": [{"name": _("Revenue"), "values": values}]
        },
        "type": "bar",            # bar | line | pie | donut | percentage
        "colors": ["#7cd6fd"],
        "barOptions": {"stacked": False},  # for bar charts
        "height": 300
    }

Chart types: bar, line, pie, donut, percentage.

For multi-dataset charts (e.g., comparing periods):

"datasets": [
    {"name": "2024", "values": [10, 20, 30]},
    {"name": "2025", "values": [15, 25, 35]}
]

4. Adding Report Summary

Return a list of summary dicts as the 5th element:

def get_summary(data):
    total_revenue = sum(d.total for d in data)
    total_qty = sum(d.qty for d in data)
    return [
        {"value": total_revenue, "label": _("Total Revenue"),
         "datatype": "Currency", "currency": "USD",
         "indicator": "Green" if total_revenue > 0 else "Red"},
        {"value": total_qty, "label": _("Total Qty"),
         "datatype": "Int", "indicator": "Blue"},
        {"value": len(data), "label": _("Customers"),
         "datatype": "Int", "indicator": "Grey"}
    ]

Indicator colors: Green, Blue, Orange, Red, Grey.

5. Prepared Reports

For reports that timeout on large datasets, add to the .js file:

frappe.query_reports["Heavy Report"] = {
    filters: [ /* ... */ ],
    prepared_report: true    // enables background generation
};

When prepared_report: true, Frappe queues the report via background job. Users see cached results and can regenerate on demand.

6. Number Cards

Three types of Number Cards for workspace dashboards:

Type	Source	Use Case
Document Type	DocType aggregate	Count/sum of documents
Report	Script/Query Report	KPI from report data
Custom	Whitelisted method	Any computed value

Document Type Number Card

Create via Desk > Number Card. Set DocType, aggregate function (Count/Sum/Avg), and filters.

Report-Based Number Card

Point to an existing report. The card displays the first row's first numeric column.

Custom Method Number Card

# In your app, create a whitelisted method:
@frappe.whitelist()
def get_open_tickets():
    count = frappe.db.count("Issue", {"status": "Open"})
    return {"value": count, "fieldtype": "Int", "route_options": {"status": "Open"},
            "route": ["query-report", "Open Issues"]}

7. Dashboard Charts

Create via Desk > Dashboard Chart or programmatically in fixtures:

# hooks.py
fixtures = [
    {"dt": "Dashboard Chart", "filters": [["module", "=", "My Module"]]}
]

Source types: Report, Group By, Custom (whitelisted method).

Group By Chart

{
    "chart_name": "Invoices by Status",
    "chart_type": "Group By",
    "document_type": "Sales Invoice",
    "group_by_type": "Count",
    "group_by_based_on": "status",
    "type": "Donut",
    "filters_json": "{\"docstatus\": 1}"
}

8. Building a Dashboard

Dashboards combine multiple charts and Number Cards:

{
    "name": "Sales Dashboard",
    "module": "Selling",
    "charts": [
        {"chart": "Monthly Revenue", "width": "Full"},
        {"chart": "Invoices by Status", "width": "Half"},
        {"chart": "Top Customers", "width": "Half"}
    ],
    "cards": [
        {"card": "Total Revenue"},
        {"card": "Open Orders"}
    ]
}

9. Performance Optimization

• ALWAYS add indexes on columns used in WHERE/GROUP BY (frappe.model.utils.add_index)
• ALWAYS use as_dict=True in frappe.db.sql() — matches column fieldnames
• NEVER use SELECT * — specify exact columns
• NEVER load full documents (frappe.get_doc) inside report loops — use SQL
• Use frappe.qb (query builder) for parameterized queries in v14+
• For reports > 50k rows, ALWAYS enable prepared_report: true
• ALWAYS filter by docstatus to exclude draft/cancelled documents

10. Common Patterns

Date Range Filter Pattern

if filters.get("from_date") and filters.get("to_date"):
    conditions += " AND posting_date BETWEEN %(from_date)s AND %(to_date)s"

Multi-Currency Pattern

{"fieldname": "amount", "label": _("Amount"), "fieldtype": "Currency",
 "options": "currency", "width": 120}
# "options": "currency" means use the row's "currency" field for formatting

Group By with Totals Pattern

data = frappe.db.sql("""
    SELECT customer, COUNT(*) as count, SUM(grand_total) as total
    FROM `tabSales Invoice`
    WHERE docstatus = 1 {conditions}
    GROUP BY customer WITH ROLLUP
""".format(conditions=conditions), filters, as_dict=True)

See Also

• references/examples.md — Complete report examples
• references/anti-patterns.md — Common mistakes
• references/workflows.md — Step-by-step workflows
• frappe-syntax-api — Frappe Python API reference
• frappe-core-database — Database query patterns