Passing multiple values for a single parameter in Reporting Services

Question

I have several Multi-Select parameters in my report  I am trying to find a way to pass in multiple values for a single parameter in the web query string  If I pass in a single value  it works fine   The report runs fine selecting multiple choices for a single param  My trouble lies in the web query string

User · Answer

I needed solution for Oracle and I found this worked for me inside my query for my report for DB>=10g.

select * from where in ( select regexp_substr(,'[^,]+', 1, level) from dual connect by regexp_substr(, '[^,]+', 1, level) is not null );

source https://blogs.oracle.com/aramamoo/entry/how_to_split_comma_separated_string_and_pass_to_in_clause_of_select_statement

User · Answer

this worked for a distinct set of strings   e g   START    END    ERROR    SUCCESS    1 define a Report-Parameter   e g   log status   and check  Allow multiple values    2  define a dataset 3  open the dataset-properties window 3a  in the Query-Tab enter your query  e g   select   from your table where  CHARINDEX your column   log status 0   gt  0    3b  in the Parameters-Tab enter your Parameter  e g      Parametername   log status   Parametervalue   lt  lt Expr gt  gt   3c  for the Expr click on the  fx -Button and enter    join Parameters log status Value          finished    it s similar to Ed Harper s solution  but sorry to say this  didn t work for me

User · Answer

I needed solution for Oracle and I found this worked for me inside my query for my report for DB>=10g.

select * from where in ( select regexp_substr(,'[^,]+', 1, level) from dual connect by regexp_substr(, '[^,]+', 1, level) is not null );

source https://blogs.oracle.com/aramamoo/entry/how_to_split_comma_separated_string_and_pass_to_in_clause_of_select_statement

User · Answer

So multiply text values would end up in the query with single quotes around each I used  join Parameters Customer Value         So after   Value  that is comma  double-quote  single-quote  comma  single-quote  double-quote  close-bracket  simples

User · Answer

This is one of the poor supported features in SQL Reporting Services   What you need to do is pass all of your selected items as a single string to your stored procedure  Each element within the string will be separated by a comma   What I then do is split the string using a function that returns the provided string as a table  See below   ALTER FUNCTION  dbo   fn MVParam       RepParam nvarchar 4000    Delim char 1        RETURNS  Values TABLE  Param nvarchar 4000  AS   BEGIN   DECLARE  chrind INT   DECLARE  Piece nvarchar 100    SELECT  chrind   1    WHILE  chrind  gt  0     BEGIN       SELECT  chrind   CHARINDEX  Delim  RepParam        IF  chrind   gt  0         SELECT  Piece   LEFT  RepParam  chrind - 1        ELSE         SELECT  Piece    RepParam       INSERT   Values Param  VALUES CAST  Piece AS VARCHAR         SELECT  RepParam   RIGHT  RepParam LEN  RepParam  -  chrind        IF LEN  RepParam    0 BREAK     END   RETURN   END   You can then reference the results in the where clause of your main query like so   where someColumn IN SELECT Param FROM dbo fn MVParam  sParameterString         I hope this you find this solution to be of use  Please feel free to pose any questions you may have   Cheers John

User · Answer

I m new to the site  and couldn t figure how to comment on a previous answer  which is what I feel this should be  I also couldn t up vote Jeff s post  which I believe gave me my answer  Anyways     While I can see how some of the great posts  and subsequent tweaks  work  I only have read access to the database  so no UDF  SP or view-based solutions work for me  So Ed Harper s solution looked good  except for VenkateswarluAvula s comment that you can not pass a comma-separated string as a parameter into an WHERE IN clause and expect it to work as you need  But Jeff s solution to the ORACLE 10g fills that gap  I put those together with Russell Christopher s blog post at http   blogs msdn com b bimusings archive 2007 05 07 how-do-you-set-select-all-as-the-default-for-multi-value-parameters-in-reporting-services aspx and I have my solution   Create your multi-select parameter MYPARAMETER using whatever source of available values  probably a dataset   In my case  the multi-select was from a bunch of TEXT entries  but I m sure with some tweaking it would work with other types  If you want Select All to be the default position  set the same source as the default  This gives you your user interface  but the parameter created is not the parameter passed to my SQL    Skipping ahead to the SQL  and Jeff s solution to the WHERE IN   MYPARAMETER  problem  I have a problem all my own  in that 1 of the values   Charge   appears in one of the other values   Non Charge    meaning the CHARINDEX might find a false-positive  I needed to search the parameter for the delimited value both before and after  This means I need to make sure the comma-separated list has a leading and trailling comma as well  And this is my SQL snippet   where     and CHARINDEX       pitran LINEPROPERTYID         MYPARAMETER LIST   gt  0   The bit in the middle is to create another parameter  hidden in production  but not while developing  with    A name of MYPARAMETER LIST A type of Text  A single available value of          join Parameters MYPARAMETER Value            and a label that doesn t really matter  since it will not be displayed   A default value exactly the same Just to be sure  I set Always Refresh in both    parameters  Advanced properties   It is this parameter which gets passed to SQL  which just happens to be a searchable string but which SQL handles like any piece of text   I hope putting these fragments of answers together helps somebody find what they re looking for

User · Answer

Just a comment - I ran into a world of hurt trying to get an IN clause to work in a connection to Oracle 10g   I don t think the rewritten query can be correctly passed to a 10g db   I had to drop the multi-value completely   The query would return data only when a single value  from the multi-value parameter selector  was chosen   I tried the MS and Oracle drivers with the same results   I d love to hear if anyone has had success with this

User · Answer

Although John Sansom s solution works  there s another way to do this  without having to use a potentially inefficient scalar valued UDF  In the SSRS report  on the parameters tab of the query definition  set the parameter value to    join Parameters  lt your param name gt  Value        In your query  you can then reference the value like so   where yourColumn in    lt your param name gt

User · Answer

In the past I have resorted to using stored procedures and a function to select multiple years in a SQL Server query for reporting services  Using the Join expression in the query parameter value as suggested by Ed Harper  still would not work with an SQL IN clause in the where statement   My resolution was to use the following in the where clause along with the parameter Join expression      and charindex  cast Schl Invt Yr as char 4      Invt Yr    0

User · Answer

What you also can do is add this code in your stored procedure   set  s   char 39    replace  s       char 39          char 39     char 39     Assuming  s is a multi-valued string  like  A B C

User · Answer

Modification of great John solution  solve     2 quotes  error space after one of piece in parameter    ALTER FUNCTION  dbo   fn MVParam    RepParam nvarchar 4000    Delim char 1        RETURNS  Values TABLE  Param nvarchar 4000  AS BEGIN   2 quotes error set  RepParam   replace  RepParam char 39  char 39  CHAR 39   DECLARE  chrind INT DECLARE  Piece nvarchar 100  SELECT  chrind   1  WHILE  chrind   0 BEGIN   SELECT  chrind   CHARINDEX  Delim  RepParam    IF  chrind    0     SELECT  Piece   LEFT  RepParam  chrind - 1    ELSE     SELECT  Piece    RepParam   INSERT   Values Param  VALUES CAST  Piece AS VARCHAR 300        space after one of piece in parameter  LEN  RepParam    1  -1   SELECT  RepParam   RIGHT  RepParam LEN  RepParam    1  -1 -  chrind    IF LEN  RepParam    0 BREAK END RETURN END

User · Answer

This is about using the join function to save a multi-value parameter and then restoring the exact same selections from the database later   I just finished a report that had requirements that the parameters must be saved  and when the report is opened again  the report is passed an OrderID paramater   the values previously chosen by the user must be once again selected   The report used a half of dozen parameters  each one had its own data set and resulting drop down list   The parameters were dependent upon the previous parameters to narrow the scope of the final selection  and when the report was  viewed  a stored procedure was called to populate   The stored procedure received each of the parameters passed to it from the report   It checked a storage table in the database to see if any parameters were saved for that OrderID   If not  then it saved all the parameters   If so  it updated all of the parameters for that order  this is the case where the user changes their mind later      When the report runs  there is a dataset dsParameters which is SQL text that goes out and selects the single row for that orderID if there is one   Each of the parameters in the report gets its default value from this dataset  and its selection list from a dataset dedicated to that parameter   I ran into trouble with the multi-select parameter   I used a join  Value      command in the main dataset parameter list  passing to the stored procedure a comma delimited string   But how to restore it   You can t feed the comma delimited string back to the default values box of the parameter   I had to create another dataset to split the parameter  in a manner similar to what you are talking about   It looks like this   IF OBJECT ID  tempdb   Parse   U   IS NOT NULL DROP TABLE  Parse  DECLARE  Start int   End int   Desc varchar 255   SELECT  Desc   fldDesc FROM dbCustomData dbo tblDirectReferralFormParameters WHERE fldFrom    From and fldOrderID    OrderID  CREATE TABLE  Parse  fldDesc varchar 255    SELECT  Start   1   End   1  WHILE  End  gt  0     BEGIN         SET  End   CHARINDEX      Desc  Start          IF  End   0              BEGIN                 INSERT  Parse SELECT REPLACE SUBSTRING  Desc  Start LEN  Desc           AS fldDesc                  BREAK             END         ELSE                     BEGIN                 INSERT  Parse SELECT REPLACE SUBSTRING  Desc  Start  End- Start          AS fldDesc              END         SET  Start    End   1     END  SELECT   FROM  Parse     Every time the form opens  this dataset checks the database for a saved string for this multi-valued parameter   If there is not one  it returns null   If there is on  it parses out the commas and creates a row for each of the values   Then the default values box is set to this dataset  and fldDesc   It works   When I choose one or many  they save and replenish when the form is opened again   I hope this helps   I searched for a while and did not find any mention of saving the join string in a database and then parsing it out in a dataset

User · Answer

Create the dataset for the list in the report Right click the parameter and select available values Select newly created dataset as dataset Add the value passing to the stored procedure as value field  Add the description of the parameter to label field  If the parameter is customerID then label could be CustomerName ex   Finally  add following code to your stored procedure   declare  paramName AS NVARCHAR 500    IF RIGHT  paramName  1        BEGIN  SET  paramName   LEFT   paramName  LEN   paramName -1  END

User · Answer

The below solution worked for me    In the parameter tab of your dataset properties click on the expression icon   http   chittagongit com  images fx-icon fx-icon-16 jpg  fx symbol   beside the parameter you need to allow comma delimited entry for   In the expression window that appears  use the Split function  Common Functions -  Text   Example shown below        Split Parameters ParameterName Value

User · Answer

Although John Sansom s solution works  there s another way to do this  without having to use a potentially inefficient scalar valued UDF  In the SSRS report  on the parameters tab of the query definition  set the parameter value to    join Parameters  lt your param name gt  Value        In your query  you can then reference the value like so   where yourColumn in    lt your param name gt

User · Answer

This is one of the poor supported features in SQL Reporting Services   What you need to do is pass all of your selected items as a single string to your stored procedure  Each element within the string will be separated by a comma   What I then do is split the string using a function that returns the provided string as a table  See below   ALTER FUNCTION  dbo   fn MVParam       RepParam nvarchar 4000    Delim char 1        RETURNS  Values TABLE  Param nvarchar 4000  AS   BEGIN   DECLARE  chrind INT   DECLARE  Piece nvarchar 100    SELECT  chrind   1    WHILE  chrind  gt  0     BEGIN       SELECT  chrind   CHARINDEX  Delim  RepParam        IF  chrind   gt  0         SELECT  Piece   LEFT  RepParam  chrind - 1        ELSE         SELECT  Piece    RepParam       INSERT   Values Param  VALUES CAST  Piece AS VARCHAR         SELECT  RepParam   RIGHT  RepParam LEN  RepParam  -  chrind        IF LEN  RepParam    0 BREAK     END   RETURN   END   You can then reference the results in the where clause of your main query like so   where someColumn IN SELECT Param FROM dbo fn MVParam  sParameterString         I hope this you find this solution to be of use  Please feel free to pose any questions you may have   Cheers John

User · Answer

I ran into a problem with the otherwise wonderful fn MVParam  SSRS 2005 sent data with an apostrophe as 2 quotes   I added one line to fix this   select  RepParam   replace  RepParam                My version of the fn also uses varchar instead of nvarchar   CREATE FUNCTION  dbo   fn MVParam            RepParam varchar MAX        Delim char 1            RETURNS  Values TABLE  Param varchar MAX   AS      Usage   Use this in your report SP       where ID in  SELECT Param FROM fn MVParam  PlanIDList           BEGIN     select  RepParam   replace  RepParam                 DECLARE  chrind INT    DECLARE  Piece varchar MAX     SELECT  chrind   1    WHILE  chrind  gt  0       BEGIN          SELECT  chrind   CHARINDEX  Delim  RepParam           IF  chrind  gt  0             SELECT  Piece   LEFT  RepParam  chrind - 1           ELSE             SELECT  Piece    RepParam          INSERT  VALUES Param  VALUES  Piece           SELECT  RepParam   RIGHT  RepParam DATALENGTH  RepParam  -  chrind           IF DATALENGTH  RepParam    0 BREAK       END    RETURN END

User · Answer

What you also can do is add this code in your stored procedure   set  s   char 39    replace  s       char 39          char 39     char 39     Assuming  s is a multi-valued string  like  A B C

User · Answer

This is what I use when passing a multi-select param to another multi-select param    SPLIT JOIN Parameters  lt your param name gt  Value

User · Answer

ORACLE   The  IN  phrase  Ed s Solution  won t work against an Oracle connection  at least version 10   However  found this simple work-around which does  Using the dataset s parameter s tab turn the multi-value parameter into a CSV        name  join Parameters name Value        Then in your SQL statement s WHERE clause use the instring function to check for a match       INSTR  name  TABLE FILENAME   gt  0

User · Answer

This is one of the poor supported features in SQL Reporting Services   What you need to do is pass all of your selected items as a single string to your stored procedure  Each element within the string will be separated by a comma   What I then do is split the string using a function that returns the provided string as a table  See below   ALTER FUNCTION  dbo   fn MVParam       RepParam nvarchar 4000    Delim char 1        RETURNS  Values TABLE  Param nvarchar 4000  AS   BEGIN   DECLARE  chrind INT   DECLARE  Piece nvarchar 100    SELECT  chrind   1    WHILE  chrind  gt  0     BEGIN       SELECT  chrind   CHARINDEX  Delim  RepParam        IF  chrind   gt  0         SELECT  Piece   LEFT  RepParam  chrind - 1        ELSE         SELECT  Piece    RepParam       INSERT   Values Param  VALUES CAST  Piece AS VARCHAR         SELECT  RepParam   RIGHT  RepParam LEN  RepParam  -  chrind        IF LEN  RepParam    0 BREAK     END   RETURN   END   You can then reference the results in the where clause of your main query like so   where someColumn IN SELECT Param FROM dbo fn MVParam  sParameterString         I hope this you find this solution to be of use  Please feel free to pose any questions you may have   Cheers John

User · Answer

It would probably be easier to add the multi values to a table first and then you can join or whatever you d like  even with wildcards  or save the data to another table for later use  or even add the values to another table    Set the Parameter value via expression in the dataset     SELECT DISTINCT   FROM  VALUES     amp  JOIN Parameters SearchValue Value            amp        AS tbl Value     The query itself   DECLARE  Table AS TABLE  Value nvarchar max    INSERT INTO  Table EXEC sp executeSQL  SearchValue    Wildcard example   SELECT   FROM YOUR TABLE yt   INNER JOIN  Table rt ON yt  Join Value  LIKE       rt  Value          I d love to figure out a way to do it without dynamic SQL but I don t think it ll work due to the way SSRS passes the parameters to the actual query  If someone knows better  please let me know

User · Answer

this worked for a distinct set of strings   e g   START    END    ERROR    SUCCESS    1 define a Report-Parameter   e g   log status   and check  Allow multiple values    2  define a dataset 3  open the dataset-properties window 3a  in the Query-Tab enter your query  e g   select   from your table where  CHARINDEX your column   log status 0   gt  0    3b  in the Parameters-Tab enter your Parameter  e g      Parametername   log status   Parametervalue   lt  lt Expr gt  gt   3c  for the Expr click on the  fx -Button and enter    join Parameters log status Value          finished    it s similar to Ed Harper s solution  but sorry to say this  didn t work for me

User · Answer

In the past I have resorted to using stored procedures and a function to select multiple years in a SQL Server query for reporting services  Using the Join expression in the query parameter value as suggested by Ed Harper  still would not work with an SQL IN clause in the where statement   My resolution was to use the following in the where clause along with the parameter Join expression      and charindex  cast Schl Invt Yr as char 4      Invt Yr    0

User · Answer

Create the dataset for the list in the report Right click the parameter and select available values Select newly created dataset as dataset Add the value passing to the stored procedure as value field  Add the description of the parameter to label field  If the parameter is customerID then label could be CustomerName ex   Finally  add following code to your stored procedure   declare  paramName AS NVARCHAR 500    IF RIGHT  paramName  1        BEGIN  SET  paramName   LEFT   paramName  LEN   paramName -1  END

User · Answer

This is one of the poor supported features in SQL Reporting Services   What you need to do is pass all of your selected items as a single string to your stored procedure  Each element within the string will be separated by a comma   What I then do is split the string using a function that returns the provided string as a table  See below   ALTER FUNCTION  dbo   fn MVParam       RepParam nvarchar 4000    Delim char 1        RETURNS  Values TABLE  Param nvarchar 4000  AS   BEGIN   DECLARE  chrind INT   DECLARE  Piece nvarchar 100    SELECT  chrind   1    WHILE  chrind  gt  0     BEGIN       SELECT  chrind   CHARINDEX  Delim  RepParam        IF  chrind   gt  0         SELECT  Piece   LEFT  RepParam  chrind - 1        ELSE         SELECT  Piece    RepParam       INSERT   Values Param  VALUES CAST  Piece AS VARCHAR         SELECT  RepParam   RIGHT  RepParam LEN  RepParam  -  chrind        IF LEN  RepParam    0 BREAK     END   RETURN   END   You can then reference the results in the where clause of your main query like so   where someColumn IN SELECT Param FROM dbo fn MVParam  sParameterString         I hope this you find this solution to be of use  Please feel free to pose any questions you may have   Cheers John

User · Answer

I ran into a problem with the otherwise wonderful fn MVParam  SSRS 2005 sent data with an apostrophe as 2 quotes   I added one line to fix this   select  RepParam   replace  RepParam                My version of the fn also uses varchar instead of nvarchar   CREATE FUNCTION  dbo   fn MVParam            RepParam varchar MAX        Delim char 1            RETURNS  Values TABLE  Param varchar MAX   AS      Usage   Use this in your report SP       where ID in  SELECT Param FROM fn MVParam  PlanIDList           BEGIN     select  RepParam   replace  RepParam                 DECLARE  chrind INT    DECLARE  Piece varchar MAX     SELECT  chrind   1    WHILE  chrind  gt  0       BEGIN          SELECT  chrind   CHARINDEX  Delim  RepParam           IF  chrind  gt  0             SELECT  Piece   LEFT  RepParam  chrind - 1           ELSE             SELECT  Piece    RepParam          INSERT  VALUES Param  VALUES  Piece           SELECT  RepParam   RIGHT  RepParam DATALENGTH  RepParam  -  chrind           IF DATALENGTH  RepParam    0 BREAK       END    RETURN END

User · Answer

As of MSSQL 2016 - with compatibility level 130  you can utilize String Split   to parse your joined parameter from SSRS   Say you wanted to populate a parameter from a query in SSRS and then pass that param to a stored proc or SSRS Shared Dataset    Add two datasets to your SSRS report  one that returns a list of values and labels to display in your parameter and one that has the actual data you re looking to filter   Each of these datasets can be a stored proc or shared dataset or embedded query  Create a parameter in SSRS that is NOT on the dataset you want to filter   Let s call it Customer Set Customer param to allow multiple values and set up Available Values tab with the dataset  labels  and values you want to display from the query  Right click the dataset you re looking to filter and add a parameter that IS defined in the stored procedure   Let s call it CustomerList  Click the expression button next to the value field for this parameter and do Join Parameters Customer Value       In your stored proc or shared dataset  utilize string split to break the comma-delimited  CustomerList param into an array  Customer CustID in  select value from string split  CustomerList       where value   Customer CustID

User · Answer

So multiply text values would end up in the query with single quotes around each I used  join Parameters Customer Value         So after   Value  that is comma  double-quote  single-quote  comma  single-quote  double-quote  close-bracket  simples

User · Answer

This is about using the join function to save a multi-value parameter and then restoring the exact same selections from the database later   I just finished a report that had requirements that the parameters must be saved  and when the report is opened again  the report is passed an OrderID paramater   the values previously chosen by the user must be once again selected   The report used a half of dozen parameters  each one had its own data set and resulting drop down list   The parameters were dependent upon the previous parameters to narrow the scope of the final selection  and when the report was  viewed  a stored procedure was called to populate   The stored procedure received each of the parameters passed to it from the report   It checked a storage table in the database to see if any parameters were saved for that OrderID   If not  then it saved all the parameters   If so  it updated all of the parameters for that order  this is the case where the user changes their mind later      When the report runs  there is a dataset dsParameters which is SQL text that goes out and selects the single row for that orderID if there is one   Each of the parameters in the report gets its default value from this dataset  and its selection list from a dataset dedicated to that parameter   I ran into trouble with the multi-select parameter   I used a join  Value      command in the main dataset parameter list  passing to the stored procedure a comma delimited string   But how to restore it   You can t feed the comma delimited string back to the default values box of the parameter   I had to create another dataset to split the parameter  in a manner similar to what you are talking about   It looks like this   IF OBJECT ID  tempdb   Parse   U   IS NOT NULL DROP TABLE  Parse  DECLARE  Start int   End int   Desc varchar 255   SELECT  Desc   fldDesc FROM dbCustomData dbo tblDirectReferralFormParameters WHERE fldFrom    From and fldOrderID    OrderID  CREATE TABLE  Parse  fldDesc varchar 255    SELECT  Start   1   End   1  WHILE  End  gt  0     BEGIN         SET  End   CHARINDEX      Desc  Start          IF  End   0              BEGIN                 INSERT  Parse SELECT REPLACE SUBSTRING  Desc  Start LEN  Desc           AS fldDesc                  BREAK             END         ELSE                     BEGIN                 INSERT  Parse SELECT REPLACE SUBSTRING  Desc  Start  End- Start          AS fldDesc              END         SET  Start    End   1     END  SELECT   FROM  Parse     Every time the form opens  this dataset checks the database for a saved string for this multi-valued parameter   If there is not one  it returns null   If there is on  it parses out the commas and creates a row for each of the values   Then the default values box is set to this dataset  and fldDesc   It works   When I choose one or many  they save and replenish when the form is opened again   I hope this helps   I searched for a while and did not find any mention of saving the join string in a database and then parsing it out in a dataset

User · Answer

As of MSSQL 2016 - with compatibility level 130  you can utilize String Split   to parse your joined parameter from SSRS   Say you wanted to populate a parameter from a query in SSRS and then pass that param to a stored proc or SSRS Shared Dataset    Add two datasets to your SSRS report  one that returns a list of values and labels to display in your parameter and one that has the actual data you re looking to filter   Each of these datasets can be a stored proc or shared dataset or embedded query  Create a parameter in SSRS that is NOT on the dataset you want to filter   Let s call it Customer Set Customer param to allow multiple values and set up Available Values tab with the dataset  labels  and values you want to display from the query  Right click the dataset you re looking to filter and add a parameter that IS defined in the stored procedure   Let s call it CustomerList  Click the expression button next to the value field for this parameter and do Join Parameters Customer Value       In your stored proc or shared dataset  utilize string split to break the comma-delimited  CustomerList param into an array  Customer CustID in  select value from string split  CustomerList       where value   Customer CustID

User · Answer

This works great for me   WHERE CHARINDEX CONVERT nvarchar  CustNum    CustNum   gt  0

User · Answer

Just a comment - I ran into a world of hurt trying to get an IN clause to work in a connection to Oracle 10g   I don t think the rewritten query can be correctly passed to a 10g db   I had to drop the multi-value completely   The query would return data only when a single value  from the multi-value parameter selector  was chosen   I tried the MS and Oracle drivers with the same results   I d love to hear if anyone has had success with this

User · Answer

This works great for me   WHERE CHARINDEX CONVERT nvarchar  CustNum    CustNum   gt  0

User · Answer

John Sansom and Ed Harper have great solutions  However  I was unable to get them to work when dealing with ID fields  i e  Integers   I modified the split function below to CAST the values as integers so the table will join with primary key columns  I also commented the code and added a column for order  in case the delimited list order was significant   CREATE FUNCTION  dbo   fn SplitInt         List       nvarchar 4000        Delimiter  char 1         RETURNS  Values TABLE       Position int IDENTITY PRIMARY KEY      Number int    AS    BEGIN    -- set up working variables   DECLARE  Index INT   DECLARE  ItemValue nvarchar 100    SELECT  Index   1     -- iterate until we have no more characters to work with   WHILE  Index  gt  0      BEGIN        -- find first delimiter       SELECT  Index   CHARINDEX  Delimiter  List         -- extract the item value       IF  Index   gt  0     -- if found  take the value left of the delimiter         SELECT  ItemValue   LEFT  List  Index - 1        ELSE               -- if none  take the remainder as the last value         SELECT  ItemValue    List        -- insert the value into our new table       INSERT INTO  Values  Number  VALUES  CAST  ItemValue AS int          -- remove the found item from the working list       SELECT  List   RIGHT  List LEN  List  -  Index         -- if list is empty  we are done       IF LEN  List    0 BREAK      END    RETURN    END   Use this function as previously noted with   WHERE id IN  SELECT Number FROM dbo fn SplitInt  sParameterString

User · Answer

Modification of great John solution  solve     2 quotes  error space after one of piece in parameter    ALTER FUNCTION  dbo   fn MVParam    RepParam nvarchar 4000    Delim char 1        RETURNS  Values TABLE  Param nvarchar 4000  AS BEGIN   2 quotes error set  RepParam   replace  RepParam char 39  char 39  CHAR 39   DECLARE  chrind INT DECLARE  Piece nvarchar 100  SELECT  chrind   1  WHILE  chrind   0 BEGIN   SELECT  chrind   CHARINDEX  Delim  RepParam    IF  chrind    0     SELECT  Piece   LEFT  RepParam  chrind - 1    ELSE     SELECT  Piece    RepParam   INSERT   Values Param  VALUES CAST  Piece AS VARCHAR 300        space after one of piece in parameter  LEN  RepParam    1  -1   SELECT  RepParam   RIGHT  RepParam LEN  RepParam    1  -1 -  chrind    IF LEN  RepParam    0 BREAK END RETURN END

User · Answer

I m new to the site  and couldn t figure how to comment on a previous answer  which is what I feel this should be  I also couldn t up vote Jeff s post  which I believe gave me my answer  Anyways     While I can see how some of the great posts  and subsequent tweaks  work  I only have read access to the database  so no UDF  SP or view-based solutions work for me  So Ed Harper s solution looked good  except for VenkateswarluAvula s comment that you can not pass a comma-separated string as a parameter into an WHERE IN clause and expect it to work as you need  But Jeff s solution to the ORACLE 10g fills that gap  I put those together with Russell Christopher s blog post at http   blogs msdn com b bimusings archive 2007 05 07 how-do-you-set-select-all-as-the-default-for-multi-value-parameters-in-reporting-services aspx and I have my solution   Create your multi-select parameter MYPARAMETER using whatever source of available values  probably a dataset   In my case  the multi-select was from a bunch of TEXT entries  but I m sure with some tweaking it would work with other types  If you want Select All to be the default position  set the same source as the default  This gives you your user interface  but the parameter created is not the parameter passed to my SQL    Skipping ahead to the SQL  and Jeff s solution to the WHERE IN   MYPARAMETER  problem  I have a problem all my own  in that 1 of the values   Charge   appears in one of the other values   Non Charge    meaning the CHARINDEX might find a false-positive  I needed to search the parameter for the delimited value both before and after  This means I need to make sure the comma-separated list has a leading and trailling comma as well  And this is my SQL snippet   where     and CHARINDEX       pitran LINEPROPERTYID         MYPARAMETER LIST   gt  0   The bit in the middle is to create another parameter  hidden in production  but not while developing  with    A name of MYPARAMETER LIST A type of Text  A single available value of          join Parameters MYPARAMETER Value            and a label that doesn t really matter  since it will not be displayed   A default value exactly the same Just to be sure  I set Always Refresh in both    parameters  Advanced properties   It is this parameter which gets passed to SQL  which just happens to be a searchable string but which SQL handles like any piece of text   I hope putting these fragments of answers together helps somebody find what they re looking for

User · Answer

This is what I use when passing a multi-select param to another multi-select param    SPLIT JOIN Parameters  lt your param name gt  Value

User · Answer

The below solution worked for me    In the parameter tab of your dataset properties click on the expression icon   http   chittagongit com  images fx-icon fx-icon-16 jpg  fx symbol   beside the parameter you need to allow comma delimited entry for   In the expression window that appears  use the Split function  Common Functions -  Text   Example shown below        Split Parameters ParameterName Value

User · Answer

John Sansom and Ed Harper have great solutions  However  I was unable to get them to work when dealing with ID fields  i e  Integers   I modified the split function below to CAST the values as integers so the table will join with primary key columns  I also commented the code and added a column for order  in case the delimited list order was significant   CREATE FUNCTION  dbo   fn SplitInt         List       nvarchar 4000        Delimiter  char 1         RETURNS  Values TABLE       Position int IDENTITY PRIMARY KEY      Number int    AS    BEGIN    -- set up working variables   DECLARE  Index INT   DECLARE  ItemValue nvarchar 100    SELECT  Index   1     -- iterate until we have no more characters to work with   WHILE  Index  gt  0      BEGIN        -- find first delimiter       SELECT  Index   CHARINDEX  Delimiter  List         -- extract the item value       IF  Index   gt  0     -- if found  take the value left of the delimiter         SELECT  ItemValue   LEFT  List  Index - 1        ELSE               -- if none  take the remainder as the last value         SELECT  ItemValue    List        -- insert the value into our new table       INSERT INTO  Values  Number  VALUES  CAST  ItemValue AS int          -- remove the found item from the working list       SELECT  List   RIGHT  List LEN  List  -  Index         -- if list is empty  we are done       IF LEN  List    0 BREAK      END    RETURN    END   Use this function as previously noted with   WHERE id IN  SELECT Number FROM dbo fn SplitInt  sParameterString

User · Answer

ORACLE   The  IN  phrase  Ed s Solution  won t work against an Oracle connection  at least version 10   However  found this simple work-around which does  Using the dataset s parameter s tab turn the multi-value parameter into a CSV        name  join Parameters name Value        Then in your SQL statement s WHERE clause use the instring function to check for a match       INSTR  name  TABLE FILENAME   gt  0

User · Answer

If you want to pass multiple values to RS via a query string all you need to do is repeat the report parameter for each value     For example  I have a RS column called COLS and this column expects one or more values      amp rp COLS 1 amp rp COLS 1 amp rp COLS 5 etc

User · Answer

It would probably be easier to add the multi values to a table first and then you can join or whatever you d like  even with wildcards  or save the data to another table for later use  or even add the values to another table    Set the Parameter value via expression in the dataset     SELECT DISTINCT   FROM  VALUES     amp  JOIN Parameters SearchValue Value            amp        AS tbl Value     The query itself   DECLARE  Table AS TABLE  Value nvarchar max    INSERT INTO  Table EXEC sp executeSQL  SearchValue    Wildcard example   SELECT   FROM YOUR TABLE yt   INNER JOIN  Table rt ON yt  Join Value  LIKE       rt  Value          I d love to figure out a way to do it without dynamic SQL but I don t think it ll work due to the way SSRS passes the parameters to the actual query  If someone knows better  please let me know

User · Answer

If you want to pass multiple values to RS via a query string all you need to do is repeat the report parameter for each value     For example  I have a RS column called COLS and this column expects one or more values      amp rp COLS 1 amp rp COLS 1 amp rp COLS 5 etc

[sql] Passing multiple values for a single parameter in Reporting Services

Examples related to sql

Examples related to reporting-services

Examples related to parameters

Examples related to query-string