I’m struggling trying to build script reports. Gemini and ChatGPT don’t have any good resource for the limitations in the GUI vs backend files.
I’m unable to get variable replacements working for filters.
The following python works when I hard code the dates.
# Report Doctype -> Script Field (Python)
def execute(filters=None):
# 1. Initialize filters if none are provided (for safety)
if not filters:
filters = {}
# 2. Define the columns (headers) for the report
# This is essential for the Script Report to know the table structure.
columns = [
{"fieldname": "account", "label": "Account", "fieldtype": "Link", "options": "Account", "width": 300},
{"fieldname": "amount", "label": "Amount", "fieldtype": "Currency", "width": 150}
]
# 3. Get the dates from the filters dictionary
# Frappe automatically populates 'from_date' and 'to_date' if you've defined them in the GUI.
from_date = filters.get('from_date')
to_date = filters.get('to_date')
# 4. Build and Execute the SQL Query
# We use %(filter_name)s placeholders and pass the filters dictionary to the sql function.
journal_entries = frappe.db.sql("""
SELECT
sca.account AS account,
SUM(sd.amount) AS amount
FROM `tabSalary Detail` sd
INNER JOIN `tabSalary Slip` ss ON ss.name = sd.parent
INNER JOIN `tabSalary Component` sc ON sd.salary_component = sc.name
INNER JOIN `tabSalary Component Account` sca ON sca.parent = sd.salary_component
WHERE ss.docstatus = 1
AND sca.account IN ("Fed WT - VOLT", "Social Security Payable - VOLT", "Medicare Payable - VOLT")
AND ss.end_date BETWEEN "2025-11-01" AND "2025-11-30"
GROUP BY sca.account
ORDER BY sd.amount DESC
""", filters, as_dict=True)
# 5. Return the results
# For a Script Report, the function must return (columns, data).
return journal_entries
result = execute()
If I try to use variables like below:
# Report Doctype -> Script Field (Python)
def execute(filters=None):
# 1. Initialize filters if none are provided (for safety)
if not filters:
filters = {}
# 2. Define the columns (headers) for the report
# This is essential for the Script Report to know the table structure.
columns = [
{"fieldname": "account", "label": "Account", "fieldtype": "Link", "options": "Account", "width": 300},
{"fieldname": "amount", "label": "Amount", "fieldtype": "Currency", "width": 150}
]
# 3. Get the dates from the filters dictionary
# Frappe automatically populates 'from_date' and 'to_date' if you've defined them in the GUI.
from_date = filters.get('from_date')
to_date = filters.get('to_date')
# 4. Build and Execute the SQL Query
# We use %(filter_name)s placeholders and pass the filters dictionary to the sql function.
journal_entries = frappe.db.sql("""
SELECT
sca.account AS account,
SUM(sd.amount) AS amount
FROM `tabSalary Detail` sd
INNER JOIN `tabSalary Slip` ss ON ss.name = sd.parent
INNER JOIN `tabSalary Component` sc ON sd.salary_component = sc.name
INNER JOIN `tabSalary Component Account` sca ON sca.parent = sd.salary_component
WHERE ss.docstatus = 1
AND sca.account IN ("Fed WT - VOLT", "Social Security Payable - VOLT", "Medicare Payable - VOLT")
AND ss.end_date BETWEEN %(from_date)s AND %(to_date)s
GROUP BY sca.account
ORDER BY sd.amount DESC
""", filters, as_dict=True)
# 5. Return the results
# For a Script Report, the function must return (columns, data).
return journal_entries
result = execute()
I get error:
### Response Data
```
{
exception: "KeyError: 'from_date'",
exc_type: "KeyError",
_exc_source: "Server Script"
}
Are there any good sources that explain the requirements for Script Reports in the GUI?
EDIT: OMG, I finally got it. I finished the script with:
result = execute(filters=filters)