Query Based Dropdown List causing issues

I have a query that fetches data for a selection of units based on selections in a query based dropdown menu parameter.

select *
from dbo.Data d
where d.Unitid in (’{{ Unit }}’)

The query fetching the list of units returns the right values, but I get this error:

Error running query: Conversion failed when converting the varchar value ‘3561,3714,4483’ to data type int.DB-Lib error message 20018, severity 16: General SQL Server error: Check messages from the SQL Server

With 3561,3714,4483 being the unit Id’s returned by the parameter query. If I manually pase in the same values, the query works as expected.:

select *
from dbo.Data d
where d.UnitId in( 3561,3714,4483 )

Can anyone explain what might be causing this error and how to get around it? I have attempted to cast the unit values to int and changed the number formatting in the visualization editing tool, but this didn’t solve the problem.

Cheers, Mike

It’s probably because of the quotation marks around the Unit parameter. What happens if you remove them?

Hi jesse,

You were right, removing the quotation marks did the job. Strangely, If I want to slice the time axis, I have to do it like this, with quotation marks:

Select *
...
and d.[TimeStamp] >= '{{ myDate.start }}'
and d.[TimeStamp] <= '{{ myDate.end }}'

Cheers, Mike

Glad to hear it works for you!

And yes, what you’ve shown there is exactly how time range parameters are designed to be employed.