April 21, 2009 at 8:30 am
Why not take it a step further, like this:
=Iif(Parameters!CategoryName.Count = 1, "'" & Parameters!CategoryName.Value(0) & "'", "'" & Join(Parameters!CategoryName.Value, "','") & "'")
April 21, 2009 at 8:31 am
Alex,
I also want to comment that, while I provided an alternate method, I do appreciate the article and the method it presents. As always "It depends" is the answer.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 21, 2009 at 8:43 am
Alex,
Do you have an example of using the WHILE loop in a stored proc? I have found the query parameters section in my report for this part:
"=Join(Parameters!CategoryName.Value, "'") ."
but my stored proc change is where I am having trouble and am more comfortable using the while loop (I am still a beginner :-D)
--Background- I am trying to build a report with cascading drop downs and am using multi-value string parameters. I can run the report correctly when using one value but when I choose multiple values my report returns nothing.
April 21, 2009 at 9:04 am
Wow, that is a lot of work. I've been using multi-value parameters for a while now. I created a split function on the SQL Server that I use for every multi-value parameter. It works with one, two, or select all:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_SSRS_SplitMultiValueParams]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[udf_SSRS_SplitMultiValueParams]
go
CREATE FUNCTION [dbo].[udf_SSRS_SplitMultiValueParams]
/* This function is used to split up multi-value parameters */
(
@ItemList NVARCHAR(4000),
@delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item VARCHAR(50))
AS
BEGIN
DECLARE @tempItemList NVARCHAR(4000)
SET @tempItemList = @ItemList
DECLARE @i INT
DECLARE @Item NVARCHAR(4000)
SET @tempItemList = REPLACE (@tempItemList, @delimiter + ' ', @delimiter)
SET @i = CHARINDEX(@delimiter, @tempItemList)
WHILE (LEN(@tempItemList) > 0)
BEGIN
IF @i = 0
SET @Item = @tempItemList
ELSE
SET @Item = LEFT(@tempItemList, @i - 1)
INSERT INTO @IDTable(Item) VALUES(@Item)
IF @i = 0
SET @tempItemList = ''
ELSE
SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
SET @i = CHARINDEX(@delimiter, @tempItemList)
END
RETURN
END
Then, I pass in the multivalue parameter to the stored procedure, just like I would pass in any parameter. Inside the stored procedure, I add it wherever I need it with the following:
where metric1 in (select item from dbo.udf_SSRS_SplitMultiValueParams(@metric1, ','))
Works great for me, and since it is technically a table, you can join to it instead of using IN, for performance consideration. I always use an int field instead of a string for the value field in the parameter, and give something meaningful to the users under the label. This cuts down on the size of the string sent into the database, as well as dealing with strings containing commas.
And if you have so many items in the MV parameter that you run up against the 4000 char limit, then you should re-think your use of the multi-value parameter, and maybe segment it by making it dependant on another parameter, as sort of a "grouping" item.
..if this is helpful, let me know!
April 21, 2009 at 9:59 am
We're using this function
CREATE FUNCTION [dbo].[fn_ParseParam]
(@RepParam nvarchar(4000), @Delim char(1)= ',')
RETURNS @Values TABLE (Param nvarchar(4000))AS
BEGIN
DECLARE @chrind INT
DECLARE @Piece nvarchar(4000)
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 (LTRIM(@Piece))
SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
IF LEN(@RepParam) = 0 BREAK
END
RETURN
END
April 21, 2009 at 10:14 am
Alex,
Nice article and thanks for taking the time to write this up.
And while SSRS's multi-valued approach is a bit laking, I think we all would choose having this option over nothing at all.
The more you are prepared, the less you need it.
April 21, 2009 at 11:50 am
I always just write a UDF that splits the comma seperated values, and then use 'IN' instead of '=' in my compare.
UDF looks like this.
/****** Object: UserDefinedFunction [dbo].[Split] Script Date: 04/21/2009 13:47:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[Split]
(
@List nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Value nvarchar(100)
)
AS
BEGIN
While (Charindex(@SplitOn,@List)>0)
Begin
Insert Into @RtnValue (value)
Select
Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End
Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))
Return
END
GO
then you can call the udf like so.
SELECT
col1,
col2
WHERE
ThemeID IN (SELECT Convert(INT, VALUE) FROM dbo.Split(@p_ThemeID,','))
Technical conquests for the nerd in all of us
www.tavislovell.com
April 21, 2009 at 1:23 pm
Got it to work! Now I can get three reports on the server that I have been waiting to go live for quite a while. Thanks to everyone for your help :w00t:
April 21, 2009 at 1:29 pm
For internal SQL only - Yes.
April 21, 2009 at 2:48 pm
Yes, I think it's much ado about nothing. It is what it is for now! I usually just parse it out in my stored proc like this:
set @Industry = @Industry + ','
while charindex(',',@Industry,1) > 0
Begin
set @IndustryStringLength = len(@Industry)
set @MyEndPosition = charindex(',',@Industry,1)
set @MyIndustry = substring(@Industry,1,@myendposition-1)
set @Industry = substring(@Industry,@myendposition+1,@IndustryStringLength)
insert into @TempIndustry Select @MyIndustry
End
Put it into a temp table and then use something like "Where IndustryCode In (Select Industry from @TempIndustry)" for my actual select query.
It's a pain, but it works!
April 21, 2009 at 8:16 pm
Where I work, we resolved this issue by using a function that returns a table. So, the report passes the comma delimited string of values to the stored procedure which in turn calls the function in the WHERE clause.
Within the procedure the string is passed to a function that contains a looping structure that essentially converts the delimited string into a table and returns that table (the table consists of a single column of the values that made up the string).
Then in the proc we use the IN statement with the function. Here's an example.
Let's say the report passes the parameter value 'Red,Blue,Green,Yellow' to a proc.
We have a proc such as this (psuedocode)
CREATE PROC spGetFabricsByColor @color
AS
SELECT Fabric, Style,Pattern
FROM Fabrics
WHERE Color in (select Results from dbo.FN_ReportString(@color))
The function FN_ReportString returns a table consisting of a single column (Results) as follows:
Results
----------
Red
Blue
Green
Yellow
April 22, 2009 at 8:34 am
Thank you very much, Simon. It works. Next question please how to exlude or make invisible All in multi-valued parameters. Thank you. Irina.
June 30, 2009 at 3:13 pm
I'm actually wondering how to determine if ALL values are selected ?
I don't want to pass anything to the procedure if ALL is selected. Its just to much overhead and it destroys the query plan if you are adding all the entries into the query vs omitting it completely.
Let me know if you all find a solution.
Thanks,
Raul
June 30, 2009 at 3:51 pm
I would like to know the answer for this question also. I would like to exclude "select all" options from some reports if I know IN class is going to be big.
If you add values to default value of parameter and no set of available values was defined then no "Select All" value appears in drop down list. But if you have available value from the query?
Best regards, Irina.
June 30, 2009 at 3:56 pm
There is no way I know of to hide ALL. Instead you have to limit the number of values passed to the SP.
I answered this earlier in the thread
You can limit to 5 by having a second parameter that is based on the first (the multi value parameter).
Set the query to use the second parameter and set the default value for the second parameter to something like.
=iif(Parameters!lastname.Count>5,"-1",Join (Parameters!lastname.Value ,","))
Where lastname is the parameter the user selects. This results in the query being executed with an invalid value. You can then in the report display a message saying they need to select < 5 options.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 15 posts - 31 through 45 (of 69 total)
You must be logged in to reply to this topic. Login to reply