Overview

Script Reports in iVendNext use Python for advanced, server-side reporting with dynamic filters, custom formatting, and multi-dataset support—features beyond Report Builder and Query Reports. This article explains their use, setup, and admin controls (not editable in cloud).

1. What Are Script Reports?

Script Reports are Python-driven reports that:
✔ Perform calculations (e.g., financial ratios, inventory forecasting).
✔ Combine data from multiple tables or modules.
✔ Support dynamic filters and conditional logic.

Example: The "Financial Analytics" report in the Accounts module is a Script Report.

Key Limitation:
Script Reports cannot be edited in hosted/cloud environments due to server-side restrictions. Only on-premise users or developers can modify them.

2. When to Use Script Reports

3. Structure of a Script Report

A Script Report consists of:

A. Python Logic

• Fetches data using frappe.db.sql or frappe.get_all.

• Processes data with Pandas, NumPy, or custom functions.

B. Dynamic Filters

Defined as a filters dictionary for user input.

C. Output Formatting

Returns data as a list of dictionaries for HTML/PDF rendering.

4. Creating a Script Report (For On-Premise Users)

Step 1: Access the Script Report Builder

1. Go to Customize > Report > New Report.

2. Select Report Type: Script Report.

Step 2: Write the Python Script

Example: A report calculating profit margins by item:

import frappe  

def execute(filters=None):  

    columns = [  

        {"label": "Item", "fieldname": "item", "fieldtype": "Link", "options": "Item"},  

        {"label": "Profit Margin (%)", "fieldname": "margin", "fieldtype": "Percent"}  

    ]  

    data = frappe.db.sql("""  

        SELECT  

            item.item_code AS item,  

            (SUM(sales.amount) - SUM(sales.cost)) / SUM(sales.amount) * 100 AS margin  

        FROM `tabSales Invoice Item` sales  

        JOIN `tabItem` item ON sales.item_code = item.name  

        WHERE sales.posting_date BETWEEN %(from_date)s AND %(to_date)s  

        GROUP BY item.item_code  

    """, filters, as_dict=1)  

    return columns, data  

Step 3: Define Filters

Add interactive filters in the filters dictionary:

filters = [  

    {  

        "fieldname": "from_date",  

        "label": "From Date",  

        "fieldtype": "Date",  

        "default": frappe.utils.add_months(frappe.utils.nowdate(), -1)  

    },  

    {  

        "fieldname": "to_date",  

        "label": "To Date",  

        "fieldtype": "Date",  

        "default": frappe.utils.nowdate()  

    }  

]  

Step 4: Test and Deploy

1. Click Save.

2. Run the report with test filters to validate results.

5. Advanced Features

A. Conditional Formatting

Highlight rows based on rules (e.g., red for negative margins):

for row in data:  

    row["margin_color"] = "red" if row["margin"] < 0 else "green"  

B. Multi-Dataset Reports

Combine data from multiple queries:

sales_data = frappe.db.sql("""SELECT ...""", filters)  

purchase_data = frappe.db.sql("""SELECT ...""", filters)  

C. External API Calls

Fetch real-time data (e.g., currency rates):

import requests  

response = requests.get("https://api.exchangerate.host/latest")  

6. Troubleshooting

Here’s a quick look at some common issues you might run into.

Issue: "Module Not Found" Error

Cause: Missing Python library (e.g., Pandas).
Fix: Install the package on the server or use Frappe’s built-in utilities.

Issue: Slow Performance

Causes:

• Unoptimized SQL queries.

• Heavy calculations in loops.
  Fix:

• Use INDEXES in database tables.

• Limit fetched data with WHERE clauses.

Issue: Permission Errors

Cause: The user lacks access to referenced DocTypes.
Fix: Update role permissions via Customize > Permissions.