ReportServer Forum

Ronen

Member

Registered: 2015-10-12

Select In User_Variable

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

Arno Mittelbach

datenwerke

Registered: 2012-02-14

Re: Select In User_Variable

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

Ronen

Member

Registered: 2015-10-12

Re: Select In User_Variable

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

Arno Mittelbach

datenwerke

Registered: 2012-02-14

Re: Select In User_Variable

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

Ronen

Member

Registered: 2015-10-12

Re: Select In User_Variable

Thank you very much Arno!

This helped a lot.