Frappe Syntax Reports

Reports: Query, Script & Report Builder

Quick Reference

Report Types at a Glance

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

Supported Filter Fieldtypes

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

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

Custom method signature:

@frappe.whitelist()
def get_total_active_users(filters=None):
    return frappe.db.count("User", {"enabled": 1})

Dashboard Charts

Dashboard Chart Source hook in hooks.py:

dashboard_chart_source = [
    "myapp.dashboard_chart_source.get_chart_data"
]

Critical Rules

• ALWAYS define columns as list of dicts with fieldname, label, fieldtype. The legacy string format is ONLY for Query Report SQL aliases.
• NEVER return None for columns or data in execute() — ALWAYS return empty lists [].
• ALWAYS use _(...) for translatable labels in columns and report_summary.
• NEVER use frappe.db.sql with user-supplied filter values directly in f-strings — ALWAYS pass as parameters: frappe.db.sql(query, filters, as_dict=True).
• ALWAYS set Reference DocType on the Report document — it controls user access permissions.
• NEVER omit width in column definitions — columns without width render poorly.
• ALWAYS match datasets[].values length to labels length in chart data — mismatched lengths cause chart rendering errors.

See Also

• references/query-report.md — Complete Query Report API
• references/script-report.md — Script Report JS API
• references/examples.md — Working report examples
• references/anti-patterns.md — Common report mistakes
• references/dashboard.md — Number Cards, Dashboard Charts

Common Report Anti-Patterns

AP-001: Wrong Column Format for Report Type

Problem

Using legacy string format in Script Reports or dict format in Query Report SQL aliases.

Wrong

# In a Script Report .py file — legacy string format does NOT work here
def execute(filters=None):
    columns = [
        "Customer:Link/Customer:200",
        "Amount:Currency:120"
    ]

Correct

# Script Reports ALWAYS use dict format
def execute(filters=None):
    columns = [
        {"fieldname": "customer", "label": _("Customer"),
         "fieldtype": "Link", "options": "Customer", "width": 200},
        {"fieldname": "amount", "label": _("Amount"),
         "fieldtype": "Currency", "width": 120}
    ]

Rule: ALWAYS use dict format for Script Reports. Legacy string format is ONLY for Query Report SQL aliases.

---

AP-002: Returning None Instead of Empty Lists

Problem

Returning None for columns or data crashes the report renderer.

Wrong

def execute(filters=None):
    if not filters.get("company"):
        return None, None  # Crashes!

Correct

def execute(filters=None):
    if not filters.get("company"):
        return [], []  # Safe empty return

Rule: ALWAYS return [] for empty columns and data — NEVER None.

---

AP-003: SQL Injection via String Formatting

Problem

Building SQL with f-strings or .format() using user-supplied filter values.

Wrong

def get_data(filters):
    # DANGEROUS — SQL injection vulnerability
    return frappe.db.sql(f"""
        SELECT name FROM `tabSales Order`
        WHERE customer = '{filters.get("customer")}'
    """, as_dict=True)

Correct

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

Rule: ALWAYS use %(param)s placeholders and pass filters dict to frappe.db.sql. NEVER interpolate user input into SQL strings.

---

AP-004: Missing docstatus Filter

Problem

Querying submitted documents without filtering by docstatus, which returns Draft and Cancelled documents.

Wrong

data = frappe.db.sql("""
    SELECT name, grand_total FROM `tabSales Invoice`
    WHERE customer = %(customer)s
""", filters, as_dict=True)

Correct

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

Rule: ALWAYS include docstatus = 1 when querying submitted (final) documents. Use docstatus < 2 to include both Draft and Submitted but exclude Cancelled.

---

AP-005: Mismatched Chart Labels and Values

Problem

Chart labels array and datasets[].values arrays have different lengths, causing rendering errors or blank charts.

Wrong

chart = {
    "data": {
        "labels": ["Jan", "Feb", "Mar"],      # 3 labels
        "datasets": [
            {"name": "Revenue", "values": [100, 200]}  # 2 values — MISMATCH
        ]
    },
    "type": "bar"
}

Correct

labels = [row.month for row in data]
values = [flt(row.amount) for row in data]

chart = {
    "data": {
        "labels": labels,       # Same source, guaranteed equal length
        "datasets": [
            {"name": _("Revenue"), "values": values}
        ]
    },
    "type": "bar"
}

Rule: ALWAYS derive labels and values from the same data source to guarantee equal length.

---

AP-006: Missing Column Width

Problem

Omitting width in column definitions causes columns to render too narrow or overlap.

Wrong

columns = [
    {"fieldname": "customer", "label": _("Customer"),
     "fieldtype": "Link", "options": "Customer"}
    # No width — renders poorly
]

Correct

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

Rule: ALWAYS specify width (in pixels) for every column definition.

---

AP-007: Missing Reference DocType

Problem

Creating a report without setting Reference DocType. The report is only visible to Administrators.

Wrong

Report document with no Reference DocType set.

Correct

ALWAYS set Reference DocType to the primary DocType the report queries. This controls:

• Who can see the report (users with read permission on that DocType)
• Where the report appears in the sidebar

Rule: ALWAYS set Reference DocType on every report.

---

AP-008: Using frappe.query_reports (Plural) for Instance Access

Problem

Confusing frappe.query_reports (the registry object) with frappe.query_report (the active instance).

Wrong

// This accesses the configuration object, NOT the running instance
frappe.query_reports["My Report"].refresh();  // Does nothing

Correct

// This accesses the active report instance
frappe.query_report.refresh();  // Actually refreshes
frappe.query_report.get_filter_value("company");  // Gets filter value

Rule: Use frappe.query_reports["Name"] ONLY for defining configuration. Use frappe.query_report (singular) for runtime operations.

---

AP-009: Not Handling DateRange Filter Correctly

Problem

DateRange filter returns a list [from_date, to_date], not individual date values. Using it directly in SQL fails.

Wrong

def execute(filters=None):
    # filters.date_range = ["2024-01-01", "2024-03-31"]
    data = frappe.db.sql("""
        SELECT name FROM `tabSales Order`
        WHERE transaction_date BETWEEN %(date_range)s  -- FAILS
    """, filters, as_dict=True)

Correct

def execute(filters=None):
    if filters.get("date_range"):
        filters["from_date"] = filters["date_range"][0]
        filters["to_date"] = filters["date_range"][1]

    data = frappe.db.sql("""
        SELECT name FROM `tabSales Order`
        WHERE transaction_date BETWEEN %(from_date)s AND %(to_date)s
    """, filters, as_dict=True)

Rule: ALWAYS unpack DateRange filters into separate from_date and to_date keys before using in SQL.

---

AP-010: Forgetting Translation Wrappers

Problem

Hardcoding English strings in labels without _() or __() makes reports untranslatable.

Wrong

columns = [
    {"fieldname": "customer", "label": "Customer", ...}  # Not translatable
]
report_summary = [
    {"value": total, "label": "Total Revenue", ...}  # Not translatable
]

Correct

columns = [
    {"fieldname": "customer", "label": _("Customer"), ...}
]
report_summary = [
    {"value": total, "label": _("Total Revenue"), ...}
]

// In JS files
{ fieldname: "customer", label: __("Customer"), ... }

Rule: ALWAYS wrap label strings with _() in Python and __() in JavaScript.

---

AP-011: Missing as_dict=True in SQL Queries

Problem

Forgetting as_dict=True when columns use dict format. Data comes back as tuples instead of dicts, and fieldnames do not map to column definitions.

Wrong

data = frappe.db.sql("""
    SELECT customer, grand_total FROM `tabSales Order`
""", filters)  # Returns list of tuples

Correct

data = frappe.db.sql("""
    SELECT customer, grand_total FROM `tabSales Order`
""", filters, as_dict=True)  # Returns list of dicts

Rule: When using dict-format columns, ALWAYS use as_dict=True in frappe.db.sql() so that fieldname keys in columns match the dict keys in data rows.

---

AP-012: Not Escaping Percent Signs in SQL with Date Functions

Problem

Python string formatting interprets % in SQL date functions as format specifiers.

Wrong

frappe.db.sql("""
    SELECT DATE_FORMAT(posting_date, '%Y-%m') as month
    FROM `tabSales Invoice`
""", filters, as_dict=True)
# Error: not enough arguments for format string

Correct

frappe.db.sql("""
    SELECT DATE_FORMAT(posting_date, '%%Y-%%m') as month
    FROM `tabSales Invoice`
""", filters, as_dict=True)
# Double %% escapes the percent sign

Rule: ALWAYS use %% to escape percent signs in SQL when using frappe.db.sql with parameters.

Number Cards, Dashboard Charts & Dashboards

Number Cards

Number Cards display a single aggregated value on the workspace or module page.

Three Source Types

1. Document Type Number Card

Runs an aggregate function on a DocType. Configuration:

Example — Count of open Sales Orders:

{
    "document_type": "Sales Order",
    "function": "Count",
    "filters_json": "[['Sales Order', 'docstatus', '=', 1], ['Sales Order', 'status', '!=', 'Completed']]"
}

Example — Sum of outstanding amount:

{
    "document_type": "Sales Invoice",
    "function": "Sum",
    "aggregate_function_based_on": "outstanding_amount",
    "filters_json": "[['Sales Invoice', 'docstatus', '=', 1]]"
}

2. Report Number Card

Pulls a value from a specific column of a report. Configuration:

Example — Total revenue from a report:

{
    "report_name": "Sales Analytics",
    "report_field": "total_amount",
    "function": "Sum",
    "filters_json": "{\"company\": \"My Company\"}"
}

3. Custom Method Number Card

Calls a whitelisted Python method that returns a number. Configuration:

Python method signature:

# In myapp/api.py
import frappe

@frappe.whitelist()
def get_active_user_count(filters=None):
    """Number Card calls this method. MUST return a numeric value."""
    return frappe.db.count("User", {"enabled": 1})

@frappe.whitelist()
def get_monthly_revenue(filters=None):
    """Accepts optional filters from the Number Card."""
    result = frappe.db.sql("""
        SELECT SUM(grand_total)
        FROM `tabSales Invoice`
        WHERE docstatus = 1
        AND MONTH(posting_date) = MONTH(CURDATE())
        AND YEAR(posting_date) = YEAR(CURDATE())
    """)
    return result[0][0] or 0

Number Card Formatting

Number Card Permissions

• Document Type cards: user MUST have read access to the DocType
• Report cards: user MUST have access to the report
• Custom Method cards: user MUST have read access to any DocType (basic desk access)

---

Dashboard Charts

Dashboard Charts display visual charts on workspaces and module pages.

Three Chart Sources

1. Report Chart

Uses data from a Script Report or Query Report:

2. Group By Chart

Auto-aggregates a DocType field:

Example — Sales Orders by status:

{
    "chart_type": "Group By",
    "document_type": "Sales Order",
    "group_by_type": "Count",
    "group_by_based_on": "status",
    "type": "Pie",
    "filters_json": "[['Sales Order', 'docstatus', '=', 1]]"
}

3. Custom Chart Source

Uses a Python function registered in hooks.py:

In hooks.py:

dashboard_chart_source = [
    "myapp.chart_sources.monthly_revenue.get_data"
]

Chart source function:

# myapp/chart_sources/monthly_revenue.py
import frappe
from frappe.utils import getdate, add_months

@frappe.whitelist()
def get_data(chart_name=None, chart=None, no_cache=None,
             filters=None, from_date=None, to_date=None,
             timespan=None, time_interval=None, heatmap_year=None):
    """
    MUST return dict with 'labels' and 'datasets' keys.
    """
    data = frappe.db.sql("""
        SELECT
            DATE_FORMAT(posting_date, '%%Y-%%m') as month,
            SUM(grand_total) as total
        FROM `tabSales Invoice`
        WHERE docstatus = 1
        GROUP BY month
        ORDER BY month
    """, as_dict=True)

    return {
        "labels": [row.month for row in data],
        "datasets": [
            {"name": "Revenue", "values": [row.total for row in data]}
        ]
    }

Chart Data Format

All Dashboard Charts consume this format:

{
    "labels": ["Jan", "Feb", "Mar", "Apr"],
    "datasets": [
        {
            "name": "Dataset 1",
            "values": [100, 200, 150, 300]
        },
        {
            "name": "Dataset 2",
            "values": [80, 150, 120, 250]
        }
    ]
}

For Heatmap charts:

{
    "labels": [],
    "dataPoints": {
        1704067200: 5,   # Unix timestamps as keys
        1704153600: 3,
        1704240000: 8
    }
}

Chart Types

Custom Chart Options

For advanced configuration, use custom_options (JSON string):

{
    "colors": ["#5e64ff", "#ffa00a", "#29cd42"],
    "barOptions": {
        "stacked": 1,
        "spaceRatio": 0.5
    },
    "lineOptions": {
        "regionFill": 1,
        "dotSize": 4
    },
    "axisOptions": {
        "xIsSeries": 1,
        "shortenYAxisNumbers": 1
    },
    "tooltipOptions": {
        "formatTooltipX": "d => d",
        "formatTooltipY": "d => d + ' units'"
    }
}

---

Dashboard Configuration

Dashboards group multiple charts and number cards on a single page.

Dashboard Document Fields

Adding Charts to Dashboard

Each row in the Dashboard Chart child table:

{
    "chart": "Monthly Revenue",    // Dashboard Chart name
    "width": "Full"                // "Full" or "Half"
}

Adding Number Cards to Dashboard

Each row in the Number Card child table:

{
    "card": "Active Users Count"   // Number Card name
}

Workspace Integration

Charts and Number Cards can also be added to Workspaces (v14+):

• Add a "Chart" or "Number Card" block to the workspace
• Select the Dashboard Chart or Number Card document
• Configure layout position and width

Dashboard Chart Cache

• Dashboard Charts are cached using cache_source decorator
• Cache key: "chart-data:{chart_name}"
• Cache clears on chart document update
• Force refresh with no_cache=True parameter

---

Critical Rules

• ALWAYS return a numeric value from Number Card custom methods — returning None shows "0" but returning a string crashes
• ALWAYS ensure Dashboard Chart source functions return {"labels": [...], "datasets": [...]} format — any other format causes blank charts
• NEVER create Number Cards without setting proper permissions — unauthorized users see errors instead of data
• ALWAYS use @frappe.whitelist() on custom methods for both Number Cards and Dashboard Chart sources
• ALWAYS match datasets[].values length to labels length in custom chart sources
• NEVER rely on Dashboard Chart cache for real-time data — use no_cache=True or set appropriate cache TTL

Working Report Examples

Example 1: Simple Query Report (SQL Only)

Report Configuration

• Report Type: Query Report
• Reference DocType: Sales Order

SQL Query

SELECT
  `tabSales Order`.name as "Sales Order:Link/Sales Order:200",
  `tabSales Order`.customer as "Customer:Link/Customer:180",
  `tabSales Order`.transaction_date as "Date:Date:100",
  `tabSales Order`.grand_total as "Grand Total:Currency:120",
  `tabSales Order`.status as "Status:Data:100"
FROM
  `tabSales Order`
WHERE
  `tabSales Order`.docstatus = 1
  AND `tabSales Order`.company = %(company)s
ORDER BY
  `tabSales Order`.transaction_date DESC
LIMIT 500

---

Example 2: Script Report with Chart and Summary

File: sales_analytics.py

import frappe
from frappe import _
from frappe.utils import flt, getdate, add_months


def execute(filters=None):
    columns = get_columns(filters)
    data = get_data(filters)
    chart = get_chart_data(data, filters)
    report_summary = get_report_summary(data, filters)

    return columns, data, None, chart, report_summary


def get_columns(filters):
    return [
        {
            "fieldname": "month",
            "label": _("Month"),
            "fieldtype": "Data",
            "width": 120
        },
        {
            "fieldname": "total_orders",
            "label": _("Total Orders"),
            "fieldtype": "Int",
            "width": 100
        },
        {
            "fieldname": "total_amount",
            "label": _("Total Amount"),
            "fieldtype": "Currency",
            "options": "Company:company:default_currency",
            "width": 150
        },
        {
            "fieldname": "avg_order_value",
            "label": _("Avg Order Value"),
            "fieldtype": "Currency",
            "options": "Company:company:default_currency",
            "width": 150
        }
    ]


def get_data(filters):
    data = frappe.db.sql("""
        SELECT
            DATE_FORMAT(transaction_date, '%%Y-%%m') as month,
            COUNT(name) as total_orders,
            SUM(grand_total) as total_amount,
            AVG(grand_total) as avg_order_value
        FROM
            `tabSales Order`
        WHERE
            docstatus = 1
            AND company = %(company)s
            AND transaction_date BETWEEN %(from_date)s AND %(to_date)s
        GROUP BY
            DATE_FORMAT(transaction_date, '%%Y-%%m')
        ORDER BY
            month
    """, filters, as_dict=True)

    return data


def get_chart_data(data, filters):
    if not data:
        return None

    labels = [row.month for row in data]
    amounts = [flt(row.total_amount) for row in data]
    orders = [row.total_orders for row in data]

    return {
        "data": {
            "labels": labels,
            "datasets": [
                {"name": _("Amount"), "values": amounts},
                {"name": _("Orders"), "values": orders}
            ]
        },
        "type": "bar",
        "fieldtype": "Currency",
        "colors": ["#5e64ff", "#ffa00a"]
    }


def get_report_summary(data, filters):
    if not data:
        return []

    total_amount = sum(flt(row.total_amount) for row in data)
    total_orders = sum(row.total_orders for row in data)
    avg_value = total_amount / total_orders if total_orders else 0

    return [
        {
            "value": total_amount,
            "label": _("Total Revenue"),
            "datatype": "Currency",
            "indicator": "Green"
        },
        {
            "value": total_orders,
            "label": _("Total Orders"),
            "datatype": "Int",
            "indicator": "Blue"
        },
        {
            "value": avg_value,
            "label": _("Average Order Value"),
            "datatype": "Currency",
            "indicator": "Blue"
        }
    ]

File: sales_analytics.js

frappe.query_reports["Sales Analytics"] = {
    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
        }
    ]
};

---

Example 3: Script Report with Tree View

File: account_tree.py

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


def execute(filters=None):
    columns = [
        {
            "fieldname": "account",
            "label": _("Account"),
            "fieldtype": "Link",
            "options": "Account",
            "width": 300
        },
        {
            "fieldname": "balance",
            "label": _("Balance"),
            "fieldtype": "Currency",
            "width": 150
        }
    ]

    data = get_data(filters)
    return columns, data


def get_data(filters):
    accounts = frappe.db.get_all("Account",
        filters={"company": filters.get("company")},
        fields=["name", "parent_account", "is_group", "lft", "rgt"],
        order_by="lft"
    )

    data = []
    for account in accounts:
        balance = get_balance(account.name, filters)
        indent = get_indent(account, accounts)
        data.append({
            "account": account.name,
            "balance": balance,
            "indent": indent,          # Required for tree mode
            "parent_account": account.parent_account,
            "bold": account.is_group   # Bold group accounts
        })

    return data

File: account_tree.js

frappe.query_reports["Account Tree"] = {
    tree: true,
    initial_depth: 2,
    parent_field: "parent_account",
    filters: [
        {
            fieldname: "company",
            label: __("Company"),
            fieldtype: "Link",
            options: "Company",
            default: frappe.defaults.get_user_default("company"),
            reqd: 1
        }
    ],
    formatter: function(value, row, column, data, default_formatter) {
        value = default_formatter(value, row, column, data);
        if (data && data.bold) {
            value = "<b>" + value + "</b>";
        }
        return value;
    }
};

---

Example 4: Grouped Report with DateRange Filter

File: customer_summary.py

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


def execute(filters=None):
    # DateRange filter comes as [from_date, to_date]
    if filters.get("date_range"):
        filters["from_date"] = filters["date_range"][0]
        filters["to_date"] = filters["date_range"][1]

    columns = [
        {"fieldname": "customer", "label": _("Customer"), "fieldtype": "Link",
         "options": "Customer", "width": 200},
        {"fieldname": "total_orders", "label": _("Orders"), "fieldtype": "Int",
         "width": 80},
        {"fieldname": "total_qty", "label": _("Total Qty"), "fieldtype": "Float",
         "width": 100},
        {"fieldname": "total_amount", "label": _("Total Amount"),
         "fieldtype": "Currency", "width": 150}
    ]

    data = frappe.db.sql("""
        SELECT
            customer,
            COUNT(name) as total_orders,
            SUM(total_qty) as total_qty,
            SUM(grand_total) as total_amount
        FROM `tabSales Order`
        WHERE
            docstatus = 1
            AND transaction_date BETWEEN %(from_date)s AND %(to_date)s
        GROUP BY customer
        ORDER BY total_amount DESC
    """, filters, as_dict=True)

    return columns, data

File: customer_summary.js

frappe.query_reports["Customer Summary"] = {
    filters: [
        {
            fieldname: "date_range",
            label: __("Date Range"),
            fieldtype: "DateRange",
            default: [
                frappe.datetime.add_months(frappe.datetime.get_today(), -3),
                frappe.datetime.get_today()
            ],
            reqd: 1
        }
    ]
};

---

Example 5: Report with Dynamic Link and Custom Buttons

File: party_ledger.py

import frappe
from frappe import _


def execute(filters=None):
    columns = [
        {"fieldname": "posting_date", "label": _("Date"), "fieldtype": "Date",
         "width": 100},
        {"fieldname": "voucher_type", "label": _("Voucher Type"),
         "fieldtype": "Data", "width": 120},
        {"fieldname": "voucher_no", "label": _("Voucher No"),
         "fieldtype": "Dynamic Link", "options": "voucher_type", "width": 180},
        {"fieldname": "debit", "label": _("Debit"), "fieldtype": "Currency",
         "width": 120},
        {"fieldname": "credit", "label": _("Credit"), "fieldtype": "Currency",
         "width": 120},
        {"fieldname": "balance", "label": _("Balance"), "fieldtype": "Currency",
         "width": 120}
    ]

    data = get_gl_entries(filters)

    # Calculate running balance
    balance = 0
    for row in data:
        balance += flt(row.debit) - flt(row.credit)
        row["balance"] = balance

    return columns, data

File: party_ledger.js

frappe.query_reports["Party Ledger"] = {
    filters: [
        {
            fieldname: "party_type",
            label: __("Party Type"),
            fieldtype: "Link",
            options: "DocType",
            default: "Customer",
            reqd: 1,
            get_query: function() {
                return {
                    filters: { name: ["in", ["Customer", "Supplier", "Employee"]] }
                };
            }
        },
        {
            fieldname: "party",
            label: __("Party"),
            fieldtype: "Dynamic Link",
            options: "party_type",
            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
        }
    ],
    onload: function(report) {
        report.page.add_inner_button(__("Print Statement"), function() {
            let filters = report.get_filter_values();
            frappe.call({
                method: "myapp.api.get_print_statement",
                args: { filters: filters }
            });
        });
    }
};

---

Example 6: Custom Report (No App Required)

For System Managers who need a quick report without deploying an app:

1. Create new Report, set Type = "Script Report" 2. Leave "Is Standard" = "No" (this makes it a Custom Report) 3. Write Python directly in the Script field:

# This goes in the Report document's Script field
result = frappe.db.get_all("Sales Invoice",
    filters={
        "docstatus": 1,
        "posting_date": ["between", [filters.from_date, filters.to_date]]
    },
    fields=["customer", "posting_date", "grand_total", "status"],
    order_by="posting_date desc",
    limit_page_length=0
)

# For custom reports, just set columns and data directly
columns = [
    {"fieldname": "customer", "label": "Customer", "fieldtype": "Link",
     "options": "Customer", "width": 200},
    {"fieldname": "posting_date", "label": "Date", "fieldtype": "Date",
     "width": 100},
    {"fieldname": "grand_total", "label": "Total", "fieldtype": "Currency",
     "width": 120},
    {"fieldname": "status", "label": "Status", "fieldtype": "Data",
     "width": 100}
]
data = result

4. Add filters in the Filters table of the Report document 5. No .py or .js files needed — everything is in the database

Query Report API Reference

Overview

Query Reports execute SQL directly against the database. They are stored in the Report document and do NOT require Developer Mode. System Manager role is required to create them.

Creating a Query Report

1. Navigate to "New Report" via the awesomebar 2. Set Report Type = "Query Report" 3. Set Reference DocType (controls who can access the report) 4. Set Module (determines sidebar placement) 5. Write SQL in the Query field

SQL Query with Legacy Column Format

The column format is embedded in SQL aliases:

SELECT
  `tabSales Order`.name as "Sales Order:Link/Sales Order:200",
  `tabSales Order`.customer as "Customer:Link/Customer:180",
  `tabSales Order`.transaction_date as "Date:Date:100",
  `tabSales Order`.grand_total as "Grand Total:Currency:120",
  `tabSales Order`.status as "Status:Data:100",
  `tabSales Order`.per_delivered as "Delivered %:Percent:100",
  `tabSales Order`.company as "Company:Link/Company:150"
FROM
  `tabSales Order`
WHERE
  `tabSales Order`.docstatus = 1
ORDER BY
  `tabSales Order`.transaction_date DESC

Column Alias Format

"Label:Fieldtype/Options:Width"

Examples by Fieldtype

-- Link column
name as "Invoice:Link/Sales Invoice:200"

-- Currency column
grand_total as "Total:Currency:120"

-- Date column
posting_date as "Date:Date:100"

-- Integer column
qty as "Quantity:Int:80"

-- Float column
rate as "Rate:Float:100"

-- Percent column
per_billed as "Billed %:Percent:80"

-- Plain data
customer_name as "Customer Name:Data:200"

-- Check (boolean)
is_return as "Is Return:Check:60"

Filters in Query Reports

SQL Placeholder Filters

Use %(filter_name)s placeholders in SQL:

SELECT
  name as "Work Order:Link/Work Order:200",
  production_item as "Item:Link/Item:150",
  qty as "Qty:Int:80"
FROM
  `tabWork Order`
WHERE
  docstatus = 1
  AND company = %(company)s
  AND production_item LIKE %(item)s
ORDER BY creation DESC

Filter Definition in JS File

Create a .js file alongside the Report document (for Standard reports):

frappe.query_reports["My Query Report"] = {
    filters: [
        {
            fieldname: "company",
            label: __("Company"),
            fieldtype: "Link",
            options: "Company",
            default: frappe.defaults.get_user_default("company"),
            reqd: 1
        },
        {
            fieldname: "item",
            label: __("Item"),
            fieldtype: "Link",
            options: "Item"
        }
    ]
};

Filter with Wildcard Default

{
    fieldname: "item",
    label: __("Item"),
    fieldtype: "Link",
    options: "Item",
    default: "%"  // matches all when no selection
}

Query Report with Python Script

For complex Query Reports that need Python logic, create a .py file:

import frappe

def execute(filters=None):
    conditions = get_conditions(filters)
    columns = get_columns()
    data = frappe.db.sql("""
        SELECT
            so.name, so.customer, so.grand_total
        FROM
            `tabSales Order` so
        WHERE
            so.docstatus = 1
            {conditions}
        ORDER BY so.creation DESC
    """.format(conditions=conditions), filters, as_dict=True)

    return columns, data

def get_columns():
    return [
        {
            "fieldname": "name",
            "label": "Sales Order",
            "fieldtype": "Link",
            "options": "Sales Order",
            "width": 200
        },
        {
            "fieldname": "customer",
            "label": "Customer",
            "fieldtype": "Link",
            "options": "Customer",
            "width": 180
        },
        {
            "fieldname": "grand_total",
            "label": "Grand Total",
            "fieldtype": "Currency",
            "width": 120
        }
    ]

def get_conditions(filters):
    conditions = ""
    if filters.get("company"):
        conditions += " AND so.company = %(company)s"
    if filters.get("customer"):
        conditions += " AND so.customer = %(customer)s"
    return conditions

Column Definition in Report Document (v13+)

Since Frappe v13, you can define columns directly in the Report document UI:

• Add rows to the Columns table
• Set Label, Fieldtype, Width, Options per column
• This replaces the need for legacy string format in SQL aliases

When using this approach, your SQL SELECT column names MUST match the fieldnames configured in the Columns table.

Permissions

• Query Reports inherit permissions from the Reference DocType
• Users who can read the Reference DocType can run the report
• System Manager role is required to CREATE Query Reports
• ALWAYS set a Reference DocType — reports without one are only visible to Administrators

Critical Rules

• ALWAYS use parameterized queries with %(filter)s — NEVER concatenate user input into SQL strings
• ALWAYS filter by docstatus when querying submitted documents — omitting it returns Draft documents
• NEVER use SELECT * — ALWAYS specify exact columns needed
• ALWAYS prefix table names with tab in backticks: ` tabSales Order `
• NEVER forget the backticks around table names with spaces

Script Report JS API Reference

Overview

Script Reports have two files:

• {report_name}.py — Server-side execute() function
• {report_name}.js — Client-side filters, hooks, and formatting

JS File Structure

frappe.query_reports["Report Name"] = {
    // Filters array (required)
    filters: [...],

    // Lifecycle hooks
    onload: function(report) { },
    after_datatable_render: function(datatable) { },

    // Display configuration
    formatter: function(value, row, column, data, default_formatter) { },
    get_datatable_options: function(options) { },

    // Tree mode
    tree: false,
    initial_depth: 1,
    parent_field: "parent_account",

    // Export
    export_hidden_cols: false
};

Filter Configuration

All Filter Properties

{
    fieldname: "company",         // Required: maps to filters dict key
    label: __("Company"),         // Required: display label
    fieldtype: "Link",            // Required: input type
    options: "Company",           // Required for Link/Select
    default: "My Company",       // Optional: default value
    reqd: 1,                     // Optional: mandatory filter
    hidden: 0,                   // Optional: hide from UI
    read_only: 0,                // Optional: non-editable
    width: "80px",               // Optional: filter width
    get_query: function() {      // Optional: custom query for Link
        return {
            filters: { "is_group": 0 }
        };
    },
    on_change: function() {      // Optional: callback on value change
        frappe.query_report.refresh();
    },
    depends_on: 'eval:doc.company=="My Company"'  // Optional: conditional display
}

Filter Fieldtype Examples

// Link — autocomplete from DocType
{ fieldname: "customer", fieldtype: "Link", options: "Customer" }

// Select — dropdown
{ fieldname: "status", fieldtype: "Select",
  options: "\nDraft\nSubmitted\nCancelled" }  // leading \n for blank option

// Date
{ fieldname: "from_date", fieldtype: "Date",
  default: frappe.datetime.add_months(frappe.datetime.get_today(), -1) }

// DateRange — returns [from_date, to_date]
{ fieldname: "date_range", fieldtype: "DateRange",
  default: [frappe.datetime.add_months(frappe.datetime.get_today(), -1),
            frappe.datetime.get_today()] }

// Check — boolean
{ fieldname: "show_cancelled", fieldtype: "Check", default: 0 }

// Dynamic Link — depends on another filter
{ fieldname: "party_type", fieldtype: "Link", options: "DocType",
  get_query: function() {
      return { filters: { name: ["in", ["Customer", "Supplier"]] } };
  }
},
{ fieldname: "party", fieldtype: "Dynamic Link", options: "party_type" }

// MultiSelectList
{ fieldname: "warehouses", fieldtype: "MultiSelectList",
  get_data: function(txt) {
      return frappe.db.get_link_options("Warehouse", txt);
  }
}

// Int
{ fieldname: "limit", fieldtype: "Int", default: 20 }

Lifecycle Hooks

onload

Fires once when report loads. Use for dynamic filter setup:

onload: function(report) {
    // Add custom button
    report.page.add_inner_button(__("Download PDF"), function() {
        // custom action
    });

    // Modify filters dynamically
    let company_filter = report.get_filter("company");
    company_filter.df.default = "Default Company";
}

after_datatable_render

Fires after the DataTable renders. Use for post-render modifications:

after_datatable_render: function(datatable) {
    // Highlight specific rows
    $(datatable.wrapper).find(".dt-row").each(function() {
        // custom styling
    });
}

Formatter

Custom cell formatting. Return HTML string:

formatter: function(value, row, column, data, default_formatter) {
    value = default_formatter(value, row, column, data);

    if (column.fieldname === "balance" && data && data.balance < 0) {
        value = "<span style='color:red'>" + value + "</span>";
    }

    if (data && data.bold) {
        value = "<b>" + value + "</b>";
    }

    return value;
}

DataTable Options

Customize the DataTable rendering:

get_datatable_options: function(options) {
    return Object.assign(options, {
        checkboxColumn: true,
        noDataMessage: __("No records found"),
        dynamicRowHeight: true
    });
}

Tree Mode

Enable hierarchical display:

frappe.query_reports["Account Balance"] = {
    tree: true,
    initial_depth: 3,          // levels expanded by default
    parent_field: "parent_account",  // field linking to parent row
    filters: [...]
};

For tree mode, execute() must return data with:

• An indent field (integer, 0 = root level)
• Rows ordered so children appear after their parent

Accessing Report from JS

// Refresh report
frappe.query_report.refresh();

// Get filter value
let company = frappe.query_report.get_filter_value("company");

// Set filter value
frappe.query_report.set_filter_value("company", "My Company");

// Get all filter values
let filters = frappe.query_report.get_filter_values();

// Toggle filter visibility
frappe.query_report.toggle_filter_display("status", true);  // hide

Custom Buttons and Actions

onload: function(report) {
    report.page.add_inner_button(__("Create Invoice"), function() {
        let checked = report.datatable.getCheckedRowIndexes();
        if (checked.length === 0) {
            frappe.throw(__("Select at least one row"));
            return;
        }
        // process checked rows
        let selected_data = checked.map(i => report.data[i]);
        // call server method
        frappe.call({
            method: "myapp.api.create_invoices",
            args: { rows: selected_data },
            callback: function(r) {
                frappe.msgprint(__("Invoices created"));
                report.refresh();
            }
        });
    });
}

Critical Rules

• ALWAYS use __() for translatable strings in JS filters and labels
• ALWAYS call default_formatter(value, row, column, data) first in custom formatters — then modify the result
• NEVER mutate data directly in the formatter — it causes rendering bugs
• ALWAYS return a value from the formatter function — returning undefined blanks the cell
• NEVER use frappe.query_reports (plural) for accessing the current report instance — use frappe.query_report (singular)