Pie chart which contains an amount of elements that depends on the value given to a variable

Grafana Dashboards
1

Hi!

I am trying to create a panel with a pie chart which contains an amount of elements that depends on the value given to a variable.

Currently, I have created a number of SQL queries in the pie chart that look as follows:

SELECT SUM(mvalue) AS "room $room1" FROM periodic_measurements WHERE (apartmentid = $apartment AND roomid = $room1 AND metric = 5 AND $__timeFilter(mtimestamp))

where room1 is a query variable defined as follows:

SELECT DISTINCT roomid FROM periodic_measurements WHERE apartmentid = $apartment AND roomid != 0 ORDER BY roomid LIMIT 1 OFFSET 0;

I have created 10 of such variables for the first 10 rooms and each of the 10 queries in the Grafana pie chart panel uses one of such variables.

Each of such SQL queries in the Grafana panel provides a single numeric value.

However, each apartment has a different number of rooms so I am trying to find a more dynamic way.

Once the value of the variable apartment has been assigned, Grafana should show the energy of all the rooms of the apartment in the pie chart.

Is there any way Grafana could accept a SQL query containing a loop as input for a pie chart?

I was thinking something like this (tested in SQL CLI):

DO $$
DECLARE
    number_of_rooms INT;
    room_ids INT [];
    room_energy FLOAT(6);
BEGIN
    SELECT count(DISTINCT roomid) INTO number_of_rooms FROM periodic_measurements WHERE apartmentid = 4 AND roomid != 0;
    SELECT ARRAY(SELECT DISTINCT roomid INTO room_ids FROM periodic_measurements WHERE apartmentid = 4 AND roomid != 0 ORDER BY roomid);
    FOR counter IN 1..number_of_rooms LOOP
        SELECT SUM(mvalue) INTO room_energy FROM periodic_measurements WHERE (apartmentid = 4 AND roomid = room_ids[counter] AND metric = 5 AND mtimestamp::TEXT LIKE '2023-05-15%');
        RAISE NOTICE 'room %: %', room_ids[counter], room_energy;
    END LOOP;
END $$;

where the values of room_energy would be used in the pie chart and room %, room_ids[counter] would go into the legend.

I am running Grafana version 9.5.1. on Linux Ubuntu 22.04.2.

Thanks a lot in advance!

Bernardo

2

How many pie charts do you have?

3

Just one.

4

is it possible to do this in the database type you are using. that is pivot the table then you can get the following

 SELECT  [Room1], [Room2], [Room3]  
FROM  
(
	select mvalue, concat('Room',roomid) as Room 
	 from periodic_measurements
	 where apartmentid = $apartmentid 
	 AND roomid in ($roomid)
	 AND $__timeFilter(mtimestamp)
) AS SourceTable  
PIVOT  
(  
  SUM(mvalue)  
  FOR Room IN ([Room1], [Room2], [Room3])  
) AS PivotTable;

image
image1563×618 60.5 KB

5

Hi!

Thanks for your answer!

The database I am using is Timescale (postgreSQL) and therefore I cannot use the PIVOT command.

Thanks to your hint, I have modified the script in the panel query as follows:

SELECT
  SUM(CASE WHEN roomid = $room1 THEN mvalue END) AS "room $room1"
  ,SUM(CASE WHEN roomid = $room2 THEN mvalue END) AS "room $room2"
  ,SUM(CASE WHEN roomid = $room3 THEN mvalue END) AS "room $room3"
  ,SUM(CASE WHEN roomid = $room4 THEN mvalue END) AS "room $room4"
  ,SUM(CASE WHEN roomid = $room5 THEN mvalue END) AS "room $room5"
  ,SUM(CASE WHEN roomid = $room6 THEN mvalue END) AS "room $room6"
  ,SUM(CASE WHEN roomid = $room7 THEN mvalue END) AS "room $room7"
  ,SUM(CASE WHEN roomid = $room8 THEN mvalue END) AS "room $room8"
  --,SUM(CASE WHEN roomid = $room9 THEN mvalue END) AS "room $room9"
  --,SUM(CASE WHEN roomid = $room10 THEN mvalue END) AS "room $room10"
FROM periodic_measurements
WHERE (apartmentid = $apartment AND metric = 5 AND $__timeFilter(mtimestamp));

where each of the variables roomx are defined separately as:

SELECT DISTINCT roomid FROM periodic_measurements WHERE apartmentid = $apartment AND roomid != 0 ORDER BY roomid LIMIT 1 OFFSET x-1;

This is much cleaner than using separate queries for each room but it has still got few problems:

  1. If a room does not exist I get an error. For example, if the apartment has only 8 rooms I have to comment the last 2 lines of code as shown.
  2. If a new apartment is added with more than 10 rooms, I have to add more lines.

Is there a way I could use a DO statement in the Grafana panel query in order to be able to use a FOR loop?

King regards,

Bernardo

6

What if tomorrow you inherit 10 more apartments from the old lady next door with 100 rooms each, this approach is just not sustainable and feels very clunky. The same with doing a loop.

Also look into using maybe Transformation tab Group By Matrix

Actually the best thing you could do is ask this question on a postgres forum as this is really not a grafana question but a postgres question