数据与分析
reports
Create reports in Frappe including Report Builder, Query Reports (SQL), and Script Reports (Python + JS). Use when building data analysis views, dashboards, or custom reporting features.
lubusIN · 已有静态检查记录
安装与使用
运行命令前先阅读来源与依赖说明。复制命令不会自动安装或执行。
npx skills add lubusIN/frappe-skills --skill reports技能说明
name: reports description: Create reports in Frappe including Report Builder, Query Reports (SQL), and Script Reports (Python + JS). Use when building data analysis views, dashboards, or custom reporting features.
Frappe Reports
Build reports using Report Builder, Query Reports (SQL), or 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
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)
Procedure
0) Choose report type
| 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 |
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
2) Query Report
Reports using raw SQL queries:
- Create Report → Type = "Query Report"
- Set Reference DocType (controls permissions)
- Write SQL query
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.
3) Script Report (standard)
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):
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):
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 customer
阅读完整来源 ↗