tomevault-io 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.
Converted and distributed by TomeVault — claim your Tome and manage your conversions.