frappe-report-generator

name	frappe-report-generator
description	Generate custom reports, query reports, and script reports for Frappe applications. Use when creating data analysis and reporting features.

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 <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/<app>/<app>/<module>/doctype/<name>/<name>.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 <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 <= %(to_date)s")

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

    return " AND " + " AND ".join(conditions) if conditions else ""

3. Script Report Structure

In execute(filters), prefer frappe.db.get_all / frappe.qb.get_query over raw SQL wherever they suffice — get_query handles joins, aggregations, and child-table fields. For user-facing data, use frappe.db.get_list so row-level permissions are enforced (get_all ignores them). Never call frappe.db.commit() in a report — reports are read-only.

Advanced Script Report:

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

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

    return columns, data, None, chart, report_summary

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

def get_data(filters):
    # Get sales orders
    sales_orders = frappe.get_all(
        "Sales Order",
        filters={
            "docstatus": 1,
            "posting_date": ["between", [filters.get("from_date"), filters.get("to_date")]]
        },
        fields=["customer", "grand_total"]
    )

    # Aggregate by customer
    customer_data = {}
    for order in sales_orders:
        customer = order.customer
        if customer not in customer_data:
            customer_data[customer] = {
                "customer": customer,
                "total_orders": 0,
                "total_amount": 0
            }

        customer_data[customer]["total_orders"] += 1
        customer_data[customer]["total_amount"] += flt(order.grand_total)

    # Calculate averages
    data = []
    for customer, values in customer_data.items():
        data.append({
            "customer": customer,
            "total_orders": values["total_orders"],
            "total_amount": values["total_amount"],
            "avg_order_value": values["total_amount"] / values["total_orders"]
        })

    return sorted(data, key=lambda x: x["total_amount"], reverse=True)

def get_chart_data(data):
    """Generate chart for report"""
    if not data:
        return None

    labels = [d["customer"] for d in data[:10]]  # Top 10
    values = [d["total_amount"] for d in data[:10]]

    return {
        "data": {
            "labels": labels,
            "datasets": [
                {
                    "name": "Total Sales",
                    "values": values
                }
            ]
        },
        "type": "bar",
        "colors": ["#7cd6fd"]
    }

def get_report_summary(data):
    """Generate summary cards"""
    if not data:
        return []

    total_customers = len(data)
    total_revenue = sum(d["total_amount"] for d in data)
    total_orders = sum(d["total_orders"] for d in data)
    avg_order_value = total_revenue / total_orders if total_orders else 0

    return [
        {
            "value": total_customers,
            "label": "Total Customers",
            "datatype": "Int"
        },
        {
            "value": total_revenue,
            "label": "Total Revenue",
            "datatype": "Currency"
        },
        {
            "value": total_orders,
            "label": "Total Orders",
            "datatype": "Int"
        },
        {
            "value": avg_order_value,
            "label": "Avg Order Value",
            "datatype": "Currency"
        }
    ]

4. Report Filters

Filter Definition (JSON):

{
  "filters": [
    {
      "fieldname": "customer",
      "label": "Customer",
      "fieldtype": "Link",
      "options": "Customer"
    },
    {
      "fieldname": "from_date",
      "label": "From Date",
      "fieldtype": "Date",
      "default": "frappe.datetime.month_start()",
      "reqd": 1
    },
    {
      "fieldname": "to_date",
      "label": "To Date",
      "fieldtype": "Date",
      "default": "frappe.datetime.month_end()",
      "reqd": 1
    },
    {
      "fieldname": "status",
      "label": "Status",
      "fieldtype": "Select",
      "options": "\nDraft\nSubmitted\nCancelled",
      "default": "Submitted"
    }
  ]
}

5. Advanced Query Patterns

Complex Joins:

def get_data(filters):
    query = """
        SELECT
            so.name as sales_order,
            so.customer,
            c.customer_group,
            c.territory,
            so.posting_date,
            SUM(soi.amount) as total_amount,
            COUNT(soi.name) as total_items
        FROM
            `tabSales Order` so
        INNER JOIN
            `tabCustomer` c ON so.customer = c.name
        INNER JOIN
            `tabSales Order Item` soi ON soi.parent = so.name
        WHERE
            so.docstatus = 1
            AND so.posting_date BETWEEN %(from_date)s AND %(to_date)s
        GROUP BY
            so.name
        ORDER BY
            total_amount DESC
    """

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

Aggregations:

def get_summary_data(filters):
    query = """
        SELECT
            MONTH(posting_date) as month,
            YEAR(posting_date) as year,
            COUNT(name) as order_count,
            SUM(grand_total) as total_sales,
            AVG(grand_total) as avg_order_value,
            MIN(grand_total) as min_order,
            MAX(grand_total) as max_order
        FROM
            `tabSales Order`
        WHERE
            docstatus = 1
            AND posting_date BETWEEN %(from_date)s AND %(to_date)s
        GROUP BY
            YEAR(posting_date), MONTH(posting_date)
        ORDER BY
            year DESC, month DESC
    """

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

6. Dynamic Columns

def get_columns():
    """Generate columns dynamically based on data"""
    base_columns = [
        {
            "fieldname": "customer",
            "label": _("Customer"),
            "fieldtype": "Link",
            "options": "Customer",
            "width": 150
        }
    ]

    # Add month columns dynamically
    months = ["Jan", "Feb", "Mar", "Apr", "May", "Jun",
              "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]

    for month in months:
        base_columns.append({
            "fieldname": month.lower(),
            "label": _(month),
            "fieldtype": "Currency",
            "width": 100
        })

    base_columns.append({
        "fieldname": "total",
        "label": _("Total"),
        "fieldtype": "Currency",
        "width": 120
    })

    return base_columns

7. Report Formatting

Conditional Formatting:

def get_data(filters):
    data = # ... get data

    for row in data:
        # Add indicator
        if row.grand_total > 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/<app_name>/<module>/report/<report_name>/
├── __init__.py
├── <report_name>.json
├── <report_name>.py
└── <report_name>.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.