name: Generate Frappe Report description: Generate custom reports (query reports and script reports) for Frappe applications with filters, aggregations, and formatting.

Generate Frappe Report

Create custom reports for data analysis, dashboards, and business intelligence in Frappe.

When to Use

• Creating custom reports for data analysis
• Building dashboards and analytics
• Creating query reports (SQL-based)
• Creating script reports (Python-based)
• Building reports with filters and aggregations

Instructions

1. Query Report (SQL-based)

Report JSON ({app}/{module}/report/{report_name}/{report_name}.json):

{
  "doctype": "Report",
  "name": "Sales Analysis",
  "report_name": "Sales Analysis",
  "ref_doctype": "Sales Order",
  "report_type": "Query Report",
  "is_standard": "Yes",
  "module": "Selling",
  "disabled": 0
}

Python File ({report_name}.py):

import frappe
from frappe import _

def execute(filters=None):
    """Execute query report"""
    columns = get_columns()
    data = get_data(filters)
    return columns, data

def get_columns():
    """Define report columns"""
    return [
        {
            "fieldname": "sales_order",
            "label": _("Sales Order"),
            "fieldtype": "Link",
            "options": "Sales Order",
            "width": 150
        },
        {
            "fieldname": "customer",
            "label": _("Customer"),
            "fieldtype": "Link",
            "options": "Customer",
            "width": 200
        },
        {
            "fieldname": "order_date",
            "label": _("Order 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):
    """Get report data"""
    conditions = get_conditions(filters)
    
    query = f"""
        SELECT 
            so.name as sales_order,
            so.customer,
            so.transaction_date as order_date,
            so.grand_total,
            so.status
        FROM `tabSales Order` so
        WHERE {conditions}
        ORDER BY so.transaction_date DESC
    """
    
    return frappe.db.sql(query, as_dict=True)

def get_conditions(filters):
    """Build WHERE conditions"""
    conditions = ["so.docstatus = 1"]  # Only submitted orders
    
    if filters.get("customer"):
        conditions.append(f"so.customer = '{filters.customer}'")
    
    if filters.get("from_date"):
        conditions.append(f"so.transaction_date >= '{filters.from_date}'")
    
    if filters.get("to_date"):
        conditions.append(f"so.transaction_date <= '{filters.to_date}'")
    
    if filters.get("status"):
        conditions.append(f"so.status = '{filters.status}'")
    
    return " AND ".join(conditions)

2. Script Report (Python-based)

Report JSON:

{
  "doctype": "Report",
  "name": "Customer Sales Summary",
  "report_name": "Customer Sales Summary",
  "ref_doctype": "Sales Order",
  "report_type": "Script Report",
  "is_standard": "Yes",
  "module": "Selling"
}

Python File:

import frappe
from frappe import _
from frappe.utils import flt, getdate

def execute(filters=None):
    """Execute script report"""
    columns = get_columns()
    data = get_data(filters)
    chart = get_chart_data(data)
    summary = get_summary(data)
    
    return columns, data, None, chart, summary

def get_columns():
    """Define report columns"""
    return [
        {
            "fieldname": "customer",
            "label": _("Customer"),
            "fieldtype": "Link",
            "options": "Customer",
            "width": 200
        },
        {
            "fieldname": "order_count",
            "label": _("Order Count"),
            "fieldtype": "Int",
            "width": 100
        },
        {
            "fieldname": "total_amount",
            "label": _("Total Amount"),
            "fieldtype": "Currency",
            "width": 120
        },
        {
            "fieldname": "average_order",
            "label": _("Average Order"),
            "fieldtype": "Currency",
            "width": 120
        }
    ]

def get_data(filters):
    """Get aggregated data"""
    conditions = get_conditions(filters)
    
    orders = frappe.get_all(
        "Sales Order",
        filters=conditions,
        fields=["customer", "grand_total", "name"],
        order_by="customer"
    )
    
    # Aggregate by customer
    customer_data = {}
    for order in orders:
        customer = order.customer
        if customer not in customer_data:
            customer_data[customer] = {
                "customer": customer,
                "order_count": 0,
                "total_amount": 0
            }
        
        customer_data[customer]["order_count"] += 1
        customer_data[customer]["total_amount"] += flt(order.grand_total)
    
    # Calculate averages
    data = []
    for customer, values in customer_data.items():
        values["average_order"] = values["total_amount"] / values["order_count"]
        data.append(values)
    
    return sorted(data, key=lambda x: x["total_amount"], reverse=True)

def get_conditions(filters):
    """Build filters"""
    conditions = {"docstatus": 1}
    
    if filters.get("customer"):
        conditions["customer"] = filters.customer
    
    if filters.get("from_date"):
        conditions["transaction_date"] = [">=", filters.from_date]
    
    if filters.get("to_date"):
        if "transaction_date" in conditions:
            conditions["transaction_date"] = [
                "between",
                [filters.from_date, filters.to_date]
            ]
        else:
            conditions["transaction_date"] = ["<=", filters.to_date]
    
    return conditions

def get_chart_data(data):
    """Generate chart data"""
    if not data:
        return None
    
    chart = {
        "data": {
            "labels": [d["customer"] for d in data[:10]],  # Top 10
            "datasets": [{
                "name": "Total Amount",
                "values": [d["total_amount"] for d in data[:10]]
            }]
        },
        "type": "bar"
    }
    
    return chart

def get_summary(data):
    """Generate summary"""
    if not data:
        return []
    
    total_orders = sum(d["order_count"] for d in data)
    total_amount = sum(d["total_amount"] for d in data)
    avg_order = total_amount / total_orders if total_orders > 0 else 0
    
    return [
        {
            "label": _("Total Customers"),
            "value": len(data),
            "indicator": "blue"
        },
        {
            "label": _("Total Orders"),
            "value": total_orders,
            "indicator": "green"
        },
        {
            "label": _("Total Amount"),
            "value": frappe.utils.fmt_currency(total_amount),
            "indicator": "blue"
        },
        {
            "label": _("Average Order Value"),
            "value": frappe.utils.fmt_currency(avg_order),
            "indicator": "green"
        }
    ]

3. Report Filters

Filter JSON (in report JSON):

{
  "filters": [
    {
      "fieldname": "from_date",
      "label": "From Date",
      "fieldtype": "Date",
      "default": "2024-01-01"
    },
    {
      "fieldname": "to_date",
      "label": "To Date",
      "fieldtype": "Date",
      "default": "2024-12-31"
    },
    {
      "fieldname": "customer",
      "label": "Customer",
      "fieldtype": "Link",
      "options": "Customer"
    },
    {
      "fieldname": "status",
      "label": "Status",
      "fieldtype": "Select",
      "options": "Draft\nSubmitted\nCancelled"
    }
  ]
}

4. Report Types

Query Report:

• Fast for large datasets
• Direct SQL queries
• Complex joins and aggregations
• Limited formatting

Script Report:

• Full Python flexibility
• Complex business logic
• Dynamic columns
• Charts and summaries

Report Builder:

• No-code solution
• User-configurable
• Basic aggregations
• Simple use cases

5. Common Patterns

Group By:

def get_data(filters):
    query = """
        SELECT 
            customer,
            COUNT(*) as order_count,
            SUM(grand_total) as total_amount
        FROM `tabSales Order`
        WHERE docstatus = 1
        GROUP BY customer
        ORDER BY total_amount DESC
    """
    return frappe.db.sql(query, as_dict=True)

Date Range:

def get_conditions(filters):
    conditions = []
    
    if filters.get("from_date") and filters.get("to_date"):
        conditions.append(
            f"transaction_date BETWEEN '{filters.from_date}' AND '{filters.to_date}'"
        )
    
    return " AND ".join(conditions) if conditions else "1=1"

Aggregations:

# Count
COUNT(*) as count

# Sum
SUM(amount) as total

# Average
AVG(amount) as average

# Min/Max
MIN(date) as earliest_date
MAX(date) as latest_date

Key Patterns

1. Query Reports: Use SQL for performance
2. Script Reports: Use Python for flexibility
3. Filters: Always validate filter input
4. Charts: Include chart data for visualization
5. Summary: Add summary metrics
6. Security: Check permissions before data access
7. Performance: Optimize queries for large datasets

Best Practices

• Use Query Reports for simple, fast reports
• Use Script Reports for complex business logic
• Validate filters before using in queries
• Add charts for better visualization
• Include summaries for quick insights
• Check permissions before data access
• Optimize queries for performance
• Document filters clearly