# Help using template variable in a graph query to create filters

**URL:** <https://community.grafana.com/t/help-using-template-variable-in-a-graph-query-to-create-filters/116708>\
**Category:** Dashboards\
**Tags:** templating\
**Created:** [March 11, 2024, 11:01pm UTC](https://community.grafana.com/t/help-using-template-variable-in-a-graph-query-to-create-filters/116708 "2024-03-11T23:01:02Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Che](https://avatars.discourse-cdn.com/v4/letter/c/d78d45/32.png) [@Che](https://community.grafana.com/u/Che)\
**Post date:** [March 11, 2024, 11:01pm UTC](https://community.grafana.com/t/help-using-template-variable-in-a-graph-query-to-create-filters/116708/1 "2024-03-11T23:01:03Z")

</div>

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;

---

<div class="post-metadata">

**Author:** ![yosiasz](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/yosiasz/32/45847_2.png) [@yosiasz](https://community.grafana.com/u/yosiasz)\
**Post date:** [March 11, 2024, 11:09pm UTC](https://community.grafana.com/t/help-using-template-variable-in-a-graph-query-to-create-filters/116708/2 "2024-03-11T23:09:16Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Che](https://avatars.discourse-cdn.com/v4/letter/c/d78d45/32.png) [@Che](https://community.grafana.com/u/Che)\
**Post date:** [March 11, 2024, 11:24pm UTC](https://community.grafana.com/t/help-using-template-variable-in-a-graph-query-to-create-filters/116708/3 "2024-03-11T23:24:46Z")

</div>

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;

---

<div class="post-metadata">

**Author:** ![yosiasz](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/yosiasz/32/45847_2.png) [@yosiasz](https://community.grafana.com/u/yosiasz)\
**Post date:** [March 11, 2024, 11:25pm UTC](https://community.grafana.com/t/help-using-template-variable-in-a-graph-query-to-create-filters/116708/4 "2024-03-11T23:25:46Z")

</div>

> [@Che](#):
>
> (‘Dental’ IN ($insurance\_type\_lkp)

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

---

<div class="post-metadata">

**Author:** ![Che](https://avatars.discourse-cdn.com/v4/letter/c/d78d45/32.png) [@Che](https://community.grafana.com/u/Che)\
**Post date:** [March 11, 2024, 11:26pm UTC](https://community.grafana.com/t/help-using-template-variable-in-a-graph-query-to-create-filters/116708/5 "2024-03-11T23:26:26Z")

</div>

![image](https://us1.discourse-cdn.com/grafana/original/3X/1/5/15e80a996050b942792e642e35d7ab2b4217db9d.png)

---

<div class="post-metadata">

**Author:** ![Che](https://avatars.discourse-cdn.com/v4/letter/c/d78d45/32.png) [@Che](https://community.grafana.com/u/Che)\
**Post date:** [March 11, 2024, 11:27pm UTC](https://community.grafana.com/t/help-using-template-variable-in-a-graph-query-to-create-filters/116708/6 "2024-03-11T23:27:30Z")

</div>

sorry this was the wrong query i posted

---

<div class="post-metadata">

**Author:** ![yosiasz](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/yosiasz/32/45847_2.png) [@yosiasz](https://community.grafana.com/u/yosiasz)\
**Post date:** [March 11, 2024, 11:28pm UTC](https://community.grafana.com/t/help-using-template-variable-in-a-graph-query-to-create-filters/116708/7 "2024-03-11T23:28:02Z")

</div>

you have issue with `${insurance_type_lkp}`

---

<div class="post-metadata">

**Author:** ![Che](https://avatars.discourse-cdn.com/v4/letter/c/d78d45/32.png) [@Che](https://community.grafana.com/u/Che)\
**Post date:** [March 11, 2024, 11:29pm UTC](https://community.grafana.com/t/help-using-template-variable-in-a-graph-query-to-create-filters/116708/8 "2024-03-11T23:29:26Z")

</div>

yes when i just do ${insurance\_type\_lkp} still having the error and i even tried ($insurance\_type\_lkp) same issues

---

<div class="post-metadata">

**Author:** ![Che](https://avatars.discourse-cdn.com/v4/letter/c/d78d45/32.png) [@Che](https://community.grafana.com/u/Che)\
**Post date:** [March 11, 2024, 11:32pm UTC](https://community.grafana.com/t/help-using-template-variable-in-a-graph-query-to-create-filters/116708/9 "2024-03-11T23:32:00Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![yosiasz](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/yosiasz/32/45847_2.png) [@yosiasz](https://community.grafana.com/u/yosiasz)\
**Post date:** [March 11, 2024, 11:34pm UTC](https://community.grafana.com/t/help-using-template-variable-in-a-graph-query-to-create-filters/116708/10 "2024-03-11T23:34:19Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Che](https://avatars.discourse-cdn.com/v4/letter/c/d78d45/32.png) [@Che](https://community.grafana.com/u/Che)\
**Post date:** [March 11, 2024, 11:37pm UTC](https://community.grafana.com/t/help-using-template-variable-in-a-graph-query-to-create-filters/116708/11 "2024-03-11T23:37:39Z")

</div>

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;

---

<div class="post-metadata">

**Author:** ![yosiasz](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/yosiasz/32/45847_2.png) [@yosiasz](https://community.grafana.com/u/yosiasz)\
**Post date:** [March 11, 2024, 11:43pm UTC](https://community.grafana.com/t/help-using-template-variable-in-a-graph-query-to-create-filters/116708/12 "2024-03-11T23:43:31Z")

</div>

> [@Che](#):
>
> data types should be date which in my case just want the year and string

not all of your filtered fields are date.

So all of these IN filters are wrong

```auto
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.

---

<div class="post-metadata">

**Author:** ![Che](https://avatars.discourse-cdn.com/v4/letter/c/d78d45/32.png) [@Che](https://community.grafana.com/u/Che)\
**Post date:** [March 11, 2024, 11:48pm UTC](https://community.grafana.com/t/help-using-template-variable-in-a-graph-query-to-create-filters/116708/13 "2024-03-11T23:48:41Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Che](https://avatars.discourse-cdn.com/v4/letter/c/d78d45/32.png) [@Che](https://community.grafana.com/u/Che)\
**Post date:** [March 11, 2024, 11:51pm UTC](https://community.grafana.com/t/help-using-template-variable-in-a-graph-query-to-create-filters/116708/14 "2024-03-11T23:51:33Z")

</div>

> [@Che](#):
>
> 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)))

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

---

<div class="post-metadata">

**Author:** ![yosiasz](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/yosiasz/32/45847_2.png) [@yosiasz](https://community.grafana.com/u/yosiasz)\
**Post date:** [March 11, 2024, 11:56pm UTC](https://community.grafana.com/t/help-using-template-variable-in-a-graph-query-to-create-filters/116708/15 "2024-03-11T23:56:38Z")

</div>

look at pg query language usage of the IN syntax

> **[PostgreSQL IN - Matching Against a List of Values](https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-in/)**
>
> This tutorial shows you how to use the PostgreSQL IN operator to match against a list of values. It also shows you how to use the IN operator in a subquery.

---

<div class="post-metadata">

**Author:** ![Che](https://avatars.discourse-cdn.com/v4/letter/c/d78d45/32.png) [@Che](https://community.grafana.com/u/Che)\
**Post date:** [March 11, 2024, 11:57pm UTC](https://community.grafana.com/t/help-using-template-variable-in-a-graph-query-to-create-filters/116708/16 "2024-03-11T23:57:59Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Che](https://avatars.discourse-cdn.com/v4/letter/c/d78d45/32.png) [@Che](https://community.grafana.com/u/Che)\
**Post date:** [March 12, 2024, 12:14am UTC](https://community.grafana.com/t/help-using-template-variable-in-a-graph-query-to-create-filters/116708/17 "2024-03-12T00:14:23Z")

</div>

still no luck because i dont really see where i go wrong 😕 😥

---

<div class="post-metadata">

**Author:** ![yosiasz](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/yosiasz/32/45847_2.png) [@yosiasz](https://community.grafana.com/u/yosiasz)\
**Post date:** [March 12, 2024, 12:14am UTC](https://community.grafana.com/t/help-using-template-variable-in-a-graph-query-to-create-filters/116708/18 "2024-03-12T00:14:58Z")

</div>

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,

> **[Variable syntax | Grafana documentation](https://grafana.com/docs/grafana/latest/dashboards/variables/variable-syntax/)**
>
> Learn about different types of variable syntax

string variables should be `doublequote`d

 ![image](https://us1.discourse-cdn.com/grafana/original/3X/8/4/849a575112dc773b0dde9d6c1fe3944599bab119.png)

---

<div class="post-metadata">

**Author:** ![Che](https://avatars.discourse-cdn.com/v4/letter/c/d78d45/32.png) [@Che](https://community.grafana.com/u/Che)\
**Post date:** [March 12, 2024, 12:19am UTC](https://community.grafana.com/t/help-using-template-variable-in-a-graph-query-to-create-filters/116708/20 "2024-03-12T00:19:46Z")

</div>

> [@Che](#):
>
> 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

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

---

<div class="post-metadata">

**Author:** ![yosiasz](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/yosiasz/32/45847_2.png) [@yosiasz](https://community.grafana.com/u/yosiasz)\
**Post date:** [March 12, 2024, 12:21am UTC](https://community.grafana.com/t/help-using-template-variable-in-a-graph-query-to-create-filters/116708/21 "2024-03-12T00:21:30Z")

</div>

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

Please show what the other dashboard does.

[Next page](https://community.grafana.com/t/help-using-template-variable-in-a-graph-query-to-create-filters/116708.md?page=2)