Select In User_Variable

ReportServer ReportServer (EN)
1

Hello

I am trying to write a query where its Where clause have an IN clause. For example:
where client in (${UserVariable})

The user variable is defined as String Parameter with comma seperated digits (e.g. - 1,2,3).
The problem is the Where clause return result for only the first digit in the comma seperated list and ignore the rest.

When I tried to define the variable as a List Parameter it didnt return any value and it looks that the list is completly ignored.

Could anyone please advise?

Thank you

Ronen

2

Hi Ronen,

if you have a list parameter (i.e., a datasource parameter or a user variable list) then you need to use the $X{IN, COLUMN, PARAMETER} syntax. that is

SELECT * FROM TABLE WHERE $X{IN, COLUMN, PARAMETER}

Cheers
-Arno

3

Thanks Arno for the quick response.

I have tried this but still getting the same behaviour-

My where clause is : where $X{IN, client_uid, test}

If I am setting test to contain 4,5 the query returns output with entries having 4 in the client_uid column. If I am changing it to 5,4 it returns entries with 5 on the column.

What am I missing here?

Thanks

Ronen

4

Hi Ronen,

test must be a list-type parameter. That is you must use a user variable of the list type and separate the values with a ‘|’ (pipe symbol).

Cheers
-Arno

5

Thank you very much Arno!

This helped a lot.