frappe-reports

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Frappe Reports

Frappe 报表

Build reports using Report Builder, Query Reports (SQL), or Script Reports (Python + JS).

使用Report Builder、Query Reports（SQL）或Script Reports（Python + JS）构建报表。

When to use

适用场景

Creating data analysis or summary reports
Building SQL-based query reports
Implementing complex reports with Python logic and JS UI
Adding custom filters, formatters, and charts to reports
Creating printable report formats

创建数据分析或汇总报表
构建基于SQL的查询报表
实现包含Python逻辑与JS界面的复杂报表
为报表添加自定义筛选器、格式化器和图表
创建可打印的报表格式

Inputs required

所需输入

Report purpose and data requirements
Source DocType(s) for the report
Filter requirements
Column definitions (fields, types, formatting)
Whether report is standard (app-bundled) or custom (site-specific)

报表用途与数据需求
报表的源DocType(s)
筛选器需求
列定义（字段、类型、格式）
报表是标准型（应用捆绑）还是自定义型（站点专属）

Procedure

操作步骤

0) Choose report type

0) 选择报表类型

Type	Complexity	Code Required	Best For
Report Builder	Low	None	Simple field selection, grouping, sorting
Query Report	Medium	SQL only	Direct SQL queries, joins, aggregations
Script Report	High	Python + JS	Complex logic, computed fields, dynamic filters

类型	复杂度	是否需要代码	最佳适用场景
Report Builder	低	无需	简单字段选择、分组、排序
Query Report	中	仅需SQL	直接SQL查询、关联、聚合
Script Report	高	Python + JS	复杂逻辑、计算字段、动态筛选器

1) Report Builder

Create via UI with no code:

Navigate to the Report list → New Report
Select Reference DocType
Choose Report Type = "Report Builder"
Add columns, filters, sorting, and grouping via the builder UI

通过UI界面无代码创建：

导航至报表列表 → 新建报表
选择参考DocType
选择报表类型 = "Report Builder"
通过构建器UI添加列、筛选器、排序和分组规则

2) Query Report

Reports using raw SQL queries:

Create Report → Type = "Query Report"
Set Reference DocType (controls permissions)
Write SQL query

sql

SELECT
    `tabSales Order`.name AS "Sales Order:Link/Sales Order:200",
    `tabSales Order`.customer AS "Customer:Link/Customer:200",
    `tabSales Order`.transaction_date AS "Date:Date:120",
    `tabSales Order`.grand_total AS "Grand Total:Currency:150",
    `tabSales Order`.status AS "Status:Data:100"
FROM `tabSales Order`
WHERE `tabSales Order`.docstatus = 1
    {% if filters.company %}
    AND `tabSales Order`.company = %(company)s
    {% endif %}
    {% if filters.from_date %}
    AND `tabSales Order`.transaction_date >= %(from_date)s
    {% endif %}
ORDER BY `tabSales Order`.transaction_date DESC

Column format in SELECT:

"Label:Fieldtype/Options:Width"

Fieldtype	Example
Link	`"Customer:Link/Customer:200"`
Currency	`"Amount:Currency:150"`
Date	`"Date:Date:120"`
Int	`"Quantity:Int:100"`
Data	`"Status:Data:100"`

Filter variables: Use

%(filter_name)s

for parameterized queries.

使用原生SQL查询的报表：

创建报表 → 类型 = "Query Report"
设置参考DocType（控制权限）
编写SQL查询

sql

SELECT
    `tabSales Order`.name AS "Sales Order:Link/Sales Order:200",
    `tabSales Order`.customer AS "Customer:Link/Customer:200",
    `tabSales Order`.transaction_date AS "Date:Date:120",
    `tabSales Order`.grand_total AS "Grand Total:Currency:150",
    `tabSales Order`.status AS "Status:Data:100"
FROM `tabSales Order`
WHERE `tabSales Order`.docstatus = 1
    {% if filters.company %}
    AND `tabSales Order`.company = %(company)s
    {% endif %}
    {% if filters.from_date %}
    AND `tabSales Order`.transaction_date >= %(from_date)s
    {% endif %}
ORDER BY `tabSales Order`.transaction_date DESC

SELECT语句中的列格式：

"标签:字段类型/选项:宽度"

字段类型	示例
Link	`"Customer:Link/Customer:200"`
Currency	`"Amount:Currency:150"`
Date	`"Date:Date:120"`
Int	`"Quantity:Int:100"`
Data	`"Status:Data:100"`

筛选器变量：使用

%(filter_name)s

实现参数化查询。

3) Script Report (standard)

3) Script Report（标准型）

For app-bundled reports with full Python + JS control:

Create the report structure:

my_app/
└── my_module/
    └── report/
        └── sales_summary/
            ├── sales_summary.json    # Report metadata
            ├── sales_summary.py      # Python data logic
            └── sales_summary.js      # JS filters and UI

Python script (

sales_summary.py

python

import frappe
from frappe import _

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

def get_columns():
    return [
        {
            "label": _("Customer"),
            "fieldname": "customer",
            "fieldtype": "Link",
            "options": "Customer",
            "width": 200
        },
        {
            "label": _("Total Orders"),
            "fieldname": "total_orders",
            "fieldtype": "Int",
            "width": 120
        },
        {
            "label": _("Total Amount"),
            "fieldname": "total_amount",
            "fieldtype": "Currency",
            "width": 150
        },
        {
            "label": _("Average Order"),
            "fieldname": "avg_order",
            "fieldtype": "Currency",
            "width": 150
        }
    ]

def get_data(filters):
    conditions = get_conditions(filters)

    data = frappe.db.sql("""
        SELECT
            customer,
            COUNT(name) as total_orders,
            SUM(grand_total) as total_amount,
            AVG(grand_total) as avg_order
        FROM `tabSales Order`
        WHERE docstatus = 1 {conditions}
        GROUP BY customer
        ORDER BY total_amount DESC
    """.format(conditions=conditions), filters, as_dict=True)

    return data

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

def get_chart(data):
    if not data:
        return None

    return {
        "data": {
            "labels": [d.customer for d in data[:10]],
            "datasets": [{
                "name": _("Total Amount"),
                "values": [d.total_amount for d in data[:10]]
            }]
        },
        "type": "bar"
    }

JavaScript script (

sales_summary.js

javascript

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)
        },
        {
            fieldname: "to_date",
            label: __("To Date"),
            fieldtype: "Date",
            default: frappe.datetime.get_today()
        }
    ],

    onload(report) {
        // Custom initialization
    },

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

        // Highlight high-value customers
        if (column.fieldname === "total_amount" && data.total_amount > 100000) {
            value = `<span style="color: green; font-weight: bold">${value}</span>`;
        }

        return value;
    }
};

Report JSON (

sales_summary.json

json

{
    "name": "Sales Summary",
    "doctype": "Report",
    "report_type": "Script Report",
    "ref_doctype": "Sales Order",
    "module": "My Module",
    "is_standard": "Yes",
    "disabled": 0
}

适用于应用捆绑式报表，支持完整的Python + JS控制：

创建报表结构：

my_app/
└── my_module/
    └── report/
        └── sales_summary/
            ├── sales_summary.json    # 报表元数据
            ├── sales_summary.py      # Python数据逻辑
            └── sales_summary.js      # JS筛选器与界面

Python脚本 (

sales_summary.py

python

import frappe
from frappe import _

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

def get_columns():
    return [
        {
            "label": _("Customer"),
            "fieldname": "customer",
            "fieldtype": "Link",
            "options": "Customer",
            "width": 200
        },
        {
            "label": _("Total Orders"),
            "fieldname": "total_orders",
            "fieldtype": "Int",
            "width": 120
        },
        {
            "label": _("Total Amount"),
            "fieldname": "total_amount",
            "fieldtype": "Currency",
            "width": 150
        },
        {
            "label": _("Average Order"),
            "fieldname": "avg_order",
            "fieldtype": "Currency",
            "width": 150
        }
    ]

def get_data(filters):
    conditions = get_conditions(filters)

    data = frappe.db.sql("""
        SELECT
            customer,
            COUNT(name) as total_orders,
            SUM(grand_total) as total_amount,
            AVG(grand_total) as avg_order
        FROM `tabSales Order`
        WHERE docstatus = 1 {conditions}
        GROUP BY customer
        ORDER BY total_amount DESC
    """.format(conditions=conditions), filters, as_dict=True)

    return data

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

def get_chart(data):
    if not data:
        return None

    return {
        "data": {
            "labels": [d.customer for d in data[:10]],
            "datasets": [{
                "name": _("Total Amount"),
                "values": [d.total_amount for d in data[:10]]
            }]
        },
        "type": "bar"
    }

JavaScript脚本 (

sales_summary.js

javascript

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)
        },
        {
            fieldname: "to_date",
            label: __("To Date"),
            fieldtype: "Date",
            default: frappe.datetime.get_today()
        }
    ],

    onload(report) {
        // 自定义初始化逻辑
    },

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

        // 高亮高价值客户
        if (column.fieldname === "total_amount" && data.total_amount > 100000) {
            value = `<span style="color: green; font-weight: bold">${value}</span>`;
        }

        return value;
    }
};

报表JSON配置 (

sales_summary.json

json

{
    "name": "Sales Summary",
    "doctype": "Report",
    "report_type": "Script Report",
    "ref_doctype": "Sales Order",
    "module": "My Module",
    "is_standard": "Yes",
    "disabled": 0
}

4) Add report print format

4) 添加报表打印格式

Create

sales_summary.html

in the report folder for a custom print layout:

html

<h2>Sales Summary Report</h2>
<table class="table table-bordered">
    <tr>
        <th>Customer</th>
        <th>Orders</th>
        <th>Total</th>
    </tr>
    {% for row in data %}
    <tr>
        <td>{{ row.customer }}</td>
        <td>{{ row.total_orders }}</td>
        <td>{{ frappe.format(row.total_amount, {fieldtype: 'Currency'}) }}</td>
    </tr>
    {% endfor %}
</table>

在报表文件夹中创建

sales_summary.html

以自定义打印布局：

html

<h2>Sales Summary Report</h2>
<table class="table table-bordered">
    <tr>
        <th>Customer</th>
        <th>Orders</th>
        <th>Total</th>
    </tr>
    {% for row in data %}
    <tr>
        <td>{{ row.customer }}</td>
        <td>{{ row.total_orders }}</td>
        <td>{{ frappe.format(row.total_amount, {fieldtype: 'Currency'}) }}</td>
    </tr>
    {% endfor %}
</table>

5) Register report in hooks (optional)

5) 在钩子中注册报表（可选）

Reports are auto-discovered if they follow the standard directory structure. No

hooks.py

entry is needed for standard reports.

如果报表遵循标准目录结构，会被自动发现。标准报表无需在

hooks.py

中添加条目。

Verification

验证项

Failure modes / debugging

故障模式与调试

Report not found: Check module path and
```
is_standard
```
setting; run
```
bench migrate
```
SQL syntax error: Test query in
```
bench --site <site> mariadb
```
first
No data returned: Check
```
docstatus
```
filter; verify filters match data
Permission denied: Verify Reference DocType permissions for the user's role
Slow query: Add indexes; use Query Builder; limit result set

报表未找到：检查模块路径与
```
is_standard
```
设置；执行
```
bench migrate
```
SQL语法错误：先在
```
bench --site <site> mariadb
```
中测试查询
无数据返回：检查
```
docstatus
```
筛选器；验证筛选器与数据是否匹配
权限拒绝：验证用户角色是否拥有参考DocType的权限
查询缓慢：添加索引；使用查询构建器；限制结果集

Escalation

问题升级

For DocType schema →
```
frappe-doctype-development
```
For API endpoints (report data via API) →
```
frappe-api-development
```
For Desk UI customization →
```
frappe-desk-customization
```

关于DocType架构 →
```
frappe-doctype-development
```
关于API端点（通过API获取报表数据） →
```
frappe-api-development
```
关于桌面UI自定义 →
```
frappe-desk-customization
```

References

参考资料

references/reports.md — Report types, creation, and examples

references/reports.md — 报表类型、创建方法与示例

Guardrails

防护准则

Validate filters: Check filter values before building queries; handle empty/invalid input
Handle empty results: Always handle case where query returns no data; show appropriate message
Use
frappe.db.escape()
: Escape user input in SQL queries to prevent injection
Limit result sets: Add LIMIT clause or pagination for large datasets
Check permissions in execute: Verify user has permission to see the data

验证筛选器：构建查询前检查筛选器值；处理空值/无效输入
处理空结果：始终处理查询无返回数据的情况；显示合适提示信息
使用
frappe.db.escape()
：在SQL查询中转义用户输入以防止注入
限制结果集：为大型数据集添加LIMIT子句或分页
在execute中检查权限：验证用户是否有权查看数据

Common Mistakes

常见错误

Mistake	Why It Fails	Fix
SQL injection via filters	Security vulnerability	Use `frappe.db.escape()` or Query Builder with parameters
Missing permission checks	Unauthorized data access	Verify `frappe.has_permission()` or filter by allowed records
Unbounded queries	Timeouts, memory issues	Add `LIMIT` , use pagination, or filter by date range
Wrong column fieldtype	Formatting issues	Match column `fieldtype` to data (Currency, Date, etc.)
Not handling None in aggregations	Errors or wrong totals	Use `COALESCE()` or `IFNULL()` in SQL
Hardcoded `docstatus` assumptions	Missing draft/cancelled records	Explicitly filter `docstatus` based on report needs

错误	失败原因	修复方案
通过筛选器注入SQL	安全漏洞	使用 `frappe.db.escape()` 或带参数的查询构建器
缺失权限检查	未授权数据访问	验证 `frappe.has_permission()` 或按允许记录筛选
无边界查询	超时、内存问题	添加 `LIMIT` 、使用分页或按日期范围筛选
列字段类型错误	格式问题	列 `fieldtype` 与数据类型匹配（货币、日期等）
聚合时未处理None	错误或错误的总计	在SQL中使用 `COALESCE()` 或 `IFNULL()`
硬编码 `docstatus` 假设	缺失草稿/已取消记录	根据报表需求显式筛选 `docstatus`