Frappe Script Reports with the Python Query Builder

One of the greatest strengths of the Frappe Framework is its incredible flexibility. While ERPNext offers a wide array of standard reports, you'll inevitably need to create custom reports tailored to your unique business processes. This is where Script Reports shine. ✨

Unlike simple Query Reports, Script Reports allow you to harness the full power of Python to fetch, join, aggregate, and calculate data from multiple sources. This gives you complete control over the final output, enabling you to build truly insightful analytics for your business.

In this guide, we'll walk through the fundamental building blocks of creating a Script Report from scratch. We'll use a real-world example—a "Sewing Planning" report for a manufacturing company—to illustrate each concept. By the end, you'll have a solid foundation to build any report you can imagine. 🚀

🎯 Defining Our Goal: The Sewing Planning Report

First, let's define what we want to build. Our goal is a report that shows a summary of items from Sales Orders, grouped by style and color. It should provide a clear overview for the production planning team.

Our target output will look something like this:

To achieve this, we'll need to fetch and combine data from the Sales Order DocType and its child table, Sales Order Item.

Step 1: Create the Report DocType 🏗️

Every report in Frappe begins as a Report document. But before we create it, we need to enable a crucial setting.

Prerequisite: Enable Developer Mode

First, ensure your Frappe instance is in developer mode. This special mode allows Frappe to create the physical report files (.py, .js, .json) in your app's directory when you save the new Report DocType.

You can enable it by running this command in your bench directory:

bench set-config developer_mode 1

Tip: Your bench directory is the folder where the Procfile resides.

Then, restart your bench with bench restart for the change to take effect.

Creating the Report Document

Now, let's create the report itself:

1. In the Awesome Bar, search for Report List and click "New".
2. Report Name: Give your report a clear, descriptive name (e.g., "Sewing Planning").
3. Ref DocType: Choose a relevant DocType. Since our report is based on sales orders, we'll select Sales Order. This is a great feature, as it allows users to click a row in the report and navigate directly to the corresponding document.
4. Is Standard: Set this to Yes. This tells the system it's a developer-created report, not a user-generated one.
5. Report Type: Select Script Report. This is the key step that tells Frappe to look for our Python and JavaScript files.
6. Module: Assign the report to one of your custom modules to keep your work organized.
7. Save.

Upon saving, Frappe will automatically create a new directory for your report inside your custom app's module folder (e.g., my_app/my_module/report/sewing_planning/). This folder will contain three essential files:

• sewing_planning.py: Where our backend Python logic will live.
• sewing_planning.js: For frontend code, like adding custom filters.
• sewing_planning.json: The report's metadata (you rarely need to touch this).

Step 2: Define the Columns with get_columns 📊

Let's open our Python file (sewing_planning.py) and start coding. The first building block is the execute function, which Frappe calls to run the report. This function, in turn, will call two other functions we'll create: get_columns and get_data.

The get_columns function returns a list of dictionaries, where each dictionary defines a single column in our report's table.

# sewing_planning.py

import frappe
from frappe import _

def execute(filters=None):
    columns = get_columns()
    data = get_data(filters) # We'll build this next
    return columns, data

def get_columns():
    """Defines the columns for our report."""
    columns = [
        {
            "label": _("Sales Order"),
            "fieldname": "sales_order",
            "fieldtype": "Link",
            "options": "Sales Order",  # Makes the value a clickable link to the SO
            "width": 160,
        },
        {
            "label": _("Customer"),
            "fieldname": "customer",
            "fieldtype": "Link",
            "options": "Customer", # Link to the Customer document
            "width": 130,
        },
        {
            "label": _("Style"),
            "fieldname": "style",
            "fieldtype": "Data",  # A simple text field
            "width": 130,
        },
        {
            "label": _("Order Qty"),
            "fieldname": "order_qty",
            "fieldtype": "Float", # A number field
            "width": 100,
        },
    ]
    return columns

Key Takeaway: The fieldname is critical. It acts as a unique ID for each column and is used to map the data we fetch in the next step to the correct column.

A Quick Intro to Frappe Query Builder (Powered by PyPika) 🛠️

Before we write the data-fetching logic, let's talk about the tool we'll be using: the Frappe Query Builder, accessible via frappe.qb.

Think of the Query Builder as a safe and elegant way to build SQL queries using Python. Instead of manually writing raw SQL strings like SELECT customer, style FROM ..., which can be error-prone and vulnerable to security risks, the Query Builder provides a fluent, chainable interface.

Why is it better?

• *�️ Secure: It automatically sanitizes your inputs, providing excellent protection against SQL injection attacks.
• *� Readable: The Pythonic syntax is often much easier to read and maintain than complex, multi-line SQL strings.
• *� Portable: It abstracts the database layer. The same Query Builder code will work seamlessly whether your Frappe instance runs on MariaDB or PostgreSQL.

Under the hood, the Frappe Query Builder is a powerful wrapper around PyPika, a popular open-source SQL query builder library. This means we get all the power and reliability of a dedicated library, perfectly integrated into the Frappe framework.

Now, let's see it in action

Step 3: Fetch the Data with get_data and Query Builder 💾

This is the heart of our report. Our challenge is that some data (Customer) is in the parent Sales Order DocType, while other data (Style, Order Qty) is in the Sales Order Item child table. This requires a JOIN.

The fundamental logic is to start your query from the most detailed table (the child table) and join "up" to the parent.

# sewing_planning.py (add this function)

from frappe.query_builder import DocType

def get_data(filters):
    """Fetches the data for our report using Frappe Query Builder."""
    # 1. Define the DocTypes (tables) you need
    SalesOrder = DocType("Sales Order")
    SalesOrderItem = DocType("Sales Order Item")

    # 2. Build the query
    query = (
        frappe.qb.from_(SalesOrderItem)  # Start from the child table
        .join(SalesOrder)  # Join the parent table
        .on(SalesOrderItem.parent == SalesOrder.name)  # Define the join condition
        .select(
            SalesOrder.name.as_("sales_order"),  # Use .as_() to match column fieldnames
            SalesOrder.customer.as_("customer"),
            SalesOrderItem.style_no.as_("style"),  # Assuming 'style_no' is a custom field in Sales Order Item
            SalesOrderItem.qty.as_("order_qty"),
        )
        .where(SalesOrder.docstatus == 1)  # Always filter for submitted documents
    )

    # 3. Run the query and return the data as a list of dictionaries
    return query.run(as_dict=True)

Key Takeaway: The structure from_(child).join(parent).on(child.parent == parent.name) is a reusable pattern for nearly any report involving parent-child DocTypes.

Step 4: Add Interactive Filters 🎛️

A static report is good, but an interactive one is great. Let's empower users to filter the report by "Customer".

First, define the filter in the JavaScript file (sewing_planning.js):

// sewing_planning.js

frappe.query_reports["Sewing Planning"] = {
  filters: [
    {
      fieldname: "customer",
      label: __("Customer"),
      fieldtype: "Link",
      options: "Customer", // This creates a dropdown with all customers
    },
  ],
};

Next, use the filter value in your Python get_data function:

# sewing_planning.py (updated get_data function)

def get_data(filters):
    SalesOrder = DocType("Sales Order")
    SalesOrderItem = DocType("Sales Order Item")

    query = (
        frappe.qb.from_(SalesOrderItem)
        .join(SalesOrder)
        .on(SalesOrderItem.parent == SalesOrder.name)
        .select(
            # ... same select fields as before
        )
        .where(SalesOrder.docstatus == 1)
    )

    # --- Apply Filters ---
    # The 'filters' dictionary contains values from the JS file
    if filters.get("customer"):
        query = query.where(SalesOrder.customer == filters.get("customer"))
    # ---------------------

    return query.run(as_dict=True)

Key Takeaway: The filters dictionary acts as a bridge, passing user selections from the frontend UI to your backend query. You can add as many filters as you need by repeating this pattern for dates, custom fields, and more.

Step 5: Aggregate and Calculate Data 🧮

What if we want a summary view—just one row per style, with the total quantity summed up? This requires aggregation.

Let's modify our query to group the results and perform calculations.

# sewing_planning.py (updated get_data function)

from frappe.query_builder.functions import Sum, Count

def get_data(filters):
    SalesOrder = DocType("Sales Order")
    SalesOrderItem = DocType("Sales Order Item")

    query = (
        frappe.qb.from_(SalesOrderItem)
        .join(SalesOrder)
        .on(SalesOrderItem.parent == SalesOrder.name)
        .select(
            SalesOrder.customer.as_("customer"),
            SalesOrderItem.style_no.as_("style"),
            Sum(SalesOrderItem.qty).as_("order_qty"), # Use Sum() for aggregation
            Count(SalesOrderItem.color).distinct().as_("no_of_colors"), # Count unique colors
        )
        .where(SalesOrder.docstatus == 1)
        .groupby(SalesOrderItem.style_no, SalesOrder.customer) # Group by all non-aggregated fields
    )

    # Apply filters as before
    if filters.get("customer"):
        query = query.where(SalesOrder.customer == filters.get("customer"))

    data = query.run(as_dict=True)

    # Perform post-query calculations that can't be done in SQL
    for row in data:
        # Example: Calculate a 'due_qty' based on another fetched value
        row["due_qty"] = row.get("order_qty", 0) - row.get("planned_qty", 0)

    return data

Key Takeaways:

1. Aggregation: Use functions like Sum, Count, and Avg from frappe.query_builder.functions.
2. Grouping: When you use an aggregate function (Sum, Count, etc.), you must group the results by all the other columns you are selecting.
3. Post-Processing: For complex logic or calculations that depend on other calculated fields (like our due_qty), perform them in a simple Python loop after you've fetched the initial data from the database.

By following these fundamental building blocks—defining columns, fetching and joining data, adding filters, and performing calculations—you can systematically construct almost any custom report imaginable in Frappe and ERPNext. Happy coding! 🎉