Script Reports

Script Reports provide far more flexibility than standard Query Reports. They support tree hierarchies, editable cells, custom charts, conditional formatting, external SQL, toolbar actions, and localStorage persistence.

File Structure

mymodule/report/my_report/
├── my_report.json         # Report definition (ref_doctype, roles, report_type)
├── my_report.py           # Server-side execute() function
├── my_report.js           # Client-side filters, formatters, editors, hooks
├── my_report.sql          # External SQL query (optional)
├── my_report.css          # Custom styles (optional)
└── columns.json           # External column definitions (optional)

Report JSON Definition

{
    "doctype": "Report",
    "report_type": "Script Report",
    "ref_doctype": "Item",
    "is_standard": "Yes",
    "module": "MyModule",
    "name": "My Report",
    "report_name": "My Report",
    "roles": [
        {"role": "Stock Manager"},
        {"role": "Item Manager"}
    ]
}

Python: The execute() Function

Basic Pattern

def execute(filters=None):
    columns = get_columns()
    data = get_data(filters)
    return columns, data

Full Return Signature

def execute(filters=None):
    return columns, data, message, chart
    #      list     list   str|None  dict|None

External Columns from JSON

import json
from pathlib import Path

def execute(filters=None):
    columns = json.loads((Path(__file__).parent / "columns.json").read_text())
    ...

External SQL

def execute(filters=None):
    query = (Path(__file__).parent / "my_report.sql").read_text()
    data = frappe.db.sql(query, {"param": value}, as_dict=True)
    ...

Chart Data

chart = {
    "type": "line",  # or "bar", "pie", "donut", "percentage"
    "data": {
        "labels": ["Project A", "Project B"],
        "datasets": [
            {"name": "Quoted Hours", "values": [10.0, 20.0]},
            {"name": "Actual Hours", "values": [12.0, 18.0]},
        ],
    },
    "axisOptions": {"xAxisMode": "tick", "shortenYAxisNumbers": 1},
}
return columns, data, None, chart

Typed Filters

from typing import TypedDict
from frappe.types import DF

class Filters(TypedDict, total=False):
    item_group: DF.Link | None
    date_range: tuple[str, str]
    status: DF.Literal["Open", "Completed", "Cancelled"]

JavaScript: Client-Side Configuration

Filters

frappe.query_reports["My Report"] = {
    filters: [
        {
            fieldname: "item_group",
            label: __("Item Group"),
            fieldtype: "Link",
            options: "Item Group",
        },
        {
            fieldname: "date_range",
            label: __("Date Range"),
            fieldtype: "DateRange",
        },
        {
            fieldname: "status",
            label: __("Status"),
            fieldtype: "Select",
            options: "Open\nCompleted\nCancelled",
        },
    ],
};

Tree Reports

Enable hierarchical rendering using indent and parent in your data:

frappe.query_reports["My Report"] = {
    tree: true,
    initial_depth: 1,  // How many levels to expand by default
    // ... filters
};

Data rows need indent (0-based depth) and optionally parent (ID of parent row):

# Python: return rows with indent
data = [
    {"name": "Group A", "indent": 0, "id": "A"},
    {"name": "Item 1",  "indent": 1, "parent": "A"},
    {"name": "Item 2",  "indent": 1, "parent": "A"},
]

SQL Tree via UNION ALL

Build hierarchy in SQL using separate queries per level:

-- Level 0: Projects
SELECT id, name, ..., 0 AS indent, NULL AS parent FROM projects
UNION ALL
-- Level 1: Operations under projects
SELECT id, name, ..., 1 AS indent, project AS parent FROM operations

Custom Formatters

formatter: function (value, row, column, data, default_formatter) {
    value = default_formatter(value, row, column, data);

    if (column.fieldname === "name" && data) {
        const indent = row[1].indent;
        if (indent === 0) {
            // Parent row: link to project
            value = `<a href="/app/project/${data.id}">${data.id}</a>`;
        } else {
            // Child row: link to job card
            value = `<a href="/app/job-card/${data.job_card}">${data.name}</a>`;
        }
    }

    // Fraction formatting for a Float field
    if (column.fieldname === "conversion_factor" && value) {
        return frappe.format(value, {
            fieldtype: "Float",
            options: "Fraction",
        }, { inline: true });
    }

    return value;
},

Conditional Row Coloring

Use after_datatable_render to style rows based on data values:

after_datatable_render: function (datatable) {
    const varianceIdx = datatable.datamanager.columns.findIndex(
        (col) => col.fieldname === "variance_percent"
    );

    datatable.datamanager.rows.forEach((row, rowIndex) => {
        const value = row[varianceIdx].content;
        if (value === undefined || value === null) return;

        let intensity = Math.min(Math.abs(value) / 100, 1);
        let bgColor = value >= 0
            ? `rgba(0, 128, 0, ${intensity * 0.3})`   // Green for positive
            : `rgba(255, 0, 0, ${intensity * 0.3})`;   // Red for negative

        datatable.datamanager.columns.forEach((_, colIdx) => {
            datatable.style.setStyle(
                `.dt-cell--${colIdx}-${rowIndex}`,
                { backgroundColor: bgColor }
            );
        });

        // Bold parent rows
        if (row[1].indent === 0) {
            datatable.datamanager.columns.forEach((_, colIdx) => {
                datatable.style.setStyle(
                    `.dt-cell--${colIdx}-${rowIndex}`,
                    { fontWeight: 600 }
                );
            });
        }
    });
},

Datatable Options

get_datatable_options: function (options) {
    return Object.assign(options, {
        treeView: true,
        checkboxColumn: false,
        serialNoColumn: false,
        layout: "ratio",    // or "fixed"
        cellHeight: 36,
        showTotalRow: true,
    });
},

Inline Editors

Provide per-cell editors via getEditor in datatable options:

get_datatable_options: function (options) {
    return Object.assign(options, {
        getEditor(colIndex, rowIndex, value, parent, column, row, data) {
            if (!data || data.row_type === "group") return false;

            // Number editor
            if (column.id === "valuation_rate") {
                const input = document.createElement("input");
                input.type = "number";
                input.step = "0.01";
                input.style.width = "100%";
                parent.appendChild(input);
                return {
                    initValue(val) { input.focus(); input.value = val || ""; },
                    getValue() { return parseFloat(input.value) || 0; },
                    setValue(newValue) {
                        // Save to server
                        frappe.call({
                            method: "myapp.api.update_field",
                            args: { item: data.item_code, value: newValue },
                            async: false,
                        });
                    },
                };
            }

            // Frappe Link editor
            if (column.id === "stock_uom") {
                let control = frappe.ui.form.make_control({
                    df: { fieldtype: "Link", fieldname: "stock_uom", options: "UOM" },
                    parent: parent,
                    render_input: true,
                });
                control.toggle_label(false);
                control.toggle_description(false);
                return {
                    initValue(val) {
                        control.set_value(val);
                        setTimeout(() => control.$input?.focus(), 100);
                    },
                    getValue() { return control.get_value(); },
                    setValue(newValue) { /* save logic */ },
                };
            }

            // Frappe Fraction editor
            if (column.id === "conversion_factor") {
                let control = frappe.ui.form.make_control({
                    df: { fieldtype: "Float", fieldname: "cf", options: "Fraction" },
                    parent: parent,
                    render_input: true,
                });
                control.toggle_label(false);
                control.toggle_description(false);
                return { /* same pattern */ };
            }

            return false;
        },
    });
},

Cross-Column Recalculation

When editing one column should update others:

getEditor(colIndex, rowIndex, value, parent, column, row, data) {
    if (column.id === "reorder_qty") {
        const conv = data.conversion_factor || 1;
        const input = document.createElement("input");
        input.type = "number";
        parent.appendChild(input);
        return {
            initValue(val) { input.focus(); input.value = val; },
            getValue() { return parseFloat(input.value) || 0; },
            setValue(value) {
                // Update multiple columns at once
                updateDataRow({
                    reorder_qty: value,
                    reorder_skids: value / conv,
                    projected_stock: data.available_stock + value,
                }, rowIndex);
            },
        };
    }
}

function updateDataRow(updates, rowIndex) {
    Object.entries(updates).forEach(([key, value]) => {
        datatable.datamanager.data[rowIndex][key] = value;
        const colIdx = datatable.datamanager.getColumnIndexById(key);
        if (colIdx !== -1) {
            datatable.datamanager.updateCell(colIdx, rowIndex, { content: value });
        }
    });
    datatable.refreshRow(datatable.datamanager.getRow(rowIndex), rowIndex);
    datatable.bodyRenderer.renderFooter();
}

LocalStorage Persistence

Persist unsaved editor data across page refreshes:

const STORAGE_KEY = "my_report_data";

function saveData() {
    const data = {};
    datatable.datamanager.data.forEach((row) => {
        if (row.item && row.reorder_qty) {
            data[row.item] = row.reorder_qty;
        }
    });
    localStorage.setItem(STORAGE_KEY, JSON.stringify(data));
}

function loadData() {
    const saved = localStorage.getItem(STORAGE_KEY);
    if (!saved) return;
    const data = JSON.parse(saved);
    datatable.datamanager.data.forEach((row, idx) => {
        if (row.item && data[row.item]) {
            updateDataRow({ reorder_qty: data[row.item] }, idx);
        }
    });
}

Custom Toolbar Buttons

onload: function (report) {
    report.page.add_button(__("Create Purchase Order"), function () {
        frappe.call({
            method: "myapp.api.create_purchase_order",
            args: { data: report.data },
            callback: (r) => { /* handle result */ },
        });
    }, { btn_class: "btn-primary", icon: "check" });
},

Column Totals

get_datatable_options: function (options) {
    return Object.assign(options, {
        showTotalRow: true,
        hooks: {
            columnTotal(columnValues, cell) {
                if ([8, 9, 10].includes(cell.colIndex)) {
                    return columnValues.reduce((acc, v) =>
                        acc + (typeof v === "number" ? v : 0), 0);
                }
            },
        },
    });
},

Column Background Colors

after_datatable_render: function (datatable) {
    datatable.style.setStyle(".dt-cell--col-6", {
        backgroundColor: "rgba(182, 216, 230, 1)",
    });
    [8, 9].forEach((col) => {
        datatable.style.setStyle(`.dt-cell--col-${col}`, {
            backgroundColor: "rgba(148, 250, 179, 1)",
        });
    });
},

Server-Side Whitelisted Endpoints for Reports

For editable reports, expose update endpoints:

@frappe.whitelist()
def update_item_field(item_code: str, fieldname: str, value: Any) -> dict:
    allowed_fields = {"stock_uom", "sales_uom", "valuation_rate"}
    if fieldname not in allowed_fields:
        return {"status": "error", "message": _("Field not editable")}

    try:
        doc = frappe.get_doc("Item", item_code)
        doc.set(fieldname, value)
        doc.save()
        frappe.db.commit()
        return {"status": "success", "message": _("{0} updated").format(fieldname)}
    except Exception as e:
        frappe.log_error(frappe.get_traceback(), f"Update Error - {item_code}")
        return {"status": "error", "message": str(e)}

CSS for Full-Width Reports

/* my_report.css */
@media (min-width: 768px) {
    body.full-width .container {
        width: 100% !important;
        max-width: 100% !important;
    }
}