Help using template variable in a graph query to create filters

Grafana Dashboards
1

am on Grafana v9.5.15

I’m not sure what I’m doing wrong with using my template variable in a query. I’m using a postgres database with a table called “enrollment” that contains some enrollments data. I would like to use a template variable to select the enrollments data for on a dropdown using filters for year and insurance type .
but am having this error when i run the query …
: db query error: pq: syntax error at or near “$”

Here is the query i used :
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
i.short_name;

2

Click on Query Inspector and see what your query looks like and post it back here

3

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 (‘2024’)
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
i.short_name;

1 Like
4

this interpolation is still showing some issues? might be root of your problem

Why is this yeah a string?
EXTRACT(YEAR FROM benefit_effective_date) IN ('2024')

5

image
image604×825 49.4 KB

6

sorry this was the wrong query i posted

7

you have issue with ${insurance_type_lkp}

8

yes when i just do ${insurance_type_lkp} still having the error and i even tried ($insurance_type_lkp) same issues

9

just did again ${benefit_effective_date} and now am seeing different error : db query error: pq: syntax error at or near “‘2024’”

10

every time you change things, please check Query inspector and refresh the query inspector. what data type are each of the fields you are filtering on

11

sure i just did , but still having same error as before db query error: pq: syntax error at or near “$”

data types should be date which in my case just want the year and string

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
i.short_name;

12

not all of your filtered fields are date.

So all of these IN filters are wrong

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}

try to fix those. I would go the stored procedure approach for this. I never use inline sql query.

13

The weird thing is i used similar approach with another dashboard but with 6 other filers last 3 months and it was working good but now doing same it throws this errors and when i export the old dashboard and make edits on the inline query using the new query it works without errors but when i create a new dashboard it wont work

14

I made corrections but still having same error

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)))

15

look at pg query language usage of the IN syntax

1 Like
16

ohh and also regarding the data type this should be date EXTRACT(YEAR FROM benefit_effective_date) IN ${benefit_effective_date}

and the rest should be string thats what i meant

((‘Health’ IN ${insurance_type_lkp} AND insurance_type_lkp IN (131)) OR
(‘Dental’ IN (${insurance_type_lkp}) AND insurance_type_lkp IN (132)))

1 Like
17

still no luck because i dont really see where i go wrong :confused: :disappointed_relieved:

18

try the stored procedure route and pass all of those parameters and look what you did here with the parentheses after in the IN filter.

Also look at this,

string variables should be doublequoted

image
image549×750 55.5 KB

20

never tried that before … by stored route you mean using the builder ?

as for that parenthesis i did take them out for this but still having same issues … like i said before i have another dashboard i created using same configurations and its working but now am creating a new dashboard its not working … thats why am really confused

21

Probably best not add some other learning so I would stick to grafana.

Please show what the other dashboard does.