-
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