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.
Thanks Data Queen 🙂 Your site is the first that explained this well without using crazy functions or SQL. There is a Stack Overflow question here http://stackoverflow.com/questions/17244656/using-ssrs-report-to-pass-in-a-param-value-the-is-a-csv-list-to-sql-dataset that’s pretty helpful too.
Thanks DataQueen just got what was needed simple to implement without using any functions and store procedure
Thanks! Simply solution of my problem!
Problem is…you don’t have a Select All option. How to have a Select All option when using multiple values? With a dropdown list where the user can only select a single value, we can handle the Select All by passing null and checking in the Where clause for a value OR @parameter Is Null
That’s true. Read this post for details on a clean way to address it. http://thedataqueenblog.azurewebsites.net/2011/06/how-to-default-to-all-in-an-ssrs-multi-select-parameter/
Will this work if the datasets are stored procedures?
Yes, it will.
removing the multi-value option from the report parameter makes it harder for a user who may want to run an adhoc report. Do you have any suggestions other than requiring a user manually enter their parameters.
Yes, just use the built in functionality for multi-valued parameters. https://msdn.microsoft.com/en-us/library/dn385719(v=sql.110).aspx
Thank you very much for this post. Saved me a lot of time.
Thanks a lot to Author. Your solution was really helpful for me.
Thank you for your sol’n. I am having issues however, getting the ‘Select All’ setup. I followed the link that you provided but I am not following. http://thedataqueenblog.azurewebsites.net/2011/06/how-to-default-to-all-in-an-ssrs-multi-select-parameter/
I have followed this sol’n to a ‘T’ but I am getting hung up on how to default (in your scenario) both GroupA and GroupB to be selected when the report is initially executed.
Any help is greatly appreciated.
So what I have done so far is created another label called All with all of the values. May not be the right way to go but it does the trick.
ie.
Label Values
Group A 11000B,2200,17000
Group B 2000B,2600B
All 11000B,2200,17000,2000B,2600B
Yes, that is the correct way to default to all the values.
I’m looking for a way to “Explode” a group of values separated by a semicolon and use the values individually in my report. Any suggestions on how to do that.
AWESOME – I have looked everywhere for this answer and you made it so simple. Thank you.
Great article, several year later, still a great value for the users.
I follow your article and work just fine in SQL Server. But when I connect to Oracle stored procedure I got no data found. Any Suggestions?
Thank you!
You are a great time savior.Thank you for the step by step explanation.
Thank you! Spent the morning banging my head against the wall to find a solution for this problem. You made it simple!