This query is working fine and giving all data properly in system console but when I try to generate report from it, I am getting an error
WITH AssessmentData AS (
SELECT
ar.student AS Student,
ar.program AS Class,
ar.student_group AS Division,
ar.course AS Subject,
ar.total_score,
ard.maximum_score,
ard.assessment_criteria
FROM
tabAssessment Result ar
INNER JOIN
tabAssessment Result Detail ard
ON ar.name = ard.parent
),
RankedScores AS (
SELECT
Class,
Division,
Subject,
Student,
total_score,
maximum_score,
assessment_criteria,
ROW_NUMBER() OVER (PARTITION BY Class, Division, Subject, assessment_criteria ORDER BY total_score) AS rn_asc,
ROW_NUMBER() OVER (PARTITION BY Class, Division, Subject, assessment_criteria ORDER BY total_score DESC) AS rn_desc,
COUNT(*) OVER (PARTITION BY Class, Division, Subject, assessment_criteria) AS total_count
FROM
AssessmentData
)
SELECT
Class,
Division,
Subject,
assessment_criteria,
MAX(maximum_score) AS Maximum Score,
COUNT(DISTINCT Student) AS Student Count,
MAX(total_score) AS Highest,
ROUND(AVG(total_score), 3) AS Average,
MIN(CASE WHEN total_score > 0 THEN total_score ELSE NULL END) AS Minimum,
CASE
WHEN total_count % 2 = 1 THEN
MAX(CASE WHEN rn_asc = (total_count + 1) / 2 THEN total_score END)
ELSE
ROUND(
(MAX(CASE WHEN rn_desc = total_count / 2 THEN total_score END) +
MAX(CASE WHEN rn_asc = total_count / 2 + 1 THEN total_score END)) / 2,
3
)
END AS Median
FROM
RankedScores
GROUP BY
Class, Division, Subject, assessment_criteria;
And Error is
App Versions
{
"easebuzz": "0.0.1",
"edu_quality": "0.0.1",
"education": "0.0.1",
"erpnext": "15.17.6",
"frappe": "15.27.0",
"gameplan": "0.0.1",
"helpdesk": "0.10.0",
"hrms": "15.14.2",
"insights": "2.2.2",
"library_management": "0.0.1",
"nextai": "0.0.1",
"payments": "0.0.1",
"print_designer": "1.2.0",
"raven": "1.6.3",
"wiki": "2.0.0"
}
Route
query-report/Marks Analysis
Traceback
Traceback (most recent call last):
File "apps/frappe/frappe/app.py", line 110, in application
response = frappe.api.handle(request)
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "apps/frappe/frappe/api/__init__.py", line 49, 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 49, 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 1768, in call
return fn(*args, **newargs)
^^^^^^^^^^^^^^^^^^^^
File "apps/frappe/frappe/utils/typing_validations.py", line 31, in wrapper
return func(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^
File "apps/frappe/frappe/__init__.py", line 921, in wrapper_fn
retval = fn(*args, **get_newargs(fn, kwargs))
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "apps/frappe/frappe/desk/query_report.py", line 220, in run
result = generate_report_result(report, filters, user, custom_columns, is_tree, parent_field)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "apps/frappe/frappe/__init__.py", line 921, in wrapper_fn
retval = fn(*args, **get_newargs(fn, kwargs))
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "apps/frappe/frappe/desk/query_report.py", line 81, in generate_report_result
res = get_report_result(report, filters) or []
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "apps/frappe/frappe/desk/query_report.py", line 59, in get_report_result
res = report.execute_query_report(filters)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "apps/frappe/frappe/core/doctype/report/report.py", line 148, in execute_query_report
result = [list(t) for t in frappe.db.sql(self.query, filters)]
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "apps/frappe/frappe/database/database.py", line 234, in sql
self._cursor.execute(query, values)
File "env/lib/python3.11/site-packages/pymysql/cursors.py", line 151, in execute
query = self.mogrify(query, args)
^^^^^^^^^^^^^^^^^^^^^^^^^
File "env/lib/python3.11/site-packages/pymysql/cursors.py", line 129, in mogrify
query = query % self._escape_args(args, conn)
~~~~~~^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ValueError: unsupported format character ' ' (0x20) at index 1327
Request Data
{
"type": "GET",
"args": {
"report_name": "Marks Analysis",
"filters": "{}",
"ignore_prepared_report": false,
"are_default_filters": true
},
"headers": {},
"error_handlers": {},
"url": "/api/method/frappe.desk.query_report.run",
"request_id": "a970f50b-c7a4-4cc5-a00c-c8f61f681cae"
}
Response Data
{
"exception": "ValueError: unsupported format character ' ' (0x20) at index 1327",
"exc_type": "ValueError"
}