Frappe Impl Reports

Frappe Report Building

Quick Reference

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):

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:

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

Report Anti-Patterns

AP-1: Using SELECT * in Report Queries

WRONG:

data = frappe.db.sql("SELECT * FROM `tabSales Invoice` WHERE docstatus = 1", as_dict=True)

RIGHT:

data = frappe.db.sql("""
    SELECT name, customer, grand_total, posting_date
    FROM `tabSales Invoice`
    WHERE docstatus = 1
""", as_dict=True)

Why: SELECT * fetches unnecessary columns, wastes memory, and breaks when schema changes. ALWAYS specify exact columns.

AP-2: Loading Full Documents in Report Loops

WRONG:

def get_data(filters):
    invoices = frappe.get_all("Sales Invoice", filters={"docstatus": 1})
    data = []
    for inv in invoices:
        doc = frappe.get_doc("Sales Invoice", inv.name)  # N+1 query!
        data.append({"name": doc.name, "total": doc.grand_total})
    return data

RIGHT:

def get_data(filters):
    return frappe.db.sql("""
        SELECT name, grand_total as total
        FROM `tabSales Invoice`
        WHERE docstatus = 1
    """, as_dict=True)

Why: frappe.get_doc() inside a loop creates N+1 queries. For 10,000 invoices, that is 10,001 database calls. ALWAYS use SQL or frappe.get_all() with fields parameter.

AP-3: Missing docstatus Filter

WRONG:

data = frappe.db.sql("SELECT customer, grand_total FROM `tabSales Invoice`", as_dict=True)

RIGHT:

data = frappe.db.sql("""
    SELECT customer, grand_total
    FROM `tabSales Invoice`
    WHERE docstatus = 1
""", as_dict=True)

Why: Without docstatus = 1, the report includes Draft (0) and Cancelled (2) documents, producing incorrect totals.

AP-4: Wrong Column Definition Format

WRONG — missing fieldtype causes "undefined" display:

columns = [
    {"fieldname": "customer", "label": "Customer", "width": 200}
]

RIGHT:

columns = [
    {"fieldname": "customer", "label": _("Customer"), "fieldtype": "Link",
     "options": "Customer", "width": 200}
]

Why: Every column MUST have fieldtype. Link columns MUST have options pointing to the target DocType. ALWAYS wrap labels in _() for translation.

AP-5: String Concatenation for SQL Filters

WRONG — SQL injection risk:

def get_data(filters):
    query = "SELECT name FROM `tabSales Invoice` WHERE customer = '" + filters.get("customer") + "'"
    return frappe.db.sql(query, as_dict=True)

RIGHT:

def get_data(filters):
    return frappe.db.sql("""
        SELECT name FROM `tabSales Invoice`
        WHERE customer = %(customer)s
    """, filters, as_dict=True)

Why: NEVER concatenate filter values into SQL strings. ALWAYS use parameterized queries with %(name)s placeholders.

AP-6: Wrong Return Order from execute()

WRONG — chart in position 3 (message slot):

def execute(filters=None):
    columns = get_columns()
    data = get_data(filters)
    chart = get_chart(data)
    return columns, data, chart  # chart is in message position!

RIGHT:

def execute(filters=None):
    columns = get_columns()
    data = get_data(filters)
    chart = get_chart(data)
    return columns, data, None, chart  # None for message, chart in position 4

Why: The return order is fixed: columns, data, message, chart, report_summary. Putting chart in position 3 makes Frappe treat it as a message string.

AP-7: Not Using Prepared Reports for Large Datasets

WRONG — report times out on production:

frappe.query_reports["Huge Report"] = {
    filters: [ /* ... */ ]
    // No prepared_report flag
};

RIGHT:

frappe.query_reports["Huge Report"] = {
    filters: [ /* ... */ ],
    prepared_report: true
};

Why: Reports exceeding 50k rows or 30 seconds execution time MUST use prepared_report: true to run as background jobs.

AP-8: Hardcoded Currency in Summary

WRONG:

{"value": total, "label": "Revenue", "datatype": "Currency", "currency": "USD"}

RIGHT:

company_currency = frappe.get_cached_value("Company", filters.get("company"), "default_currency")
{"value": total, "label": _("Revenue"), "datatype": "Currency", "currency": company_currency}

Why: NEVER hardcode currency. ALWAYS derive it from the Company's default_currency or the transaction's currency field.

Report Examples

Complete Script Report: Monthly Sales Analysis

Python (monthly_sales_analysis.py)

import frappe
from frappe import _
from frappe.utils import flt

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

def get_columns():
    return [
        {"fieldname": "month", "label": _("Month"), "fieldtype": "Data", "width": 100},
        {"fieldname": "customer", "label": _("Customer"), "fieldtype": "Link",
         "options": "Customer", "width": 180},
        {"fieldname": "invoice_count", "label": _("Invoices"), "fieldtype": "Int", "width": 80},
        {"fieldname": "total_qty", "label": _("Qty"), "fieldtype": "Float", "width": 80},
        {"fieldname": "grand_total", "label": _("Revenue"), "fieldtype": "Currency",
         "options": "Company:company:default_currency", "width": 140},
    ]

def get_data(filters):
    conditions = ""
    if filters.get("company"):
        conditions += " AND si.company = %(company)s"
    if filters.get("customer"):
        conditions += " AND si.customer = %(customer)s"

    return frappe.db.sql("""
        SELECT
            DATE_FORMAT(si.posting_date, '%%Y-%%m') AS month,
            si.customer,
            COUNT(si.name) AS invoice_count,
            SUM(si.total_qty) AS total_qty,
            SUM(si.grand_total) AS grand_total
        FROM `tabSales Invoice` si
        WHERE si.docstatus = 1
            AND si.posting_date BETWEEN %(from_date)s AND %(to_date)s
            {conditions}
        GROUP BY month, si.customer
        ORDER BY month DESC, grand_total DESC
    """.format(conditions=conditions), filters, as_dict=True)

def get_chart(data):
    # Aggregate by month for chart
    month_totals = {}
    for row in data:
        month_totals.setdefault(row.month, 0)
        month_totals[row.month] += flt(row.grand_total)

    sorted_months = sorted(month_totals.keys())
    return {
        "data": {
            "labels": sorted_months,
            "datasets": [{"name": _("Revenue"), "values": [month_totals[m] for m in sorted_months]}]
        },
        "type": "bar",
        "colors": ["#5e64ff"]
    }

def get_summary(data):
    total_revenue = sum(flt(d.grand_total) for d in data)
    total_invoices = sum(d.invoice_count for d in data)
    return [
        {"value": total_revenue, "label": _("Total Revenue"),
         "datatype": "Currency", "indicator": "Green"},
        {"value": total_invoices, "label": _("Total Invoices"),
         "datatype": "Int", "indicator": "Blue"},
    ]

JavaScript (monthly_sales_analysis.js)

frappe.query_reports["Monthly Sales Analysis"] = {
    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(), -12),
            reqd: 1
        },
        {
            fieldname: "to_date",
            label: __("To Date"),
            fieldtype: "Date",
            default: frappe.datetime.get_today(),
            reqd: 1
        },
        {
            fieldname: "customer",
            label: __("Customer"),
            fieldtype: "Link",
            options: "Customer"
        }
    ]
};

Query Report: Work Order Status

SELECT
    `tabWork Order`.name AS "Work Order:Link/Work Order:200",
    `tabWork Order`.production_item AS "Item:Link/Item:150",
    `tabWork Order`.qty AS "Qty:Float:80",
    `tabWork Order`.produced_qty AS "Produced:Float:80",
    (`tabWork Order`.qty - `tabWork Order`.produced_qty) AS "Pending:Float:80",
    `tabWork Order`.status AS "Status:Data:100",
    `tabWork Order`.planned_start_date AS "Start Date:Date:100"
FROM `tabWork Order`
WHERE
    `tabWork Order`.docstatus = 1
    AND `tabWork Order`.status NOT IN ('Completed', 'Cancelled')
    AND `tabWork Order`.company = %(company)s
ORDER BY `tabWork Order`.planned_start_date ASC

Custom Number Card Method

@frappe.whitelist()
def get_overdue_invoices_amount():
    """Number Card: total overdue amount across all unpaid invoices."""
    result = frappe.db.sql("""
        SELECT COALESCE(SUM(outstanding_amount), 0) as value
        FROM `tabSales Invoice`
        WHERE docstatus = 1
            AND outstanding_amount > 0
            AND due_date < CURDATE()
    """, as_dict=True)
    return {
        "value": result[0].value if result else 0,
        "fieldtype": "Currency",
        "route_options": {"outstanding_amount": [">", 0], "due_date": ["<", "today"]},
        "route": ["List", "Sales Invoice"]
    }

Pie Chart Example

def get_chart(data):
    status_counts = {}
    for row in data:
        status_counts.setdefault(row.status, 0)
        status_counts[row.status] += 1

    return {
        "data": {
            "labels": list(status_counts.keys()),
            "datasets": [{"values": list(status_counts.values())}]
        },
        "type": "donut",
        "height": 280
    }

Multi-Dataset Line Chart

def get_chart(current_data, previous_data):
    months = ["Jan", "Feb", "Mar", "Apr", "May", "Jun",
              "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]
    return {
        "data": {
            "labels": months,
            "datasets": [
                {"name": "Current Year", "values": current_data},
                {"name": "Previous Year", "values": previous_data}
            ]
        },
        "type": "line",
        "colors": ["#7cd6fd", "#743ee2"],
        "lineOptions": {"regionFill": 1}
    }

Report Building Workflows

Workflow 1: Create a New Script Report

Step 1 — Create Report Document

1. Navigate to Report List in Desk 2. Click "+ Add Report" 3. Set Report Name (e.g., "Sales Summary") 4. Set Report Type = "Script Report" 5. Set Reference DocType (e.g., "Sales Invoice") — controls permissions 6. Set Module (e.g., "Selling") 7. Check "Is Standard" = Yes (requires Developer Mode) 8. Save

Step 2 — Write Python File

Location: my_app/module/report/sales_summary/sales_summary.py

import frappe
from frappe import _

def execute(filters=None):
    columns = get_columns()
    data = get_data(filters)
    return columns, data

def get_columns():
    return [
        {"fieldname": "name", "label": _("Invoice"), "fieldtype": "Link",
         "options": "Sales Invoice", "width": 180},
        # Add more columns...
    ]

def get_data(filters):
    return frappe.db.sql("""...""", filters, as_dict=True)

Step 3 — Write JavaScript File

Location: my_app/module/report/sales_summary/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
        }
    ]
};

Step 4 — Test

1. Run bench build to compile JS assets 2. Navigate to the report in Desk 3. Set filters and verify data 4. Check browser console for JS errors

Step 5 — Add Chart and Summary (Optional)

Extend execute() to return chart and summary in positions 4 and 5.

---

Workflow 2: Add a Dashboard to a Module

Step 1 — Create Dashboard Charts

Create each chart via Desk > Dashboard Chart:

• Set Chart Name, Chart Type (Report / Group By), source, and time period

Step 2 — Create Number Cards

Create each card via Desk > Number Card:

• Set Document Type or Report source
• Set aggregate function if Document Type based

Step 3 — Create Dashboard

1. Desk > Dashboard > New 2. Set Dashboard Name and Module 3. Add charts (Full width or Half width) 4. Add Number Cards 5. Save

Step 4 — Export as Fixtures

In hooks.py:

fixtures = [
    {"dt": "Dashboard", "filters": [["module", "=", "My Module"]]},
    {"dt": "Dashboard Chart", "filters": [["module", "=", "My Module"]]},
    {"dt": "Number Card", "filters": [["module", "=", "My Module"]]},
]

Run: bench --site mysite export-fixtures

---

Workflow 3: Convert Report to Prepared Report

When to Convert

• Report consistently takes > 15 seconds
• Dataset exceeds 50,000 rows
• Users complain about timeouts

Steps

1. Add prepared_report: true to the JS file 2. Test by running the report — it should show "Generate New Report" button 3. Verify background job completes via Background Jobs page 4. ALWAYS keep the Python execute() function efficient even for prepared reports — Frappe still calls it, just in a background worker

---

Workflow 4: Debug a Report Returning Empty Data

Checklist

1. Check filters: Are required filters (reqd: 1) set? Empty filters = empty data 2. Check docstatus: Are you filtering docstatus = 1? Draft documents have docstatus = 0 3. Check column fieldnames: Do column fieldname values match the SQL aliases exactly? 4. Test SQL directly: Run the SQL in bench --site mysite console:

   frappe.db.sql("SELECT ... FROM ... WHERE ...", {"company": "My Company"}, as_dict=True)

5. Check permissions: Does the user have read access to the Reference DocType? 6. Check return order: Is execute() returning columns, data (not data, columns)?

---

Workflow 5: Add Drill-Down Links to Report

Make report cells clickable to navigate to the source document:

# Use Link fieldtype with options pointing to the DocType
{"fieldname": "invoice", "label": _("Invoice"), "fieldtype": "Link",
 "options": "Sales Invoice", "width": 180}

For dynamic links (different DocType per row), use Dynamic Link:

{"fieldname": "reference_name", "label": _("Reference"), "fieldtype": "Dynamic Link",
 "options": "reference_type", "width": 180},
{"fieldname": "reference_type", "label": _("Type"), "fieldtype": "Data", "width": 120}