Script Reports: Leveraging Python for Complex Calculations
Introduction
Script Reports in iVendNext are the most advanced reporting option, allowing users to execute Python-based logic for data processing, complex calculations, and customized analytics. Unlike Report Builder or Query Reports, Script Reports are stored on the server and support dynamic filters, conditional formatting, and multi-dataset operations.
This guide explains how Script Reports work, their ideal use cases, and how administrators can create or modify them (note: not customizable for cloud users).
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
Go to Customize > Report > New Report.
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
Click Save.
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
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.
7. Security Best Practices
Sanitize Inputs: Always pass filters via %(...)s placeholders.
Restrict Access: Grant Script Report permissions only to trusted roles.
Avoid Hardcoding Secrets: Use Frappe’s Config for API keys.
Conclusion
Script Reports unlock iVendNext’s full analytical potential but require Python expertise. They are ideal for:
Financial modeling.
Custom dashboards.
Real-time data integrations.
For simpler needs, use Report Builder (no-code) or Query Reports (SQL-based).Related Articles
Operational Reports
This article provides an overview of the key reports available in iVendNext. It also shows how to create custom reports and explains how to add, remove, and rearrange columns, apply both predefined and custom filters, and sort the data. Introduction ...Understanding the Three Types of Reports
Introduction Reports are a critical component of iVendNext, enabling users to extract, analyze, and present data efficiently. This article explores the three primary types of reports available in iVendNext: Report Builder, Query Reports, and Script ...Advanced Reporting - Custom Reports and Scripts
Introduction While iVendNext provides pre-built operational reports, businesses often need tailored analytics to address unique challenges. This guide explores advanced reporting tools—including custom Report Builder templates, SQL Query Reports, and ...Working with Query Reports - Fetching Data Directly from the Database
Introduction Query Reports in iVendNext allow advanced users to extract data directly from the database using SQL (Structured Query Language). Unlike Report Builder, which offers a no-code interface, Query Reports provide granular control over data ...Dynamic Filters in Reports: When and How to Use Them in iVendNext
Introduction Dynamic filters empower users to refine report data on the fly, making them indispensable for real-time analytics in iVendNext. Unlike static filters, dynamic filters allow end-users to adjust parameters (e.g., date ranges, statuses, or ...