Dynamic query generation based on the variable selection which will be a large dataset

  • What Grafana version and what operating system are you using?
    version-11.2.0 and deployed in kubernetes cluster

  • What are you trying to achieve?
    i have dropdown variable created called cell which will have around 20k values and i am trying to create time series chart for displaying line trend of the kpi for each cell.So if user selects 3 cells in dropdown then 3 lines should be available in time series chart.

I got stuck with query,where query statement should dynamically create based on the cells selected in dropdown variable.Sample query pasted below,

SELECT 
    toDateTime(date_column) AS time,
    SUM(CASE WHEN `cell name` ='cell1' THEN `kpi1` ELSE 0 END) AS `cell1`,
    SUM(CASE WHEN `cell name` ='cell2' THEN `kpi1` ELSE 0 END) AS `cell2`
FROM 
    db_name.table_name
WHERE 
    PERIOD_START_TIME BETWEEN 
        toDateTime(1733077800000 / 1000) AND toDateTime(1733336999000 / 1000)
GROUP BY 
    time
ORDER BY 
    time;

I above query,below statement should dynamically created for each cells selected in dropdown…this is the place i got stuck,suppose if cell1,cell2,cell3 has been selected below statement should get generated thrice for cell1,cell2,cell3

SUM(CASE WHEN cell name =‘cell1’ THEN kpi1 ELSE 0 END) AS cell1,

  • How are you trying to achieve it?

when i hardcode few cells then it is working fine but when i use (${cell}) in the query,value selected in the cell dropdown is getting substituted but not generating the full sum statement for each cell selected.Since looping is not available this is reaaly hard to achieve

  • What happened?
    i tried with group,rows to field,organise by field name transformation…but not working

  • What did you expect to happen?

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

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

  • Did you follow any online instructions? If so, what is the URL?

seeking the support here

Hi,

I don’t think it would be possible (doesn’t mean it isn’t). Is it crucial that those are in the same panel? There’s an option Repeat for <variable> that would create identical copies for each value of the selected variable. In your example it would be three panels next to each other (or one above another).

  1. What is your datasource
  2. Is this correct kpi1 or should it be kpi2?
    CASE WHEN cell name='cell2' THENkpi1 ELSE 0 END
  3. Is your table design have the following?
create table table_name(
[cell name] varchar(50),
[kpi1] decimal(10,3),
[kpi2] decimal(10,3),
[kpi3] decimal(10,3)
)

if so this design is problematic and this is why you are attempting dynamic query that would result in very fragile query design and performance bottleneck.