---

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.

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

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

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.