Help using template variable in a graph query to create filters

Grafana Dashboards
22

this is what the query and dashboard does … and filters works perfectly but when i export this same working dashboard and import it works but when i try to create new dashboard using same queries it wont work … its just so confusing

SELECT
count(*)
FROM enrollment
WHERE
EXTRACT(YEAR FROM benefit_effective_date) IN (‘2024’) AND

((‘Health’ IN (‘Health’,‘Dental’) AND insurance_type_lkp IN (131)) OR
(‘Dental’ IN (‘Health’,‘Dental’) AND insurance_type_lkp IN (132))) AND
((‘$0’ IN (‘$0’,‘>$0’,‘NULL’) AND aptc_amt = 0) OR
(‘>$0’ IN (‘$0’,‘>$0’,‘NULL’) AND aptc_amt > 0) OR
(‘NULL’ IN (‘$0’,‘>$0’,‘NULL’) AND aptc_amt IS NULL)) AND

((‘$0’ IN (‘$0’,‘>$0’,‘NULL’) AND state_subsidy_amt = 0) OR
(‘>$0’ IN (‘$0’,‘>$0’,‘NULL’) AND state_subsidy_amt > 0) OR
(‘NULL’ IN (‘$0’,‘>$0’,‘NULL’) AND state_subsidy_amt IS NULL)) AND

((‘$0’ IN (‘$0’,‘$0-$50’,‘$50–$100’,‘Above $100’) AND net_premium_amt = 0) OR
(‘$0-$50’ IN (‘$0’,‘$0-$50’,‘$50–$100’,‘Above $100’) AND net_premium_amt > 0 AND net_premium_amt < 50) OR
(‘$50–$100’ IN (‘$0’,‘$0-$50’,‘$50–$100’,‘Above $100’) AND net_premium_amt >= 50 AND net_premium_amt < 100) OR
(‘Above $100’ IN (‘$0’,‘$0-$50’,‘$50–$100’,‘Above $100’) AND net_premium_amt > 100)) AND
((‘Broker designated’ IN (‘Broker designated’,‘Assister designated’,‘No designation’) AND assister_broker_id IS NOT NULL AND broker_role = ‘AGENT’) OR
(‘Assister designated’ IN (‘Broker designated’,‘Assister designated’,‘No designation’) AND assister_broker_id IS NOT NULL AND broker_role = ‘ASSISTER’) OR
(‘No designation’ IN (‘Broker designated’,‘Assister designated’,‘No designation’) AND assister_broker_id IS NULL)) AND

((‘Enrolled’ IN (‘Enrolled’,‘Pending’,‘Terminated’,‘Cancelled’,‘Aborted’) AND enrollment_status_lkp = 125) OR
(‘Pending’ IN (‘Enrolled’,‘Pending’,‘Terminated’,‘Cancelled’,‘Aborted’) AND enrollment_status_lkp = 128) OR
(‘Terminated’ IN (‘Enrolled’,‘Pending’,‘Terminated’,‘Cancelled’,‘Aborted’) AND enrollment_status_lkp = 124) OR
(‘Cancelled’ IN (‘Enrolled’,‘Pending’,‘Terminated’,‘Cancelled’,‘Aborted’) AND enrollment_status_lkp = 123) OR
(‘Aborted’ IN (‘Enrolled’,‘Pending’,‘Terminated’,‘Cancelled’,‘Aborted’) AND enrollment_status_lkp = 1775)) AND
(creation_timestamp + interval ‘5h’) >= date_trunc(‘month’, current_date - interval ‘3’ month) AND
creation_timestamp <= current_date AND
date_trunc(‘day’, creation_timestamp + interval ‘5h’) BETWEEN ‘2023-12-13T01:22:41.878Z’ AND ‘2024-03-12T00:22:41.878Z’

23

Doesn’t look like it uses variables hence it works?

24

it uses i copied that from inspector … below is the direct query from the panel

SELECT
count(*)
FROM enrollment
WHERE
EXTRACT(YEAR FROM benefit_effective_date) IN (${benefit_effective_date}) AND

((‘Health’ IN (${insurance_type_lkp}) AND insurance_type_lkp IN (131)) OR
(‘Dental’ IN (${insurance_type_lkp}) AND insurance_type_lkp IN (132))) AND
((‘$0’ IN (${aptc_amt}) AND aptc_amt = 0) OR
(‘>$0’ IN (${aptc_amt}) AND aptc_amt > 0) OR
(‘NULL’ IN (${aptc_amt}) AND aptc_amt IS NULL)) AND

((‘$0’ IN (${state_subsidy_amt}) AND state_subsidy_amt = 0) OR
(‘>$0’ IN (${state_subsidy_amt}) AND state_subsidy_amt > 0) OR
(‘NULL’ IN (${state_subsidy_amt}) AND state_subsidy_amt IS NULL)) AND

((‘$0’ IN (${net_premium_amt}) AND net_premium_amt = 0) OR
(‘$0-$50’ IN (${net_premium_amt}) AND net_premium_amt > 0 AND net_premium_amt < 50) OR
(‘$50–$100’ IN (${net_premium_amt}) AND net_premium_amt >= 50 AND net_premium_amt < 100) OR
(‘Above $100’ IN (${net_premium_amt}) AND net_premium_amt > 100)) AND
((‘Broker designated’ IN (${assister_broker_id}) AND assister_broker_id IS NOT NULL AND broker_role = ‘AGENT’) OR
(‘Assister designated’ IN (${assister_broker_id}) AND assister_broker_id IS NOT NULL AND broker_role = ‘ASSISTER’) OR
(‘No designation’ IN (${assister_broker_id}) AND assister_broker_id IS NULL)) AND

((‘Enrolled’ IN (${enrollment_status_lkp}) AND enrollment_status_lkp = 125) OR
(‘Pending’ IN (${enrollment_status_lkp}) AND enrollment_status_lkp = 128) OR
(‘Terminated’ IN (${enrollment_status_lkp}) AND enrollment_status_lkp = 124) OR
(‘Cancelled’ IN (${enrollment_status_lkp}) AND enrollment_status_lkp = 123) OR
(‘Aborted’ IN (${enrollment_status_lkp}) AND enrollment_status_lkp = 1775)) AND
(creation_timestamp + interval ‘5h’) >= date_trunc(‘month’, current_date - interval ‘3’ month) AND
creation_timestamp <= current_date AND
date_trunc(‘day’, creation_timestamp + interval ‘5h’) BETWEEN $__timeFrom() AND $__timeTo()

25

Compare to your non working query.
Also do you have those variables in the non working dashboard?

Please share screen of variable drop down

26

This one has extra variables that is more variable on this than the none working one which has just 3 variables : EXTRACT(YEAR FROM benefit_effective_date) IN (${benefit_effective_date})
AND ((‘Health’ IN (${insurance_type_lkp}) AND insurance_type_lkp IN (131)) OR
(‘Dental’ IN (${insurance_type_lkp}) AND insurance_type_lkp IN (132)))

27

here is the drop down for the working variable please note that this working variable i did three months back even though it works but when i create a new dashboard using same query it doesnt work and gives same $ error

image
image782×146 13.7 KB

image
image1258×494 51.7 KB

28

This was the ask from you. Provide screen grab of variables drop down from non working dashboard

29

sorry my bad i read wrongly … here is the screenshot on the none working dashboard drop down !!

image

image
image944×289 17 KB

30

this is not what you have in your non working code. what you have is

(('Enrolled' IN ${enrollment_status_lkp} AND enrollment_status_lkp = 125)

31

thats because i tried both an still doesnt work anyways

32

I think grafana has a bug or something… now the dash board turns to be working with same configurations i tried using before .

select
short_name,
i.hios_issuer_id,
i.state_of_domicile “State”,
count (ee.id)
from
issuers i
left join enrollment en on
en.hios_issuer_id = i.hios_issuer_id
join enrollee ee on
ee.enrollment_id = en.id
and ee.person_type_lkp in (168, 169)
and en.enrollment_status_lkp <> 1775
and EXTRACT(YEAR FROM benefit_effective_date) IN (${benefit_effective_date})
AND ((‘Health’ IN (${insurance_type_lkp}) AND insurance_type_lkp IN (131)) OR
(‘Dental’ IN (${insurance_type_lkp}) AND insurance_type_lkp IN (132)))
group by
1,
2,
3
order by

33

Thank you so much @yosiasz for your time and support i really do appreciate and i look forward learning more grafana stuffs from you . do you have a YouTube channel if yes please drop a link and i will follow . Thanks once again

1 Like