Too many values to unpack in report

ERPNext User Forum
1

Hi
I am trying to write a report to show the number of tasks Open and Completed, based
on the task-priority : LOW, MEDIUM, HIGH, URGENT. Ultimately , I want to add a bar-graph , but for the moment I just want to get the query right.

The acutal SQL query ( that works ) is like this …

WITH AllCombinations AS (
  SELECT
    'Low' AS priority,
    'Open' AS status
  UNION ALL
  SELECT
    'Low',
    'Completed'
  UNION ALL
  SELECT
    'Medium',
    'Open'
  UNION ALL
  SELECT
    'Medium',
    'Completed'
  UNION ALL
  SELECT
    'High',
    'Open'
  UNION ALL
  SELECT
    'High',
    'Completed'
)

SELECT
  ac.priority,
  ac.status,
  COUNT(t.name) AS task_count
FROM
  AllCombinations ac
LEFT JOIN
  tabTask t ON ac.priority = t.priority AND ac.status = t.status
  AND t.project = 'PROJ-0036'
GROUP BY
  ac.priority, ac.status;

So I built this into a client-side report …

def get_data(filters):
    # Run the SQL query
    result = frappe.db.sql(
        """
        WITH AllCombinations AS (
            SELECT
                'Low' AS priority,
                'Open' AS status
            UNION ALL
            SELECT
                'Low',
                'Completed'
            UNION ALL
            SELECT
                'Medium',
                'Open'
            UNION ALL
            SELECT
                'Medium',
                'Completed'
            UNION ALL
            SELECT
                'High',
                'Open'
            UNION ALL
            SELECT
                'High',
                'Completed'
            UNION ALL
            SELECT
                'Urgent',
                'Open'
            UNION ALL
            SELECT
                'Urgent',
                'Completed'
        )   
        SELECT
            ac.priority AS priority,
            ac.status AS status,
            COUNT(t.name) AS task_count
        FROM
            AllCombinations ac
        LEFT JOIN
            tabTask t ON ac.priority = t.priority AND ac.status = t.status
            AND t.project = 'PROJ-0036'
        GROUP BY
            ac.priority, ac.status;
        """,
        filters,
        as_dict=True  # This will return results as dictionaries instead of tuples
    )

    return result

# Define columns to match query structure
columns = [
    {
        "fieldname": "priority",
        "fieldtype": "Data",
        "label": "Priority",
        "width": 120
    },
    {
        "fieldname": "status",
        "fieldtype": "Data",
        "label": "Status",
        "width": 120
    },
    {
        "fieldname": "count",
        "fieldtype": "Int",
        "label": "Count",
        "width": 100
    },
]

# Fetch and inspect data
filters = {"project": "PROJ-0036"}
query_data = get_data(filters)
frappe.msgprint(f"Query Data: {query_data}")  # Inspect data structure

data = query_data  # Populate data directly for report

From the msgprint dialog on the screen . the data that the query is yielding is correct , but I am getting a

ValueError: too many values to unpack (expected 6)

error.

Can anyone help me out , please ?

2

I think, you have to create a custom server script report on custom app because you can easily develop.

3

Thank you @NCP , let me do that and see if that works better.

4

@trainingacademy Please check this code in Query Report then your report will work

WITH AllCombinations AS (
  SELECT
     'Low' AS priority, 'Open' AS status
  UNION ALL
  SELECT
     'Low','Completed'
  UNION ALL
  SELECT
     'Medium', 'Open'
  UNION ALL
  SELECT
     'Medium', 'Completed'
  UNION ALL
  SELECT
      'High','Open'
   UNION ALL
   SELECT
       'High','Completed'
   UNION ALL
   SELECT
       'Urgent', 'Open'
    UNION ALL
    SELECT
        'Urgent', 'Completed'
)
SELECT
  ac.priority,
  ac.status,
  IFNULL(COUNT(t.name), 0) AS task_count
FROM
  AllCombinations ac
LEFT JOIN
  tabTask t ON ac.priority = t.priority AND ac.status = t.status
  AND t.project = 'PROJ-0036'
GROUP BY
  ac.priority, ac.status
ORDER BY
  FIELD(ac.priority, 'Low', 'Medium', 'High'),
  FIELD(ac.status, 'Open', 'Completed');