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.

0002-001SelTable

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:

0002-000 Function

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)

0002-002 Dataset

SELECT
*
FROM tmpMultiSel

The second dataset is used to give the user the possibility  to multi select some values from a table:

0002-003 Dataset2

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:

0002-005 Parameter2

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.

0002-003 Parameter1

0002-004 Parameter1-2

We set the default value for the first parameter (ID) to the split values of the second parameter (IDSel)

0002-006 Parameter2-1

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)

0002-007 Interactive view

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).

0002-008 Dataset DDS

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).

0002-009 Dataset link-1 0002-010 Dataset link-2

 5.       Test it

We fire off the job and voila, you’ve got mail:

0002-011 mail1

With a nice attachment:

0002-012 Mail2

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!

31 Comments

  1. 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.

  2. Author

    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

  3. HI I HAVE EVEN TRIED UR VERSION IT DOESNT WORKS WITH AN ERROR ‘INVALID VALUE SPECIFIED FOR THE IDSEL PARAMETER’

    CHEERS

    1. Author

      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?

  4. 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

    1. Author

      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?

  5. 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

    1. Author

      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?

  6. 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

    1. Author

      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.

  7. Thanks I have managed to workaround using your logic…mine was bit complicated as it involved cascading
    Thanks for your help much appreciated.

  8. 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.

    1. Author

      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.

  9. how can you do union all ?

    UNION ALL SELECT ‘’,’’

    1. Author

      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

  10. 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?

    1. Author

      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

  11. 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?

    1. Author

      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.

  12. 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!

  13. 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.

    1. Author

      Are you running the report via subscription or via browser?

  14. i am running via report preview then i have a plan to run that report via subscription

  15. Author

    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.

  16. 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?

    1. Author

      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!

  17. 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.

    1. Author

      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?

  18. 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!

    1. Author

      Is the function working as it supposed to?
      (_f_ParseText2Table)

Leave a Reply

Your email address will not be published. Required fields are marked *