I’ve seen this issue a lot lately. There is a need to feed a comma delimited string of values into a multi-value parameter in an SSRS report. There is a simple way to do this.
SET UP YOUR MAIN REPORT QUERY
Your main report query should be set up to expect a string of values in the parameter. For example:
SELECT Product_ID ,Item_No ,Item_Description FROM DIM_Product WHERE Item_No IN (@ITEM)
SET UP THE PARAMETER
The parameter should be set up as text, but NOT allow multiple values.
You can set this up as you wish to meet your purpose, but for demonstration I will set up two groups of comma delimited strings as available values.
I have set up a second data set to feed these default values to the parameter.
SELECT '11000B,2200,17000' AS ITEMNMBR
SET UP THE PARAMETER ON THE MAIN REPORT DATASET
Now comes the magic. On the Dataset Properties for your main report dataset, on the Parameters tab, edit the expression for the Parameter Value.
Write this in the parameter expression: =split(Parameters!Item.Value,",")
This will take the comma delimited string as input, split it into individual values and it will get used in the IN clause of the main query that we set up at the beginning of this post.
WHERE Item_No IN (@ITEM)
Viola, the report filters on a string of values.