Problem
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).
Solution
I did find a workaround and modified this for my need (using it in a data driven subscription).
Sample data
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!
Thank you for this post but I have one quesion, what is the purpose of the IDSel parameter? I am also confused as to how the subscription returns any data because by default you are setting both the IDSel and the ID parameter to blank. I say that because in your parameter definition you are saying the default of the ID parameter is the SPLIT values of IDSel. If IDSel is blank then so will the ID parameter.
Brian,
You’re welcome. Did you try it out yourself?
We normally cannot pass multi value parameter to a report’s datasource using data driven subscription (DDS), so we ‘fool’ the datasource and make it accept a blank value (IDSel).
This one is used for ‘interactive’ use only and will default to the actual parameter (ID) that will also be used from the dds.
The DDS will pass a blank value to IDSel, but that is no problem, since we don’t use that in the DDS (the datasource ignores that blank value and uses the function only).
Just try it, easy and clean (at least much better than what I’ve found online).
Peter
HI I HAVE EVEN TRIED UR VERSION IT DOESNT WORKS WITH AN ERROR ‘INVALID VALUE SPECIFIED FOR THE IDSEL PARAMETER’
CHEERS
That’s strange.
There must be something different in your report, because mine is working daily for some time now.
But what are you trying to do with your report?
I have 3 parameters in my report i.e. concern , division , account …all are multi value parameter and use split function in the QUERY ….report is working all well interactively but when it comes to data driven subscription it gives error of invalid value ……my table contains comma separated list for each of the above parameter…… which I want subscription to pick up and burst report for ….
but it fails ,…….
any help will be appreciated
thanks
That sounds interesting.
I will give it a try here.
First let’s make sure I understand your ‘needs’ and simplify them a little by focussing on the multi select part only.
Please let me know if I’m correct.
Do these parameters depend on each other?
You have three parameters on this report:
1. Concern
2. Division
3. Account
These depend on each other, so the available values will depend on the previous parameter.
If you select concern 1 and 2 you only want to see divisions that belong to that concern?
How do you want your subscription?
I think user 1 needs data from all accounts from concern 1 & concern 2, division 1a and 1b + division 2x and 2y?
Or do you also store some range of accounts here for user 1 (not all accounts from divisions 1a, 1b, 2x and 2y)
Am I correct?
Do these parameters depend on each other?
ANS to your first question is yes concern is the top bit when selected will give u the available values for that concern and when any of the division is selected under that concern it gives u the available values of the Account under that division.
for How do you want your subscription?
I have table in which division and accounts column can have single or multiple values as a comma delimited string
Ok, so you store (for example) a subscription for a user:
Concern 1,2
Division 1a,1b,2a,2b
Account 1000,1001,2001,2002 etc.
Why do you also store certain accounts in this selection?
This requires a lot of data to be stored and every new account must be added to a subscription also…
Accounts are customers right?
yes they are customer….these accounts are updated in the subscription table by joining it to the master customer table that gets any new accounts
Does this user want ALL customers that fall under division 1a, 1b, 2x and 2y or does he only want certain customers in his weekly report?
In other words, would he normally select ‘all’ in the multi value subscription, or select a specific nr. of customers.
Thanks I have managed to workaround using your logic…mine was bit complicated as it involved cascading
Thanks for your help much appreciated.
Nice!
Hi
Apart from one issue outlined below, I got this to work with my report and DDS.
In interactive after running the report; any new selections made in parameter IDSel, Select the name(s), is not reflected in parameter ID unless you come out of the report and go back into it.
I can make the interactive work by specifying available values for @ID, using the join function again, however, this then causes an error in the DDS.
Did you set it to default to =join(Parameters!IDSel.Value,”,”)
Like in my printscreen after “We set the default value for the first parameter (ID) to the split values of the second parameter (IDSel)”
You can see in the next printscreen that it gets the values interactively.
how can you do union all ?
UNION ALL SELECT ‘’,’’
Hi,
Sorry for my late reply. Holiday season 🙂
I’m not sure what you don’t understand. Just how to make a UNION ALL in SQL?
Or in my example?
Please give me more info on what you don’t get and I will try to answer!
Thanks,
Peter
your step by step procedure is stunning but i am using my Dataset in MDX Coding that i am struggling for. My where condition is like (StrToSet(@TradingName, CONSTRAINED)) and i am not sure about what kind of change i needed. I am pretty sure that IN clause for SQL. Can you help me for MDX?
Hi Shamim,
I’m sorry, my MDX experience is very little.
We left SSIS cubes long ago and got back to SQL with a simple ETL db.
For now more than sufficient for our needs.
Peter
When i tried your code like
SELECT *
FROM tmpMultiSel
UNION ALL
SELECT “,”
it gets the following error:
Invalid column name ‘,’.
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
could you please tell what was the trick?
Shamim, I’m sorry, WordPress makes double quotes here. Of course, this needs to be 2x single quotes comma 2x single quotes.
”,”
This makes for the empty record in the parameter values that you can select in the in the data driven subscription.
That ”,” problem solved but after previewing the report it is saying ‘the DefaultValue expression for the report parameter ‘ID’ contains an error the expression references the parameter ‘IDSel’ which does not exist in the parameters collection. letters in the names of parameters must use the correct case’
i don’t know what’s the wrong with this? i set this expression =join(Parameters!IDSel.Value,”,”) in ID parameters default value and no available values, is that right? IDSel parameter has available values from a query of dataset Names but it has no default value. please correct me, i am broken!
i Noticed if i don’t set =join(Parameters!IDSel.Value,”,”) as a default value, then it runs at least then if i provide ID Value like 1,2,3 and whatever ticked in the names it doesn’t matter, the result are being displayed according to ID value.
Are you running the report via subscription or via browser?
i am running via report preview then i have a plan to run that report via subscription
Shamim,
As you can see in my example, it works both ways (via browser AND via subscription).
The IDSel parameter value provided via the browser is used in the browser ONLY and is passed to the ID parameter as it’s default value. (it is not used in the dataset itself)
This way a user can select (multiple) values that are joined in the default value expression of the ID parameter (=join(Parameters!IDSel.Value,”,”)) .
I’ve also added the ”,” in the available values query (UNION ALL) so that the subscription query can pass ” as it’s value. (since we cannot leave it blank)
The user will never pass that value, because it means nothing to him/her.
Next in the subscription we provide the ” value for the IDSel parameter AND the comma delimited string as the ID value.
So both ways (browser and subscription) work.
Recap:
– User (browser) sets IDSel parameter, that is used as a default for the ID parameter
– Subscription sets the IDSel to ” (that’s fine because we don’t use this in the report dataset) and the ID parameter to a supplied string (‘1,2,3,4’).
Just to be sure:
– You have the BI version of SQL? Otherwise datadriven subscription is not available.
– You have followed the steps in the blogpost precise? Every step has a reason!
I’ve just re created the report to make sure I’m not missing a step and it worked again.
Hi,,,
Need help to setup the DDS.
I have parameter in report and did not set any default values so when I am trying DDS not getting any values in Dropdown.
So I tried to write a query to get the values for dropdown parameter, It is working when I run the query in SQL and I can able to print and see the result as expected.
But when I try same query in subscription git is failing .
DECLARE @combinedString VARCHAR(MAX)
SELECT @combinedString=COALESCE(@combinedString + ‘,’,”) + Ltrim(rtrim(si.XX)) from XXX si
set @combinedString=cast (””+@combinedString+”” as varchar(max))
print @SITEIDcombinedString
select @combinedString AS SITEID
Is there any chance to get subscription without changing SSRS report parameters with default values?
Hi,
This should not be a problem.
Just make sure you pass a valid parameter from your DDS query.
So for example:
You have one parameter ‘Location’ with a value query:
SELECT
l.LocationID
,l.LocationName
FROM Location l
The value for the report parameter is set to LocationID and the label field to LocationName.
Now you go to your dds and make a query something like:
SELECT
l.LocationID
,l.LocationName
FROM Location l
INNER JOIN
(
SELECT
s.LocationID
FROM Sales s
WHERE
s.SalesDate = convert(date,getdate())
GROUP BY
s.LocationID
) s
This should give you every location that has sales today.
Now you go to the next tab (step 4) of you DDS and set your fields.
In step 5, you see the parameter field (location), just select the output ‘locationID’ to the parameter and you should be fine.
As long as it feeds valid locationID’s to that parameter, the subscription will work.
Just make sure you select the exact same field as the ‘value’ of the parameter (so not the label)
I do this all the time in our production environment without any problem.
You can always check the report log for errors if you think that it failed.
That gives you a very good clue to what went wrong.
You can find them somewhere like here
C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\LogFiles
Search for the reportname and time of execution and look for the word ERROR.
Feel free to contact me if you have more questions!
Hi there,
I tried this trick with a data driven subscription with NULL delivery (to cache reports). I broke my head about it. The report is not cached.
@GeselecteerdePeriodeID (Multi Value Pmt)
@GeselecteerdePeriodeIDString (Single Value String, hidden, passed to the SQL like ‘201801, 201802, 201803’.
When I pass the default Pmt to @GeselecteerdePeriodeIDString ” (dubble single quote) and the string with periods, it’s not cached correctly. This is because the report gets the periods in an array from ssrs like
201801
201802
etc.
..while the cached version contains a ” and 201801,201802 in the @GeselecteerdePeriodeIDString.
Do you have any suggestion how we can fix this? I have a complex report with a multi-value parameter which I need to cache using a NULL Delivery.
Hi,
I actually have never tried caching a report like this. So I will have to reproduce it in a test report.
How do I know it is cached correctly?
Hello,
Thank you so much for providing a work around for this SSRS subscription limitation. It’s been nearly five years since this article was posted but this is exactly what I was looking for.
I am currently using SSRS 2016.
So I copied and followed exactly everything you did above but my subscription failed with the following error: InvalidReportParameterException: Default value or value provided for the report parameter ‘IDSel’ is not a valid value.
This is the query for my dataset:
SELECT ‘1,2,3,4’ AS ID
, ” AS IDsel
, ‘abc@xyz.com’ AS Email
Report parameters:
Select the name(s): Get value from dataset –> IDSel
ID: Get value from dataset –> ID
This is my query for the dataset ‘Names’ to populate available value for @IDSel in the RDL (I use a temp table instead of static table)
DROP TABLE IF EXISTS #TmpMultiSel
CREATE TABLE #TmpMultiSel
(ID int, Name varchar(100))
INSERT INTO #TmpMultiSel
VALUES (1, ‘John’),
(2, ‘Frida’),
(3, ‘Sanny’),
(4, ‘Edward’),
(5, ‘Petra’),
(6, ‘Mandy’),
(7, ‘Karin’),
(8, ‘Janny’)
SELECT *
FROM #TmpMultiSel
UNION ALL
SELECT ”, ”
And this is the default value for @ID: =JOIN(Parameters!IDSel.Value, “, “)
@IDSel is set to allow blank and multiple values
Not sure what I am missing or did wrong here but the report runs perfectly on its own.
Any advice is greatly appreciated. Thank you in advance!
Is the function working as it supposed to?
(_f_ParseText2Table)