Passing multiple values ​​for a single parameter in Reporting Services

Question

There are several Multi-Select options in my report. Am I trying to find a way to pass multiple values ​​for a single parameter in a web query string? If I pass one value, it works fine.

The report works fine, choosing several options for one parameter. My problem is the web query string.

Answer 1

While John Sans's solution works, there is another way to do this, without having to use the potentially inefficient scalar-valued UDF. In the SSRS report, on the query definition parameters tab, set the parameter value

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

In your query, you can reference the value as follows:

 where yourColumn in (@<your param name>) 

Answer 2

This is what I use when passing the multi-select parameter to another multi-select parameter.

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

Answer 3

This is one of the poor supported features in SQL Reporting Services.

What you need to do is pass all the items you select as a single line to the stored procedure. Each item within the line will be separated by a comma.

Then I 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 as follows:

 where someColumn IN(SELECT Param FROM dbo.fn_MVParam(@sParameterString,',')) 

Hope you find this solution helpful. Please feel free to ask any questions you may have.

Cheers, John

Answer 4

John Sansom and Ed Harper have great solutions. However, I could not get them to work when working with identifier fields (i.e. Integers). I changed the split function below to CAST values ​​as integers, so the table will be merged with the primary key columns. I also commented on the code and added a column for the order, in case the order of the delimited list 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 noted earlier:

 WHERE id IN (SELECT Number FROM dbo.fn_SplitInt(@sParameterString,',')) 

Answer 5

ORACLE:

The phrase "IN" (Ed Solution) will not work against an Oracle connection (at least version 10). However, I found this simple job that does. Using the dataset parameters tab, enable the multi-value parameter in the CSV:

  :name =join(Parameters!name.Value,",") 

Then in your SQL statement, the WHERE clause uses the instring function to check for compliance.

  INSTR(:name, TABLE.FILENAME) > 0 

Answer 6

I had a problem with the wonderful fn_MVParam. SSRS 2005 sent data with an apostrophe in the form of 2 quotes.

I added one line to fix this.

 select @RepParam = replace(@RepParam,'''''','''') 

My version of 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 

Answer 7

A modification of John's great decision, decide:

• "2 quotation marks" error

• space after one part in the 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
  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 

Answer 8

Just a comment - I came across a world of pain trying to get the IN clause to work in conjunction with Oracle 10g. I do not think that the rewritten request can be correctly transmitted in 10g db. I had to completely abandon ambiguity. The query returned data only when a single value was selected (from a parameter selector with multiple values). I tried MS and Oracle drivers with the same results. I would love to hear if someone has been successful in this.

Answer 9

• Create a dataset for the list in the report
• Right-click the option and select the available values.
• Select a new dataset as a dataset
• Add the value passed to the stored procedure as the value field
• Add a description of the parameter in the label field (if the parameter is a customer identifier, then the label may be CustomerName ex.)
• Finally, add the following code to your stored procedure.

declare @paramName AS NVARCHAR (500),

IF RIGHT (@paramName, 1) = ',' START SET @paramName = LEFT ((@paramName, LEN ((@paramName) -1) END

Answer 10

This works fine for me:

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

Answer 11

It would probably be easier to add multiple values ​​to the table first, and then you can join as you like (even with wildcards) or save the data in another table for later use (or even add the values ​​to another table).

Set the parameter value through the expression in the dataset:

 ="SELECT DISTINCT * FROM (VALUES('" & JOIN(Parameters!SearchValue.Value, "'),('") & "')) AS tbl(Value)" 

The request 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 would like to figure out a way to do this without dynamic SQL, but I don't think this will work because of the way SSRS passes parameters to the actual query. If anyone knows better, please let me know.

Answer 12

What you can also do is add this code to your stored procedure:

 set @s = char(39) + replace(@s, ',', char(39) + ',' + char(39)) + char(39) 

(Assuming @s is a multi-valued string (e.g., "A, B, C"))

Answer 13

If you want to pass multiple values ​​to RS via the query string, all you have to do is repeat the report parameter for each value.

For example; I have an RS column called COLS, and this column expects one or more values.

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

Answer 14

I am new to this site and cannot figure out how to comment on the previous answer, and that is what I consider. I also could not vote for the post of Jeff, who, it seems to me, gave me my answer. Anyway...

As long as I see how some of the great posts and subsequent tweaks work, I only have read access to the database, so no UDF, SP solutions or views work. So Ed Harper's solution looked pretty good, with the exception of VenkateswarluAvula's comment that you cannot pass a comma-separated string as a parameter to the WHERE IN clause and expect it to work as needed. But Jeff's solution for ORACLE 10g fills this gap. I put them along with the Russell Christopher 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-screen parameter MYPARAMETER using any source of available values ​​(possibly a data set). In my case, multi-selection was from several TEXT entries, but I'm sure it will work with other types with some settings. If you want “Select All” to be the default, set the same source as the default. This gives you a user interface, but the generated parameter is not the parameter passed to my SQL.

Skipping forward to SQL and Jeff's solution for the WHERE IN (@MYPARAMETER) problem, I have a problem with all of my own, in that 1 of the values ​​("Charge") appears in one of the other values ​​('Non Charge'), that is, CHARINDEX can find false positive. I needed to look for a parameter for the separation value both before and after. This means that I need to make sure that the comma-separated list has a leading and learning comma. And this is my SQL fragment:

 where ... and CHARINDEX(',' + pitran.LINEPROPERTYID + ',', @MYPARAMETER_LIST) > 0 

A bit in the middle is to create another parameter (hidden during production, but not during development):

• Name MYPARAMETER_LIST
• Text type
• The only available value is ="," + join(Parameters!MYPARAMETER.Value,",") + "," and a label that does not matter (since it will not be displayed).
• The default value is exactly the same.
• To be sure, I set Always Refresh in both Advanced Options.

This parameter is passed to SQL, which is just a search string, but SQL processes like any piece of text.

Hope these answer snippets help someone find what they are looking for.

Answer 15

In the past, I resorted to using stored procedures and functions to select several years in a SQL Server query for Reporting Services. Using the Join expression in the query parameter value suggested by Ed Harper will still not work with the SQL IN clause in the where clause. My solution was to use the following in the where clause along with the Join expression parameter: and charindex (cast (Schl.Invt_Yr as char (4)), @Invt_Yr)> 0

Answer 16

It is about using the join function to save a multi-valued parameter and then restore the same parameters from the database later.

I just finished the report with the requirements that the parameters should be saved, and when the report is opened again (the report is transmitted by the OrderID parameter), the values ​​previously selected by the user must be selected again.

The report used half of ten parameters, each of which had its own data set and as a result of the drop-down list. The parameters depended on the previous parameters in order to narrow the final selection, and when the report was “viewed”, a stored procedure was called to fill out.

The stored procedure received each of the parameters passed to it from the report. He checked the storage table in the database to see if any parameters were stored for this OrderID. If not, then he saved all the parameters. If so, it updates all the parameters for this order (this is the case when the user changes his mind later).

When the report is run, there is a dsParameters dataset that displays the SQL text and selects one row for this order identifier, if any. Each of the parameters in the report receives its default value from this data set and its selection list from the data set dedicated to this parameter.

I am having a problem with the multi-select parameter. I used the join command (@Value, ",") in the parameter list of the main dataset, passing a comma-separated string to the stored procedure. But how to restore it? You cannot pass a comma-delimited string back to the parameter's default value field.

I had to create another dataset to separate a parameter 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 

---

Each time a form is opened, this dataset checks the database for the stored row for this multi-valued parameter. If it is not, it returns null. If it is turned on, it parses the commas and creates a string for each of the values.

Then, this dataset and fldDesc are set to the default value field. It is working! When I select one or more, they save and replenish when the form opens again.

Hope this helps. I searched for a while and did not find any mention of storing the union string in the database, and then parsed it in the data set.

Answer 17

Thus, multiplying text values ​​will appear in the request with single quotes around each = join (Parameters! Customer.Value, "','"). So after ".Value" it is a comma, double quote, single quote, comma, single quote, double quote, close-bracket. simply:)

Answer 18

I need a solution for Oracle, and I found that this worked for me inside my query for my report for DB> = 10g.

select * from which in (select regexp_substr (, '[^,] +', 1, level) from the double 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