Error with Script Report: Comparison of Sales Invoices in Two Time Periods

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:

  1. Organize sales invoices by industry and customer.
  2. Calculate the total sales invoices for two defined time periods.
  3. 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

a restart of the bench fixed it.

Also, add the suggestion that if you install Frappe in version 15, then ERPNext must also be in version 15 to prevent errors.

1 Like