frappe-report-generator

What this skill does

# 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:**
```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):**
```python
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:**
```python
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: