sqlreporting-servicesparametersquery-string

Reputation: 2431

Passing multiple values for a single parameter in Reporting Services

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.

Upvotes: 74

Answers (21)

Robert Hartshorn

Reputation: 166

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)

Upvotes: 4

sherebry

Reputation: 194

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,",")

Upvotes: 1

cjonas

Reputation: 101

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

3b) in the Parameters-Tab enter your Parameter, e.g.
Parametername: @log_status ; Parametervalue: <<Expr>>
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 )

Upvotes: 4

Aaron

Reputation: 11

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

Upvotes: 0

RichardBSmith

Reputation: 9

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

Upvotes: 1

jon

Reputation: 33

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('" & JOIN(Parameters!SearchValue.Value, "'),('") & "')) 
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.

Upvotes: 2

user3688168

Reputation: 19

This works great for me:

WHERE CHARINDEX(CONVERT(nvarchar, CustNum), @CustNum) > 0

Upvotes: 1

Nilanka Sooriyabandara

Reputation: 21

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

Upvotes: 2

Scott

Reputation: 11

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

Upvotes: 1

Minks

Reputation:

This is what I use when passing a multi-select param to another multi-select param.

=SPLIT(JOIN(Parameters!<your param name>.Value,","),",")

Upvotes: 50

J Steen

Reputation: 11

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

Upvotes: 1

Mark Belshaw

Reputation: 11

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

Upvotes: 1

Wilfred van Dijk

Reputation: 21

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

Upvotes: 1

Bob Amy

Reputation: 41

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 > 0
      BEGIN
         SELECT @chrind = CHARINDEX(@Delim,@RepParam)
         IF @chrind > 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

Upvotes: 4

Gerry

Reputation: 11

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.

&rp:COLS=1&rp:COLS=1&rp:COLS=5 etc..

Upvotes: 1

grum

Reputation: 31

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

Upvotes: 3

John Sansom

Reputation: 41899

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

Upvotes: 24

CodeGrue

Reputation: 5933

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 > 0

    BEGIN

      -- find first delimiter
      SELECT @Index = CHARINDEX(@Delimiter,@List)

      -- extract the item value
      IF @Index  > 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,','))

Upvotes: 10

Jeff

Reputation: 8148

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

Upvotes: 5

ScottLenart

Reputation: 1210

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.

Upvotes: 2

Ed Harper

Reputation: 21505

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!<your param name>.Value,",")

In your query, you can then reference the value like so:

where yourColumn in (@<your param name>)

Upvotes: 105

Passing multiple values for a single parameter in Reporting Services

Answers (21)

I had to create another dataset to split the parameter, in a manner similar to what you are talking about. It looks like this:

Related Questions