I have a report with several filter parameters. Everything works fine presently. For one parameter, I use the LIKE as follows in my WHERE statement: WHERE drug LIKE '%' + @drugused + '%' @drugused is the selection value of a dropdown list. But now I want to change my parameter to allow multi-selection. But I am not sure how to write my WHERE statement for this. Normally I would use IN but I'm not sure how to do this with the LIKE. So I want to do something like: WHERE drug IN ( LIKE '%' + @drugused + '%' ) But I am not sure how to make this work. Thanks, Bob

hello, if you have a table that holds all available values of the multivalue parameter you can do this where exists (select drugused from (select '%' + drugused + '%' drugused from lookuptable where drugused in(@drugused)) where drug like drugused)

Hey, I think you are on the right track. But I was having a hard time making your statement work. Let me give you some more info. I do have a lookup table. This is what populates my dropdown. This table is named Antibiotics. The column name in this table is ant_description. Now the column named Drug is in table sdr_drug. And @drugused in my multi-select parameter. I was hoping you could help me construct this WHERE statement with this additional info. Thanks, Bob

where exists (select drugused from (select '%' + ant_description + '%' drugused from Antibiotics where ant_description in(@drugused)) where drug like drugused)

Thanks. It works now if I choose only one drug. But I get this error if I select more than one value from the SSRS dropdown list: Query execution failed for dataset myDS. Incorrect syntax near ',' Here is the code I am using. Please be aware that I changed a few column names: WHERE EXISTS (SELECT surgical_druguse.sdr_use FROM (SELECT '%' + ant_description + '%' AS drugused FROM antibiotics WHERE (ant_description IN (@Antibiotics))) AS drugused WHERE (surgical_druguse.sdr_use LIKE drugused)) I guess the ',' (comma) is to seperate the elements selected in the multi-select dropdown list. Bob

seems like you are selecting the reference from your main query instead of drugused alias in that inner select select surgical_druguse.sdr_use from surgical_druguse WHERE EXISTS (SELECT drugused FROM (SELECT '%' + ant_description + '%' AS drugused FROM antibiotics WHERE (ant_description IN (@Antibiotics))) AS drugused WHERE (surgical_druguse.sdr_use LIKE drugused))

Hi Bob, Check this thread http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/01c29cd5-d1c0-41c5-aee5-23c8d21f5953 , my reply should help you out of this porblem. thanks, Jerry

Thanks for the reply Jerry. I'm not exactly sure where I do this: =Join(Parameters!Multivals.value,",") Bob

Hi Bob, Open the dataset parameters page, pass the =join(Parameters!Multivals.value,",") to the parameter defined in the dataset, the parameter is used by the tabled-function. thanks, Jerry

Jerry, Thanks again. For some reason I'm having a hard time figuring out this one. So I appreciate your help. I changed my parameter to: =join(Parameters!Antibiotics.value,",") I also created the function fn_String_To_Table like you said. But I am having a hard time tying all of this together. I presently have a SQL query string in my Reporting Services dataset. I am confused on how to use your function here properly. Thanks, Bob

I am still stuck. I have not used functions too much and I'm not sure of a few things. First off, what type of function is this? In SQL, I see there are table-valued functions, scalar-valued functions, and aggregate functions. Where should I put your function? Also, my present SQL statement is huge with lots of joins. I understand you want me to build a temp table with this function so that I can use it in my main sql statement. I am not good with functions so I'm not exactly sure where I run this in relation to my main sql statement. Thanks, Bob