__text, __value not working in mysql query

  • What Grafana version and what operating system are you using?
    Grafana 11.4.0
    Alpine Linux 3.20.3

  • What are you trying to achieve?
    I would like to use one column of my result as a label and the another as the value.

It is my understanding that naming a set of columns __text, and __value is supposed to do this however I have not been able to get it to work with the pie chart visualisation

  • How are you trying to achieve it?
WITH temp AS (
    SELECT 
        c.id as "learner_id",
        CASE 
            WHEN te.id IS NULL THEN 'Absent'  -- No attendance event means absence
            WHEN aa.id IS NOT NULL THEN 'Authorised Absent'  -- If an authorised absence exists
            WHEN MIN(te.`datetime`) <= e.`date_start` AND MAX(te.`datetime`) >= e.`date_end` THEN 'Present'  -- Fully attended
            WHEN MIN(te.`datetime`) > e.`date_start` AND MAX(te.`datetime`) < e.`date_end` THEN 'Partially Absent'  -- Late or early
            ELSE 'Absent'  -- Default to absent if not matching other conditions
        END AS attendance_status
    FROM `fp_events` AS e
    INNER JOIN `fp_events_contacts_c` AS e_c
        ON e_c.`fp_events_contactsfp_events_ida` = e.`id`
        AND e_c.`deleted` = 0
    INNER JOIN `contacts` AS c
        ON e_c.`fp_events_contactscontacts_idb` = c.`id`
        AND c.`deleted` = 0
    LEFT JOIN `eng_authorisedabsences_contacts_c` AS aa_c
        ON aa_c.`eng_authorisedabsences_contactscontacts_ida` = c.`id`
        AND aa_c.`deleted` = 0
    LEFT JOIN `eng_authorisedabsences` AS aa
        ON aa.`id` = aa_c.`eng_authorisedabsences_contactseng_authorisedabsences_idb`
        AND aa.`deleted` = 0
    LEFT JOIN `eng_attendancetrackers_fp_events_c` AS t_e
        ON t_e.`eng_attendancetrackers_fp_eventsfp_events_ida` = e.`id`
        AND t_e.`deleted` = 0
    LEFT JOIN `eng_attendancetrackers_contacts_c` AS t_c
        ON t_c.`eng_attendancetrackers_contactscontacts_idb` = c.`id`
        AND t_c.`deleted` = 0
    LEFT JOIN `eng_attendancetrackers` AS t
        ON t.id = t_e.`eng_attendancetrackers_fp_eventseng_attendancetrackers_idb`
        AND t.`id` = t_c.`eng_attendancetrackers_contactseng_attendancetrackers_ida`
        AND t.`deleted` = 0
    LEFT JOIN `eng_attendancetrackers_eng_attendanceevents_c` AS t_te
        ON t_te.`eng_attend648frackers_ida` = t.`id`
        AND t_te.`deleted` = 0
    LEFT JOIN `eng_attendanceevents` AS te
        ON te.`id` = t_te.`eng_attende803eevents_idb`
        AND te.`deleted` = 0
    WHERE e.deleted = 0
    GROUP BY learner_id
)
SELECT 
    attendance_status AS __text,
    COUNT(*) AS __value
FROM temp
GROUP BY attendance_status;
  • What happened?
    the resulting pie chart has __value as the legend

  • What did you expect to happen?
    the resulting pie chart to show the value of the __text columns as the names of the sets

  • Can you copy/paste the configuration(s) that you are having problems with?
    ^----

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
    no

  • Did you follow any online instructions? If so, what is the URL?
    Add variables | Grafana documentation

I found that I needed to change an option in the value options section. From Calculate to All Fields.

This is now showing the data I was expecting.

1 Like