Query Based Dropdown List causing issues

1

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

2

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

3

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

4

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.