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.
This question is related to
sql
reporting-services
parameters
query-string
The answer is
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"))
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
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.
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 );
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.
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
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.
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 )
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 );
This is what I use when passing a multi-select param to another multi-select param.
=SPLIT(JOIN(Parameters!<your param name>.Value,","),",")
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..
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.
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 :)
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
Customerparam to allow multiple values and set upAvailable Valuestab 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
@CustomerListparam into an array:Customer.CustID in (select value from string_split(@CustomerList, ',') where value = Customer.CustID)
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,','))
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
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 )
This works great for me:
WHERE CHARINDEX(CONVERT(nvarchar, CustNum), @CustNum) > 0
- 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
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
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"))
This is what I use when passing a multi-select param to another multi-select param.
=SPLIT(JOIN(Parameters!<your param name>.Value,","),",")
This works great for me:
WHERE CHARINDEX(CONVERT(nvarchar, CustNum), @CustNum) > 0
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.
- 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
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.
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
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,",")
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
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
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..
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.
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
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.
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,','))
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
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
Customerparam to allow multiple values and set upAvailable Valuestab 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
@CustomerListparam into an array:Customer.CustID in (select value from string_split(@CustomerList, ',') where value = Customer.CustID)
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
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,",")
Source: Stackoverflow.com