A Query Report covers a lot of ground, but some reports need real logic: loops, conditional columns, data from several queries merged together, or a chart and summary cards built in code. That is the job of a Script Report, written in Python inside a custom app.

Create the report files

Script Reports that live in your app are standard reports. With developer mode on, as Administrator:

  1. Create a new Report.
  2. Set Report Type to Script Report, Ref DocType to Sales Invoice, Is Standard to Yes, and Module to a module in your custom app.
  3. Save.

ERPNext writes a folder for the report inside that module, with a .json, a .py and a .js file. Those files are what you commit, and bench migrate installs the report on every other site.

The Python side

The .py file must define execute(filters). It returns the columns and the data, and can optionally return a message, a chart and a report summary, in that order.

import frappe
from frappe import _
from frappe.query_builder.functions import Sum


def execute(filters=None):
    filters = frappe._dict(filters or {})
    data = get_data(filters)
    return get_columns(), data, None, get_chart(data), get_summary(data)


def get_columns():
    return [
        {"label": _("Item Group"), "fieldname": "item_group",
         "fieldtype": "Link", "options": "Item Group", "width": 200},
        {"label": _("Qty"), "fieldname": "qty", "fieldtype": "Float", "width": 100},
        {"label": _("Net Sales"), "fieldname": "net_sales",
         "fieldtype": "Currency", "width": 150},
    ]


def get_data(filters):
    si = frappe.qb.DocType("Sales Invoice")
    sii = frappe.qb.DocType("Sales Invoice Item")
    return (
        frappe.qb.from_(sii)
        .join(si).on(si.name == sii.parent)
        .select(sii.item_group, Sum(sii.qty).as_("qty"),
                Sum(sii.base_net_amount).as_("net_sales"))
        .where(si.docstatus == 1)
        .where(si.company == filters.company)
        .where(si.posting_date.between(filters.from_date, filters.to_date))
        .groupby(sii.item_group)
        .orderby(Sum(sii.base_net_amount), order=frappe.qb.desc)
        .run(as_dict=True)
    )

frappe.qb builds the SQL for you and passes filter values as parameters, so there is no string building and no injection risk.

Chart and summary

The chart is a dictionary in the format ERPNext's charting library expects. The summary is a list of small cards shown above the table.

def get_chart(data):
    return {
        "data": {
            "labels": [d.item_group for d in data[:10]],
            "datasets": [{"name": _("Net Sales"),
                          "values": [d.net_sales for d in data[:10]]}],
        },
        "type": "bar",
    }


def get_summary(data):
    return [{"label": _("Net Sales"), "datatype": "Currency",
             "value": sum(d.net_sales for d in data), "indicator": "Blue"}]

The JavaScript side

The .js file declares the filters. Their values arrive in execute as the filters dictionary.

frappe.query_reports["Sales by Item Group"] = {
    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 },
    ],
};

Behaviour worth knowing

  • Permissions. A Link column to a restricted DocType lets ERPNext filter rows by the user's User Permissions after your code runs. Return links as Link columns, not plain text.
  • Slow reports become prepared reports. If a Script Report runs for more than about 15 seconds, ERPNext switches it to a prepared report, which runs in the background and shows the result when ready. You can also set this yourself with the Prepared Report checkbox.
  • Roles. Control who can open it in the report's Roles table, which you can also change on a live site with Role Permission for Page and Report.

Try it

  1. In a custom app on a development site, create the "Sales by Item Group" Script Report.
  2. Paste the code above into the generated files and reload the report.
  3. Check the totals against the Sales Register for the same dates.
  4. Commit the report folder and run bench migrate on a second site.

Script Reports are one of the patterns we build in ERPNext as a Platform, alongside the app structure they live in.