Chart from summary query

ERPNext
1

Hi,

I am trying to create a chart from a summarized data. Query is working fine and display data in 4 rows and 2 columns. I need this to be presented as bar chart.
I am getting Key error while running.

columns = [
_(“RepGroup”),
_(“Total Amount”),
]

data = frappe.db.sql(“”"
SELECT
RepGroup,
SUM(amount) AS total_amount
FROM (
SELECT ‘Opportunity’ AS RepGroup, opportunity_amount AS amount
FROM tabOpportunity
WHERE creation >= ‘2025-01-01’

    UNION ALL

    SELECT 'Quote' AS RepGroup, total AS amount
    FROM tabQuotation
    WHERE transaction_date > '2025-01-01'
        AND docstatus = 1
        AND status <> 'Lost'
        AND custom_vinton_status NOT IN ('WFC', 'WFA')

    UNION ALL

    SELECT 'WFC' AS RepGroup, total AS amount
    FROM tabQuotation
    WHERE transaction_date > '2025-01-01'
        AND docstatus = 1
        AND status <> 'Lost'
        AND custom_vinton_status = 'WFC'

    UNION ALL

    SELECT 'WFA' AS RepGroup, total AS amount
    FROM tabQuotation
    WHERE transaction_date > '2025-01-01'
        AND docstatus = 1
        AND status <> 'Lost'
        AND custom_vinton_status = 'WFA'

    UNION ALL

    SELECT 'Order' AS RepGroup, total AS amount
    FROM `tabSales Order`
    WHERE docstatus = 1
        AND transaction_date >= '2025-01-01'
) AS unioned_data
GROUP BY RepGroup
ORDER BY FIELD(RepGroup, 'Opportunity', 'Quote', 'WFC', 'WFA', 'Order');

“”", as_dict=True)

def get_chart_data(data):
“”“Generate pie chart configuration”“”
labels =
values = 

for row in data:
    labels.append(row.get('RepGroup'))
    values.append(row.get('total_amount', 0))

return {
    "data": {
        "labels": labels,
        "datasets": [
            {
                "name": "Sales Funnel Breakdown",
                "values": values
            }
        ]
    },
    "type": "pie",
    "colors": ["#e74c3c", "#f39c12", "#2ecc71", "#9b59b6", "#3498db"]
}

Generate the chart before returning

chart = get_chart_data(data)

Final return structure

result = columns, data, None, chart, None