Sometimes we want to create Script Reports via the GUI. It’s not always practical to add reports to a custom APP (maybe we don’t want to turn on developer mode).
I’ve been struggling with creating a script report from Salary Slips. Trying with both ChatGPT and Gemini, I can’t generate a working solution. Gemini has completely given up and suggests using file file-based approach.
Is my request impossible via the GUI?
I would like to aggregate salary slips (earnings and deduction components) and group by the associated Account in the earnings component.
The following script:
# The 'filters' variable is implicitly available in this scope.
# 1. Prepare Conditions and Values
start_date = filters.get("start_date")
end_date = filters.get("end_date")
if not start_date or not end_date:
frappe.throw("Error: Both Start Date and End Date filters are required.")
conditions = ""
filter_values = {}
if start_date:
conditions += " AND ss.posting_date >= %(start_date)s"
filter_values["start_date"] = start_date
if end_date:
conditions += " AND ss.posting_date <= %(end_date)s"
filter_values["end_date"] = end_date
# 2. SQL Query: Aggregating by Account
query = (
"SELECT "
" sca.account AS account, "
" SUM(sd.amount) AS total "
"FROM `tabSalary Detail` sd "
"INNER JOIN `tabSalary Slip` ss ON ss.name = sd.parent "
"INNER JOIN `tabSalary Component Account` sca "
" ON sca.parent = sd.salary_component "
"WHERE ss.docstatus = 1 "
+ conditions +
" GROUP BY sca.account "
"ORDER BY sca.account"
)
# 3. Execute query
rows = frappe.db.sql(
query,
filter_values,
as_dict=True
)
# 4. Define Columns
columns = [
{"label": "Account", "fieldname": "account", "fieldtype": "Link", "options": "Account", "width": 200},
{"label": "Total Amount", "fieldname": "total", "fieldtype": "Currency", "width": 120},
]
# 5. Prepare final data list (Rename to final_data to avoid name collision)
final_data = []
for r in rows:
final_data.append({
"account": r.get("account") or "Unassigned",
"total": r.get("total") or 0
})
# 6. MANDATORY ASSIGNMENT
# This non-standard assignment is what certain Frappe versions expect
# for a top-level script report to return both columns and data.
data = [columns, final_data]
Generates the following error:
### App Versions
```
{
"erpnext": "15.79.2",
"erpusa": "0.0.8",
"frappe": "15.82.1",
"hrms": "15.50.0",
"payments": "0.0.1",
"raven": "2.6.4",
"wiki": "2.0.0"
}
```
### Route
```
query-report/Payroll Summary
```
### Traceback
```
Traceback (most recent call last):
File "apps/frappe/frappe/app.py", line 115, in application
response = frappe.api.handle(request)
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "apps/frappe/frappe/api/__init__.py", line 50, in handle
data = endpoint(**arguments)
^^^^^^^^^^^^^^^^^^^^^
File "apps/frappe/frappe/api/v1.py", line 36, in handle_rpc_call
return frappe.handler.handle()
^^^^^^^^^^^^^^^^^^^^^^^
File "apps/frappe/frappe/handler.py", line 52, in handle
data = execute_cmd(cmd)
^^^^^^^^^^^^^^^^
File "apps/frappe/frappe/handler.py", line 85, in execute_cmd
return frappe.call(method, **frappe.form_dict)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "apps/frappe/frappe/__init__.py", line 1754, in call
return fn(*args, **newargs)
^^^^^^^^^^^^^^^^^^^^
File "apps/frappe/frappe/utils/typing_validations.py", line 32, in wrapper
return func(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^
File "apps/frappe/frappe/__init__.py", line 891, in wrapper_fn
retval = fn(*args, **get_newargs(fn, kwargs))
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "apps/frappe/frappe/desk/query_report.py", line 229, in run
result = generate_report_result(report, filters, user, custom_columns, is_tree, parent_field)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "apps/frappe/frappe/__init__.py", line 891, in wrapper_fn
retval = fn(*args, **get_newargs(fn, kwargs))
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "apps/frappe/frappe/desk/query_report.py", line 85, in generate_report_result
res = get_report_result(report, filters) or []
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "apps/frappe/frappe/desk/query_report.py", line 66, in get_report_result
res = report.execute_script_report(filters)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "apps/frappe/frappe/core/doctype/report/report.py", line 176, in execute_script_report
res = self.execute_script(filters)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "apps/frappe/frappe/core/doctype/report/report.py", line 195, in execute_script
safe_exec(self.report_script, None, loc, script_filename=f"Report {self.name}")
File "apps/frappe/frappe/utils/safe_exec.py", line 114, in safe_exec
exec(
File "<serverscript>: report_payroll_summary", line 14, in <module>
NameError: name '_inplacevar_' is not defined
```
### Request Data
```
{
"type": "GET",
"args": {
"report_name": "Payroll Summary",
"filters": "{\"start_date\":\"2025-09-01\",\"end_date\":\"2025-10-31\"}",
"ignore_prepared_report": false,
"are_default_filters": false
},
"headers": {},
"error_handlers": {},
"url": "/api/method/frappe.desk.query_report.run",
"request_id": null
}
```
### Response Data
```
{
"exception": "NameError: name '_inplacevar_' is not defined",
"exc_type": "NameError",
"_exc_source": "Server Script"
}
```