Hello everyone,
I am currently working on a Script Report in ERPNext to organize sales invoices by customer and industry, and to compare the total invoices for two different time periods. The goal is to compare the revenue in the two time periods and display the percentage change.
Issue
I am encountering errors when running the Script Report, which seem to indicate that the time periods or date ranges might not be processed correctly. Additionally, there are inconsistencies where sometimes the customers and industries are not found, depending on the date range provided. This behavior does not follow a clear pattern, which makes it challenging to debug.
Requirement
The report should:
- Organize sales invoices by industry and customer.
- Calculate the total sales invoices for two defined time periods.
- Compare the revenue between the two periods and show the percentage change.
Example Time Periods
- Period 1: 01.01.2024 to 01.03.2024
- Period 2: 01.02.2023 to 01.04.2023
Error Messages
Here is the code I am using and the errors I am encountering:
python:
def execute(filters=None):
if not filters:
filters = {}
data = frappe.db.sql("""
WITH current_year_data AS (
SELECT
COALESCE(c.industry, 'Not Specified') AS Branche,
COUNT(DISTINCT c.name) AS Number_of_Customers_LJ,
COUNT(si.name) AS Number_of_Orders_LJ,
SUM(si.grand_total) AS Revenue_LJ
FROM
`tabSales Invoice` si
LEFT JOIN
tabCustomer c ON c.name = si.customer
WHERE
si.posting_date BETWEEN %(period1_start)s AND %(period1_end)s
GROUP BY
COALESCE(c.industry, 'Not Specified')
),
previous_year_data AS (
SELECT
COALESCE(c.industry, 'Not Specified') AS Branche,
COUNT(DISTINCT c.name) AS Number_of_Customers_VJ,
COUNT(si.name) AS Number_of_Orders_VJ,
SUM(si.grand_total) AS Revenue_VJ
FROM
`tabSales Invoice` si
LEFT JOIN
tabCustomer c ON c.name = si.customer
WHERE
si.posting_date BETWEEN %(period2_start)s AND %(period2_end)s
GROUP BY
COALESCE(c.industry, 'Not Specified')
)
SELECT
cyd.Branche,
SUM(cyd.Revenue_LJ) AS Revenue_LJ,
SUM(pyd.Revenue_VJ) AS Revenue_VJ,
ROUND(
CASE
WHEN SUM(pyd.Revenue_VJ) > 0 THEN ((SUM(cyd.Revenue_LJ) - SUM(pyd.Revenue_VJ)) / SUM(pyd.Revenue_VJ)) * 100
ELSE NULL
END,
2
) AS Revenue_Trend,
SUM(cyd.Number_of_Customers_LJ) AS Number_of_Customers_LJ,
SUM(pyd.Number_of_Customers_VJ) AS Number_of_Customers_VJ,
ROUND(
CASE
WHEN SUM(pyd.Number_of_Customers_VJ) > 0 THEN ((SUM(cyd.Number_of_Customers_LJ) - SUM(pyd.Number_of_Customers_VJ)) / SUM(pyd.Number_of_Customers_VJ)) * 100
ELSE NULL
END,
2
) AS Customer_Trend
FROM
current_year_data cyd
LEFT JOIN
previous_year_data pyd ON cyd.Branche = pyd.Branche
GROUP BY
cyd.Branche
ORDER BY
Revenue_LJ DESC
""", filters, as_dict=True)
columns = [
{"fieldname": "Branche", "label": "Industry", "fieldtype": "Data", "width": 150},
{"fieldname": "Revenue_LJ", "label": "Revenue (LJ)", "fieldtype": "Currency", "width": 150},
{"fieldname": "Revenue_VJ", "label": "Revenue (VJ)", "fieldtype": "Currency", "width": 150},
{"fieldname": "Number_of_Customers_LJ", "label": "Number of Customers (LJ)", "fieldtype": "Int", "width": 150},
{"fieldname": "Number_of_Customers_VJ", "label": "Number of Customers (VJ)", "fieldtype": "Int", "width": 150},
{"fieldname": "Revenue_Trend", "label": "Revenue Trend", "fieldtype": "Percent", "width": 150},
{"fieldname": "Customer_Trend", "label": "Customer Trend", "fieldtype": "Percent", "width": 150}
]
return columns, data
sometimes i get also this error: ### App Versions
{
"erpnext": "16.0.0-dev",
"fgv": "0.0.1",
"frappe": "15.34.1",
"non_profit": "0.0.1",
"payments": "0.0.1"
}
Route
query-report/neuscript
Traceback
Traceback (most recent call last):
File "apps/frappe/frappe/app.py", line 114, 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 223, 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 109, in generate_report_result
result = get_filtered_data(report.ref_doctype, columns, result, user)
File "apps/frappe/frappe/desk/query_report.py", line 589, in get_filtered_data
linked_doctypes = get_linked_doctypes(columns, data)
File "apps/frappe/frappe/desk/query_report.py", line 720, in get_linked_doctypes
for col, val in row:
ValueError: not enough values to unpack (expected 2, got 1)
Request Data
{
"type": "GET",
"args": {
"report_name": "neuscript",
"filters": "{\"period1_start\":\"2024-04-02\",\"period1_end\":\"2024-11-21\",\"period2_start\":\"2022-07-23\",\"period2_end\":\"2023-07-22\"}",
"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": "ValueError: not enough values to unpack (expected 2, got 1)",
"exc_type": "ValueError"
}
Maybe someone can help me on this.
Thanks