Frappe Report Generator

Frappe Report Generator Skill

Create custom reports for data analysis, dashboards, and business intelligence in Frappe.

Global Rules

These Frappe conventions apply to everything this skill generates, and override any conflicting example below.

• Bench commands: use bare bench (never ./env/bin/bench or a full path). Always pass --site explicitly — never run a bare bench migrate / bench run-tests. Run bench start in the background and only if it isn't already running. Don't run discovery commands (which bench, bench --version).
• DocType files live at apps////doctype//.json — the app name appears twice (directory + Python package) — with an empty __init__.py alongside. Never mkdir the folder; write the JSON and run bench --site migrate to create the structure. Don't add creation, modified, owner, modified_by, or docstatus as fields — Frappe manages them.
• Database & ORM: prefer frappe.qb.get_query() over raw frappe.db.sql(). Use frappe.db.get_all() for server logic (ignores permissions) and frappe.db.get_list() for user-facing APIs (enforces them). Never use frappe.db.set_value() on a field with validation or lifecycle logic — load the doc and doc.save() so controller hooks run. Batch-fetch related records; never query inside a loop (N+1).
• Never call frappe.db.commit() in controllers, request handlers, background jobs, or patches — Frappe auto-commits on success and rolls back on uncaught errors. Flush manually only to make a write visible to a subsequent frappe.enqueue() (or pass enqueue_after_commit=True).
• Permissions & APIs: put permission checks inside controller methods (enforced on every call path), not in API wrappers. Type-hint every @frappe.whitelist() parameter so Frappe validates and casts it, and pass methods=[...] to pin the HTTP verb.

When to Use This Skill

Claude should invoke this skill when:

• User wants to create custom reports
• User needs data analysis or aggregation
• User asks about query reports or script reports
• User wants to build dashboards
• User needs help with report formatting or filters

Capabilities

1. Report Types

Query Report (SQL-based):

• Fast performance for large datasets
• Direct SQL queries
• Complex joins and aggregations
• Limited formatting options

Script Report (Python-based):

• Full Python flexibility
• Complex business logic
• Dynamic columns and formatting
• Access to Frappe ORM

Report Builder (No-code):

• User-configurable
• No coding required
• Basic aggregations
• Simple use cases

2. Query Report Structure

> Security — parameterize all filters. Bind every user-supplied value with a named placeholder (%(filter_name)s) passed via the filters dict. NEVER string-format or f-string a user value into SQL — that is an injection hole. Building the WHERE clause by concatenating parameterized fragments (each still using %(...)s) is acceptable, but the values themselves must always travel through the bound filters dict, never through the SQL string.

Basic Query Report JSON:

{
  "name": "Sales Analysis",
  "report_name": "Sales Analysis",
  "ref_doctype": "Sales Order",
  "report_type": "Query Report",
  "is_standard": "Yes",
  "module": "Selling",
  "disabled": 0,
  "query": "",
  "filters": [],
  "columns": []
}

Python File (sales_analysis.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": "sales_order",
            "label": _("Sales Order"),
            "fieldtype": "Link",
            "options": "Sales Order",
            "width": 150
        },
        {
            "fieldname": "customer",
            "label": _("Customer"),
            "fieldtype": "Link",
            "options": "Customer",
            "width": 150
        },
        {
            "fieldname": "posting_date",
            "label": _("Date"),
            "fieldtype": "Date",
            "width": 100
        },
        {
            "fieldname": "grand_total",
            "label": _("Grand Total"),
            "fieldtype": "Currency",
            "width": 120
        },
        {
            "fieldname": "status",
            "label": _("Status"),
            "fieldtype": "Data",
            "width": 100
        }
    ]

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

    query = f"""
        SELECT
            so.name as sales_order,
            so.customer,
            so.posting_date,
            so.grand_total,
            so.status
        FROM
            `tabSales Order` so
        WHERE
            so.docstatus = 1
            {conditions}
        ORDER BY
            so.posting_date DESC
    """

    return frappe.db.sql(query, filters, as_dict=1)

def get_conditions(filters):
    conditions = []

    if filters.get("customer"):
        conditions.append("so.customer = %(customer)s")

    if filters.get("from_date"):
        conditions.append("so.posting_date >= %(from_date)s")

    if filters.get("to_date"):
        conditions.append("so.posting_date  100000:
            row["indicator"] = "green"
        elif row.grand_total > 50000:
            row["indicator"] = "orange"
        else:
            row["indicator"] = "red"

    return data

8. Export Features

Reports automatically support:

• Excel export
• PDF export
• CSV export
• Print view

9. Performance Optimization

Use Indexes:

# Ensure proper indexes exist
# ALTER TABLE `tabSales Order` ADD INDEX idx_posting_date (posting_date);
# ALTER TABLE `tabSales Order` ADD INDEX idx_customer (customer);

Limit Results:

def get_data(filters):
    # Add LIMIT for large datasets
    query = f"""
        SELECT ...
        FROM ...
        WHERE ...
        LIMIT 1000
    """
    return frappe.db.sql(query, filters, as_dict=1)

Use Query Caching:

def get_data(filters):
    cache_key = f"sales_report_{filters.get('from_date')}_{filters.get('to_date')}"

    data = frappe.cache.get_value(cache_key)
    if data:
        return data

    data = frappe.db.sql(query, filters, as_dict=1)
    frappe.cache.set_value(cache_key, data, expires_in_sec=300)

    return data

10. Report Permissions

Permission Query:

def get_data(filters):
    # Only show data user has permission to see
    if not frappe.has_permission("Sales Order", "read"):
        frappe.throw(_("Not permitted"))

    # Filter by user permissions
    user_customers = frappe.get_list(
        "Customer",
        filters={"name": ["in", frappe.get_roles()]},
        pluck="name"
    )

    if user_customers:
        filters["customer"] = ["in", user_customers]

File Structure

Reports should be organized as:

apps///report//
├── __init__.py
├── .json
├── .py
└── .js (optional, for client-side customization)

Best Practices

1. Optimize queries - Use proper indexes and LIMIT
2. Filter early - Apply filters in WHERE clause, not in Python
3. Use parameterized queries - Prevent SQL injection
4. Cache when possible - Cache expensive calculations
5. Validate filters - Always validate user inputs
6. Handle permissions - Check user permissions
7. Provide defaults - Set sensible default filters
8. Document reports - Add helpful descriptions
9. Test with large data - Ensure performance at scale
10. Use chart/summary wisely - Enhance user experience

Testing Reports

Access reports at:

http://localhost:8000/app/query-report/Sales%20Analysis

Remember: This skill is model-invoked. Claude will use it autonomously when detecting report development tasks.

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: Venkateshvenki404224
• Source: Venkateshvenki404224/frappe-apps-manager
• License: MIT

Install and usage instructions live in the source repository linked above.