Business Variable

Grafana Dashboards
,
1

*** What Grafana version and what operating system are you using?**
I use Grafana v9.5.19

*** What are you trying to achieve?**
apply and link the data to use the variable to have dynamic dashboard

  • How are you trying to achieve it?
    by linking the variables with query and have dynamic filters

  • What happened?

the variables is correct but when i link it to the query , it cues tow things

1- didn’t work or show any data
2- error, by not read or recognize the element in the database

  • What did you expect to happen?
    the filters work and the variables linked correctly

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

image
image1873×2062 231 KB

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

image
image1920×878 79.1 KB

select orign_EN ,count (ItemCode) as ‘Total shipments’ FROM DBMaster.V2 WHERE orign_EN = ‘$test’
GROUP BY orign_EN order by count (ItemCode) desc

Status: 500. Message: db query error: mssql: Incorrect syntax near ‘Khamis’.

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

welcome to forum

please show us the query that uses the filters. or click on Query Inspector and show us a screen shot of the query inspector

3

Even the variables are linked, this issue is still there

image
image1870×360 36.5 KB

this the query that i use :
select orign_EN ,count (ItemCode) as ‘Total shipments’ FROM DBMasterUn_V2 WHERE orign_EN = ‘$test’
GROUP BY orign_EN order by count (ItemCode) desc

and for the variables:

SELECT DISTINCT orign_EN FROM DBMasterUn_V2 WHERE orign_EN IS NOT NULL ORDER BY orign_EN

4

now click the Query Inspector button and see what the actual query looks like

5

select orign_EN ,count (ItemCode) as ‘Total shipments’ FROM DBMasterUn_V2 WHERE orign_EN = ‘’ Khamis Mushayt post office’,’ mosadeah Dcs’,’ Sawyer’,’ Tabarjal’,‘Abanat’,‘Abdullah Fouad’,‘Abha Central Post’,‘Abhor’,‘ABO SUOD’,‘Abqaiq’,‘Abu Arish’,‘Adhem’,‘Afeef’,‘AFIF Dcs’,‘Ahad Al Masarhah’,'Ahsa Central Post ',‘AJAWEED’,‘AJF 27’,‘Al Aflaj’,‘Al Aidabi’,‘Al Ais’,‘Al Akrbiyah’,‘Al Aqeeq’,‘Al Arda’,‘Al Awali’,‘Al Awamiya’,‘Al Badaya’,‘Al Badi’‘ah’,‘Al Baghdadiya’,‘Al Bark’,‘Al Batha’‘a’,‘Al Bawadi’,‘Al bjadih’,‘Al Bukayriyah’,‘Al Dae’‘r (Bani Malik)’,‘Al Dalam’,‘Al Dawadmi’,‘Al Derayih’,‘Al Derb (Al Qyias)’,‘Al Dulayimiya’,‘Al Faisaliah’,‘Al Fawarah’,‘Al Ghazala’,‘Al Hafayer’,‘Al Haiet’,‘Al Ha’‘ir’,'Al Hajra ',‘Al Haram’,‘Al Hareeq’,‘Al Hasa Farma Operation Center’,‘Al Hawiyah’,‘Al Henakia’,‘Al Itisalat District’,‘Al Jabriya’,‘Al Jame’‘a’,‘Al Jmom’,‘Al Jouma’‘a’,‘AL KHAFJI Dcs’,‘Al Khalidiya’,‘Al Kharj’,‘Al Kharmh’,‘Al Khata’,‘Al Khobra’,‘Al Laith’,‘Al Madhleef’,‘Al Majarda’,‘Al Majma’‘a’,‘Al Makhwah’,‘Al Malaz’,‘Al Maznab’,‘Al Mendaq’,‘Al Mesa’‘ediya’,‘Al Misfalah’,‘Al Moueh’,‘Al Mountaza’,‘Al Mubarraz’,‘Al Muhamadiya’,‘Al Muzahmiah’,‘Al Naeiria post office’,‘Al Namas’,‘Al Nassej’,‘Al Nassem’,‘Al Nbhanah’,‘Al Nour Mountain’,‘Al Nuzha’,‘Al Omran’,‘Al Ouayina and Al Jubaila’,‘Al Ouoon’,‘Al Qadisiyah’,‘Al Qahma’,‘Al Qaisumah’,‘Al Qassab’,‘Al Qeblatain’,‘Al Qrra’,'Al Quayeyah ',‘Al Qunfudah’,‘Al Quoz’,‘al Qura’,‘Al Qurayyat’,‘Al Rakah’,‘Al Rass’,‘Al Rawabi’,‘Al Rawda’,‘Al Rayan’,‘Al Rhine’,‘Al Rouna’,‘Al Rweidh’,‘Al Safa’,‘Al Sahn Bani Sa’‘ad’,‘Al Sail Al Kebeer’,‘Al Salama’,‘Al Salhia’,‘AL SAMER office’,‘Al Sefarat’,‘Al Seleel’,‘Al Sena’‘iah’,‘Al Shamasiya’,‘Al Shemli’,‘Al Shifa’,‘Al Shoua’‘ra’,‘Al Shqeq’,‘Al Shsha’,‘Al Sinayia Al Thania’,‘Al Tahliya’,‘Al Tarf’,‘Al Ula’,‘Al Umrah’,‘Al Wajha’,'Al Zohour District ',‘Al Zulfi’,‘ALBSATEEN’,‘Aldayery’,‘Aljroshi Mall Boxes’,‘AlKhobar Dcs’,‘Almlqa Area’,‘Alnoor District Office-Dammam’,‘AL-Olaya Dark Store’,‘Alwadi’,‘Alyasmeen Dark Store’,‘AlYasmin Office’,‘Amlaj’ ‘’
GROUP BY orign_EN order by count (ItemCode) desc

6

do you see the issue?

WHERE orign_EN = ‘’ Khamis Mushayt post office’,’ mosadeah Dcs’,’ Sawyer’

what do you see could be the issue with this sql query

7

Oh, but is the space the issue?

8

even when I change the query, and the error, it shows no data at all

9

should be WHERE orign_EN in (‘etc’)

10

i try it , the same issue :frowning:

11

ahow us the query inspector result

12

I fix it, the issue was in the format, I use the basic query in the variable:

SELECT DISTINCT orign_EN 
FROM DBMasterUn_V2

and in the SQL query

SELECT orign_EN, COUNT(ItemCode) AS 'Total shipments' 
FROM DBMasterUn_V2 
WHERE orign_EN IN ($test) 
GROUP BY orign_EN 
ORDER BY COUNT(ItemCode) DESC;

The proviso in where condition , i was trying by

WHERE orign_EN = '$test' -- and this the cuses of the issue

It supposed to be

WHERE orign_EN IN ($test)

Thank you for your effort and for your support

1 Like