Frappe Impl Reports

impertio-studio/frappe-impl-reports

> Use when building Script Reports, Query Reports, dashboard charts, or Number Cards in ERPNext. Prevents empty report output from wrong column definitions, broken filters, and unoptimized SQL in large datasets. Covers Report Builder, Script Report (Python + JS), Query Report, Report filters, dashboard Chart DocType, Number Card, report permissions.

6k tokens

context cost

the whole folder, loaded on every use

files

instructions only

copies elsewhere

how many repositories repackaged it

158

stars on the repo

on the repository, not the skill itself

Install

one command, takes just this skill from the repository

npx skills add https://github.com/Impertio-Studio/Frappe_Claude_Skill_Package --skill frappe-impl-reports

The instruction itself

24 sections, as written by the author

Frappe Report Building

Quick Reference

|---|---|---|---|

Decision Tree: Which Report Type?

Need a report?
├─ End user builds it themselves? → Report Builder
├─ Simple SQL with no Python logic? → Query Report
├─ Complex logic / charts / summary? → Script Report
│   └─ Dataset > 100k rows or timeout? → Add prepared_report = True
└─ Real-time KPI on workspace? → Number Card or Dashboard Chart

1. Creating a Script Report

File Structure

my_app/my_module/report/sales_summary/
├── sales_summary.json    # Report DocType definition
├── sales_summary.py      # Python: execute() function
└── sales_summary.js      # JavaScript: filters + config

ALWAYS create via Desk: Report > New > Script Report > set "Is Standard = Yes" in Developer Mode.

Python: The execute() Function

# sales_summary.py
import frappe
from frappe import _

def execute(filters=None):
    columns = get_columns()
    data = get_data(filters)
    chart = get_chart(data)
    report_summary = get_summary(data)
    return columns, data, None, chart, report_summary

def get_columns():
    return [
        {"fieldname": "customer", "label": _("Customer"), "fieldtype": "Link",
         "options": "Customer", "width": 200},
        {"fieldname": "total", "label": _("Total"), "fieldtype": "Currency",
         "options": "currency", "width": 120},
        {"fieldname": "qty", "label": _("Qty"), "fieldtype": "Int", "width": 80},
        {"fieldname": "posting_date", "label": _("Date"), "fieldtype": "Date", "width": 100},
    ]

def get_data(filters):
    conditions = get_conditions(filters)
    return frappe.db.sql("""
        SELECT
            si.customer, SUM(si.grand_total) as total,
            SUM(si.total_qty) as qty, si.posting_date
        FROM `tabSales Invoice` si
        WHERE si.docstatus = 1 {conditions}
        GROUP BY si.customer
        ORDER BY total DESC
    """.format(conditions=conditions), filters, as_dict=True)

def get_conditions(filters):
    conditions = ""
    if filters.get("from_date"):
        conditions += " AND si.posting_date >= %(from_date)s"
    if filters.get("to_date"):
        conditions += " AND si.posting_date <= %(to_date)s"
    if filters.get("company"):
        conditions += " AND si.company = %(company)s"
    return conditions

Return value order (positional — ALWAYS maintain this order):

|---|---|---|---|

| 1 | columns | list[dict] | YES |

| 2 | data | list[dict] or list[list] | YES |

| 3 | message | str or None | NO |

| 4 | chart | dict or None | NO |

| 5 | report_summary | list[dict] or None | NO |

| 6 | skip_total_rows | bool | NO |

JavaScript: Filters

// sales_summary.js
frappe.query_reports["Sales Summary"] = {
    filters: [
        {
            fieldname: "company",
            label: __("Company"),
            fieldtype: "Link",
            options: "Company",
            default: frappe.defaults.get_user_default("company"),
            reqd: 1
        },
        {
            fieldname: "from_date",
            label: __("From Date"),
            fieldtype: "Date",
            default: frappe.datetime.add_months(frappe.datetime.get_today(), -1),
            reqd: 1
        },
        {
            fieldname: "to_date",
            label: __("To Date"),
            fieldtype: "Date",
            default: frappe.datetime.get_today(),
            reqd: 1
        },
        {
            fieldname: "customer_group",
            label: __("Customer Group"),
            fieldtype: "Link",
            options: "Customer Group",
            depends_on: "eval:doc.company"
        }
    ],
    formatter: function(value, row, column, data, default_formatter) {
        value = default_formatter(value, row, column, data);
        if (column.fieldname === "total" && data.total > 100000) {
            value = "<span style='color:green;font-weight:bold'>" + value + "</span>";
        }
        return value;
    }
};

2. Creating a Query Report

Query Reports use raw SQL. ALWAYS use the legacy column format in SQL aliases:

SELECT
    `tabWork Order`.name AS "Work Order:Link/Work Order:200",
    `tabWork Order`.creation AS "Date:Date:120",
    `tabWork Order`.company AS "Company:Link/Company:150",
    `tabWork Order`.qty AS "Qty:Int:80",
    `tabWork Order`.grand_total AS "Total:Currency:120"
FROM `tabWork Order`
WHERE `tabWork Order`.docstatus = 1
ORDER BY `tabWork Order`.creation DESC

Column format: "Label:Fieldtype/Options:Width"

Use %(filter_name)s for filter variables in WHERE clauses.

3. Adding Charts to Reports

Return a chart dict as the 4th element from execute():

def get_chart(data):
    labels = [d.customer for d in data[:10]]
    values = [d.total for d in data[:10]]
    return {
        "data": {
            "labels": labels,
            "datasets": [{"name": _("Revenue"), "values": values}]
        },
        "type": "bar",            # bar | line | pie | donut | percentage
        "colors": ["#7cd6fd"],
        "barOptions": {"stacked": False},  # for bar charts
        "height": 300
    }

Chart types: bar, line, pie, donut, percentage.

For multi-dataset charts (e.g., comparing periods):

"datasets": [
    {"name": "2024", "values": [10, 20, 30]},
    {"name": "2025", "values": [15, 25, 35]}
]

4. Adding Report Summary

Return a list of summary dicts as the 5th element:

def get_summary(data):
    total_revenue = sum(d.total for d in data)
    total_qty = sum(d.qty for d in data)
    return [
        {"value": total_revenue, "label": _("Total Revenue"),
         "datatype": "Currency", "currency": "USD",
         "indicator": "Green" if total_revenue > 0 else "Red"},
        {"value": total_qty, "label": _("Total Qty"),
         "datatype": "Int", "indicator": "Blue"},
        {"value": len(data), "label": _("Customers"),
         "datatype": "Int", "indicator": "Grey"}
    ]

Indicator colors: Green, Blue, Orange, Red, Grey.

5. Prepared Reports

For reports that timeout on large datasets, add to the .js file:

frappe.query_reports["Heavy Report"] = {
    filters: [ /* ... */ ],
    prepared_report: true    // enables background generation
};

When prepared_report: true, Frappe queues the report via background job. Users see cached results and can regenerate on demand.

6. Number Cards

Three types of Number Cards for workspace dashboards:

| Type | Source | Use Case |

|---|---|---|

| Document Type | DocType aggregate | Count/sum of documents |

| Report | Script/Query Report | KPI from report data |

| Custom | Whitelisted method | Any computed value |

Document Type Number Card

Create via Desk > Number Card. Set DocType, aggregate function (Count/Sum/Avg), and filters.

Report-Based Number Card

Point to an existing report. The card displays the first row's first numeric column.

Custom Method Number Card

# In your app, create a whitelisted method:
@frappe.whitelist()
def get_open_tickets():
    count = frappe.db.count("Issue", {"status": "Open"})
    return {"value": count, "fieldtype": "Int", "route_options": {"status": "Open"},
            "route": ["query-report", "Open Issues"]}

7. Dashboard Charts

Create via Desk > Dashboard Chart or programmatically in fixtures:

# hooks.py
fixtures = [
    {"dt": "Dashboard Chart", "filters": [["module", "=", "My Module"]]}
]

Source types: Report, Group By, Custom (whitelisted method).

Group By Chart

{
    "chart_name": "Invoices by Status",
    "chart_type": "Group By",
    "document_type": "Sales Invoice",
    "group_by_type": "Count",
    "group_by_based_on": "status",
    "type": "Donut",
    "filters_json": "{\"docstatus\": 1}"
}

8. Building a Dashboard

Dashboards combine multiple charts and Number Cards:

{
    "name": "Sales Dashboard",
    "module": "Selling",
    "charts": [
        {"chart": "Monthly Revenue", "width": "Full"},
        {"chart": "Invoices by Status", "width": "Half"},
        {"chart": "Top Customers", "width": "Half"}
    ],
    "cards": [
        {"card": "Total Revenue"},
        {"card": "Open Orders"}
    ]
}

9. Performance Optimization

ALWAYS add indexes on columns used in WHERE/GROUP BY (frappe.model.utils.add_index)
ALWAYS use as_dict=True in frappe.db.sql() — matches column fieldnames
NEVER use SELECT * — specify exact columns
NEVER load full documents (frappe.get_doc) inside report loops — use SQL
Use frappe.qb (query builder) for parameterized queries in v14+
For reports > 50k rows, ALWAYS enable prepared_report: true
ALWAYS filter by docstatus to exclude draft/cancelled documents

10. Common Patterns

Date Range Filter Pattern

if filters.get("from_date") and filters.get("to_date"):
    conditions += " AND posting_date BETWEEN %(from_date)s AND %(to_date)s"

Multi-Currency Pattern

{"fieldname": "amount", "label": _("Amount"), "fieldtype": "Currency",
 "options": "currency", "width": 120}
# "options": "currency" means use the row's "currency" field for formatting

Group By with Totals Pattern

data = frappe.db.sql("""
    SELECT customer, COUNT(*) as count, SUM(grand_total) as total
    FROM `tabSales Invoice`
    WHERE docstatus = 1 {conditions}
    GROUP BY customer WITH ROLLUP
""".format(conditions=conditions), filters, as_dict=True)

How to use it

Copy the folder

Take impertio-studio/frappe-impl-reports from the repository into ~/.claude/skills for personal use, or into .claude/skills inside a project.

Check the name does not clash

The agent identifies a skill by the name field in its header. Two skills with the same name cannot sit side by side — one of them will be ignored.