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.
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>)
IN clause this way. There are literally hundreds of questions covering this topic - see stackoverflow.com/… –
Tristis int type –
Tristis JOIN. You can set parameter value just as Parameters!<your param name>.Value –
Osteo This is what I use when passing a multi-select param to another multi-select param.
=SPLIT(JOIN(Parameters!<your param name>.Value,","),",")
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
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,','))
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
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
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 )
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:
CustomerCustomer param to allow multiple values and set up Available Values tab with the dataset, labels, and values you want to display from the query.CustomerList.Join(Parameters!Customer.Value, ",")@CustomerList param into an array:
Customer.CustID in (select value from string_split(@CustomerList, ',') where value = Customer.CustID)Modification of great John solution, solve:
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
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.
declare @paramName AS NVARCHAR(500),
IF RIGHT(@paramName, 1) = ',' BEGIN SET @paramName = LEFT((@paramName, LEN((@paramName)-1) END
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.
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"))
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..
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:
="," +
join(Parameters!MYPARAMETER.Value,",") + "," and a label thatIt 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.
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
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.
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.
This works great for me:
WHERE CHARINDEX(CONVERT(nvarchar, CustNum), @CustNum) > 0
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 :)
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,",")
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 );
© 2022 - 2024 — McMap. All rights reserved.
