---
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`):

```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`):

```python
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**:

```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**:

```python
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):

```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:**
```python
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:**
```python
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:**
```python
# 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