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

Optimize queries - Use proper indexes and LIMIT
Filter early - Apply filters in WHERE clause, not in Python
Use parameterized queries - Prevent SQL injection
Cache when possible - Cache expensive calculations
Validate filters - Always validate user inputs
Handle permissions - Check user permissions
Provide defaults - Set sensible default filters
Document reports - Add helpful descriptions
Test with large data - Ensure performance at scale
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.

frappe-report-generator

$ Installer