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.