I sometimes would like to pass a ‘IN’ clause using a parameter, mostly when I’m using a data driven subscription (SSRS) on a report that has a multi-value parameter and can also be run interactive (via Internet Explorer)
I spend hours looking for a ‘simple’ solution, only to found ‘NO’ or ‘Very difficult’. Passing multiple values to a SQL parameter is not valid (according to Microsoft).
I did find a workaround and modified this for my need (using it in a data driven subscription).
I have created a very simple table with the name tmpMultiSel with two columns (ID and Name)
Here are the rows in that table.
Here is what I did to make it ‘multi value’:
1. Create a function
I did find a function that accepts two parameters and splits the delimited string (first parameter) into a temporary table.
I found it http://www.codeproject.com/Articles/7938/SQL-User-Defined-Function-to-Parse-a-Delimited-Str but modified the name to my ‘habits’ _f_ParseText2Table
If we test this function, it works like so:
Meaning we can actually use this function to split a delimited string to a tempory table that we can join or select in a ‘IN’ clause (WHERE … IN (SELECT txt_value FROM dbo._f_ParseText2Table(‘1,2,3′,’,’))
2. Create a report
Next I created a report that has a dataset that links to the tmpMultiSel table (but could also link to a transactional table, that would make more sense)
SELECT * FROM tmpMultiSel
The second dataset is used to give the user the possibility to multi select some values from a table:
The trick here is that this parameter (IDSel) may be left blank (as we cannot pass multiple values to a parameter via query or Data Driven Subscription) AND that we add an empty line in the second dataset (UNION ALL SELECT ”,”) to be able to select the ” value from the Data Driven Subscription. (make sure you type 2x single quotes comma 2x single quotes)
3. Create a parameter
Here we have a parameter (ID) that we pass to the function:
In ‘real life’ we would hide this parameter, but for testing purpose it is nicer to watch how it works.
We add a second parameter (IDSel) that has multiple values and the available values selected from the second dataset.
We set the default value for the first parameter (ID) to the split values of the second parameter (IDSel)
As we test this report, it works like a charm, passing the two selected ID’s to the ID parameter, which in return passes its values to the first dataset. (what would be the transactional table)
4. Create the data driven subscription
Now that we know this report works ‘interactive’ we can proceed to the data driven subscription.
I’ve used a simple dataset that only has three columns (ID, IDSel and Mail).
I did just select some static values, but you can easily modify it to work with a ‘real’ example where you select data from a table.
Now we map this three columns to the appropriate fields and create a schedule for this subscription (once).
5. Test it
We fire off the job and voila, you’ve got mail:
With a nice attachment:
There we have a mail with the data from the table, being generated based on a multi value parameter!
You might think ‘not so simple’ but it was a 5 minute job, so it should not be too difficult.
If you have any questions, remarks, improvements or want the test files, please add your comment!