I am using RS 2000. I have a multi select parameter where I can select multiple states by separating with a comma. I am trying to figure out how to incorporate an "All" parameter.
Query:
Select [name], city, state, zipcode
From Golf inner join charlist_to_table(@.State,Default)f on State = f.str
Function:
CREATE FUNCTION charlist_to_table
(@.list ntext,
@.delimiter nchar(1) = N',')
RETURNS @.tbl Table (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000),
@.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(@.delimiter, @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
ltrim(rtrim(@.leftover)))
RETURN
END
GO
Anyone have any ideas?
Thanks,
Deb
I think by using the UNION function from sql we can add "All" parameter to the query.
Here I have taken only one field for the example but you can change the whole table.
Ex:
Select 'All' AS NAME
UNION
Select name
From Golf inner join charlist_to_table(@.State,Default)f on State = f.str
By using this query this should give you 'All' parameter.
Hope this helps...
--Deepak
|||Thanks for your reply, however that doesn't work, as it just inserts "All" into the name. I know how to add the all parameter using Union with a drop down select list, but I am unsure how to do it with this function. Any other suggestions?
|||Bump
|||I finally figured out a simple solution - In the Report Parameters window under Default Values, Non-queried enter whatever the criteria is for "All'.
Deb
