How to Create Custom Reports in Frappe
This comprehensive guide explains how to create custom reports in Frappe Framework, based on analysis of real-world examples from my experience. Frappe
supports three main types of reports: Script Reports , Query Reports and Report Builder.
Frappe lets you build custom reports to display data from your system. Two common types are Script Reports and Query Reports. Script Reports use Python code to
prepare data, while Query Reports use a raw SQL query. This guide explains how to create each type, what files are involved, and includes simple code examples. It
assumes you have Developer Mode enabled and basic knowledge of Frappe/App structure.
Table of Contents
1. Report Types Overview
2. File Structure
3. Script Report: Step-by-Step
4. Query Report: Step-by-Step
5. Key Files and Their Roles
6. Advanced Features
7. Best Practices
8. Column Format Specifications
1. Report Types Overview
Script Report
Most flexible and powerful
Uses Python for data processing and complex logic
Supports dynamic columns, custom calculations, and data aggregation
Requires Python (.py) file for backend logic
Optional JavaScript (.js) file for frontend filters and interactions
Query Report
Simple and direct
Uses raw SQL queries defined in JSON configuration
Best for straightforward data extraction
No Python file needed
Optional JavaScript (.js) file for filters
Report Builder
Visual/GUI-based
Created through Frappe's web interface
Uses JSON configuration with visual settings
Limited customization but user-friendly
No coding required
2. File Structure
Every custom report follows this directory structure:
apps/[app_name]/[app_name]/[module]/report/[report_name]/
├── __init__.py # Required Python package file
├── [report_name].json # Report configuration (required)
├── [report_name].py # Python logic (Script Reports only)
└── [report_name].js # Frontend filters/logic (optional)
3. Script Report: Step-by-Step
Script Reports are reports written in Python. They give you full flexibility (you can run complex logic, multiple queries, charts, etc.), but require Administrator rights and
Developer Mode.
It basically depends on: [python Doc datatype + SQL Query] that's it, literally that's it
Step 1: Create Report via UI
1. Enable Developer Mode and log in as an Administrator.
2. Go to Report List in your app
3. Click New Report
4. Fill basic details:
Report Name: "My Custom Report"
Module: Select your target module
Report Type: "Script Report"
Reference DocType: Base doctype for the report (that we will Query data from)
Set Is Standard = Yes
5. Save the report. Frappe will create a folder in your app with boilerplate files.
6. report_name.py : implement execute(filters=None) function.
7. report_name.js : (Optional) define any filters.
Key files for a Script Report
report_name.py – Contains the Python logic.
report_name.js – (Optional) Defines filters.
report_name.json – Metadata (auto-generated).
Quick Example – Simple Script Report
def execute(filters=None):
columns = [
{"fieldname": "account", "label": "Account", "fieldtype": "Link", "options": "Account"},
{"fieldname": "balance", "label": "Balance", "fieldtype": "Currency"}
]
data = [
{"account": "Cash", "balance": 100},
{"account": "Bank", "balance": 250}
]
return columns, data
Quick Example Filter (JS)
frappe.query_reports["My Report"] = {
filters: [
{
fieldname: "start_date",
label: __("Start Date"),
fieldtype: "Date"
},
{
fieldname: "end_date",
label: __("End Date"),
fieldtype: "Date"
}
]
};
Step 2: Create Python Logic
File: [report_name].py
# Copyright (c) 2024, Your Company and contributors
# For license information, please see license.txt
import frappe
from frappe import _
def execute(filters=None):
"""Main entry point for the report"""
filters = frappe._dict(filters or {})
columns = get_columns(filters)
data = get_data(filters)
return columns, data
def get_columns(filters):
"""Define report columns"""
return [
{
"label": _("ID"),
"fieldtype": "Link",
"fieldname": "name",
"options": "Your DocType",
"width": 120,
},
{
"label": _("Status"),
"fieldtype": "Data",
"fieldname": "status",
"width": 100,
},
{
"label": _("Amount"),
"fieldtype": "Currency",
"fieldname": "amount",
"width": 120,
}
]
def get_data(filters):
"""Fetch and process data"""
conditions = []
values = {}
if filters.get("company"):
conditions.append("company = %(company)s")
values["company"] = filters.company
where_clause = " AND ".join(conditions)
if where_clause:
where_clause = "WHERE " + where_clause
query = f"""
SELECT
name,
status,
amount
FROM `tabYour DocType`
{where_clause}
ORDER BY creation DESC
"""
return frappe.db.sql(query, values=values, as_dict=True)
Step 4: Create JavaScript Filters (Optional)
File: [report_name].js
// Copyright (c) 2024, Your Company and contributors
// For license information, please see license.txt
frappe.query_reports["My Custom 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.year_start(),
reqd: 1,
},
{
fieldname: "to_date",
label: __("To Date"),
fieldtype: "Date",
default: frappe.datetime.year_end(),
reqd: 1,
}
]
};
4. Query Report: Step-by-Step
Query Reports use SQL queries to generate reports.
Steps to create a Query Report
Same as Script Report, but select "Query Report" as Report Type.
1. Go to “New Report” and choose:
Report Type = “Query Report”
Reference DocType
Module
2. Enter SQL in the “Query” field.
3. (Optional) Add filters via report_name.js
4. Use %(filter)s syntax to inject filter values in SQL.
Example – Simple Query Report
SELECT
name AS "Name:Link/Item:200",
stock_uom AS "UOM:Data:100",
ifnull(sum(bin.actual_qty),0) AS "Stock:Float:100"
FROM `tabItem`
LEFT JOIN `tabBin` bin ON bin.item_code = `tabItem`.name
GROUP BY `tabItem`.name, `tabItem`.stock_uom;
Example Filter (JS)
frappe.query_reports["My Query Report"] = {
filters: [
{
fieldname: "item_code",
label: __("Item Code"),
fieldtype: "Link",
options: "Item"
},
{
fieldname: "status",
label: __("Status"),
fieldtype: "Select",
options: ["Open", "Closed"],
default: "Open"
}
]
};
5. Key Files and Their Roles
JSON Configuration File (.json)
Purpose: Defines report metadata and configuration
Required for: All report types
Key fields:
report_type : "Script Report", "Query Report", or "Report Builder"
ref_doctype : Base DocType for the report
query : SQL query (Query Reports only)
roles : User roles that can access the report
add_total_row : Whether to add totals row
Python Logic File (.py)
Purpose: Contains backend logic for data processing
Required for: Script Reports only
Key functions :
execute(filters) : Main entry point, returns (columns, data)
get_columns(filters) : Defines report columns
get_data(filters) : Fetches and processes data
JavaScript File (.js)
Purpose: Defines frontend filters and interactions
Required for: Optional for all report types
Key features :
Filter definitions
Dynamic filter behavior
Custom formatting
Event handlers
Package File (init.py)
Purpose: Makes directory a Python package
Required for: All reports
Content: Usually empty
6. Advanced Features
Dynamic Columns
def get_columns(filters, project_types):
columns = [
{"label": _("Product"), "fieldtype": "Data", "fieldname": "product"}
]
# Add dynamic columns based on data
for project_type in project_types:
columns.append({
"label": _(project_type["title"]),
"fieldtype": "Int",
"fieldname": project_type["id"],
})
return columns
Complex Filters with Dependencies
{
fieldname: "loan_product",
label: __("Loan Product"),
fieldtype: "Link",
options: "Loan Product",
get_query: function() {
return {
filters: {
"loan_program": frappe.query_report.get_filter_value("loan_program")
}
};
}
}
Conditional Logic in Filters
{
fieldname: "time_window",
label: __("Time Window"),
fieldtype: "Select",
options: ['Annual', 'Quarterly', 'Monthly'],
on_change: function(report) {
const value = this.get_value();
if (value == "Annual") {
report.set_filter_value('from_date', frappe.datetime.year_start());
report.set_filter_value('to_date', frappe.datetime.year_end());
}
report.refresh();
}
}
Multi-Select Filters
{
fieldname: "account",
label: __("Account"),
fieldtype: "MultiSelectList",
options: "Account",
get_data: function (txt) {
return frappe.db.get_link_options("Account", txt, {
company: frappe.query_report.get_filter_value("company"),
});
},
}
Data Aggregation and Processing
def get_data(filters, project_types):
# Build dynamic SQL with aggregation
query = """
SELECT
product_name AS loan_product,"""
# Add dynamic columns for each project type
query += "\n".join([
f"SUM(CASE WHEN project_type = '{ptype.id}' THEN amount ELSE 0 END) AS '{ptype.title}',"
for ptype in project_types
])
query += """
SUM(total_amount) AS total
FROM(
SELECT
loan_product.product_name,
loan_request.project_type,
SUM(loan.disbursed_amount) AS total_amount
FROM `tabLoan` loan
JOIN `tabLoan Request` loan_request ON loan_request.loan = loan.name
JOIN `tabLoan Product` loan_product ON loan_request.loan_product = loan_product.name
WHERE loan.company = %(company)s
GROUP BY loan_product.name, loan_request.project_type
) AS inner_query
GROUP BY product_name
"""
return frappe.db.sql(query, values=filters, as_dict=True)
7. Best Practices
Performance
Use parameterized queries to prevent SQL injection
Add appropriate WHERE clauses to limit data
Use indexes on filtered columns
Consider pagination for large datasets
Security
Always validate and sanitize filter inputs
Use frappe.db.sql() with values parameter
Set appropriate role permissions
Avoid exposing sensitive data
Code Organization
Keep functions small and focused
Use meaningful variable names
Add proper error handling
Include comprehensive comments
User Experience
Provide sensible default filter values
Use appropriate field types for filters
Add helpful labels and descriptions
Consider mobile responsiveness
8. Column Format Specifications
Column Definition Formats
# Method 1: Dictionary format (recommended)
{
"label": _("Amount"),
"fieldtype": "Currency",
"fieldname": "amount",
"width": 120,
"options": "Company:company:default_currency" # For currency fields
}
# Method 2: String format (shorthand)
"Amount:Currency:120"
"ID:Link/DocType:90"
"Status::80" # Auto-width
Common Field Types
Data : Plain text
Link : Reference to another DocType
Currency : Monetary values
Int : Integer numbers
Float : Decimal numbers
Date : Date values
Datetime : Date and time values
Check : Boolean checkbox
Script vs Query Reports
Feature Script Report Query Report
Language Python SQL
Role Needed Administrator System Manager
File Types .py, .js, .json .json, .js
Flexibility High Moderate
Yes (via JS +
Filters Support Yes (via JS)
SQL)
Typical File Structure
/apps/your_app/your_app/your_module/report/your_report/
├── your_report.py
├── your_report.js
└── your_report.json
Conclusion
Script Reports offer the most control and customization but require developer access. Query Reports are easier to write and ideal for simpler tabular views using SQL.
Use the type that best fits your use case.
This comprehensive documentation provides everything needed to create custom reports in Frappe, from basic examples to advanced features used in production
applications.
