Multi value parameter

• Bhagat

  SSC-Addicted

  Points: 402

  More actions

  May 31, 2019 at 6:44 am

  #3648816

  Problem

  Can't get multi value parameter/prompt to return one or more selections. This is due to how the data is stored in source.

  So I have a parameter than uses a dataset to provide the lookup values and returns values such as '0 Bedrooms', '1 Bedroom' etc... and this is fine.

  In my source query (stored procedure), the column stores these values as one long string that can consist of one or more of these outputs and the problem is how can I use the lookup values to find if that text string exists in the column (Matching Number of Bedrooms Desc).

  Note the output has a space before each value, e.g ' 0 Bedrooms' which is different to the lookup query.

  I have done some research I know I need a function that will split the values and that my query needs to reference the function and assign the variable.

  My issue is I cannot get it working as I believe in the WHERE clause I need to use an IN clause, which I where I need help. At the moment, I have:

  WHERE PPS.PropSizes IN (SELECT Val FROM dbo.fn_String_To_Table(@PropertySize,',','1'))

  Also, in SSRS, I have added an expression for the relevant parameter as: =JOIN(Parameters!PropertySize.Value,",")

  I have attached some screenshots that might help.

  Any advice much appreciated, thanks.

  Attachments:

  You must be logged in to view attached files.

• Site Owners

  SSC Guru

  Points: 80320

  More actions

  June 1, 2019 at 7:10 am

  #3649259

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.