May 24, 2012 at 7:41 am
Hi,
In my report , @Pack should be a multi select but when I try to run the ssrs report if i select 1 or 2 values in the dropdown it runs just fine but when I try to select 3-4 values, the report still shows initial 2 choices. My guess is it is just filtering first 2 dates based on ',' . Dates is varchar field here.
Any ideas please?
Thanks for your help.
--MY Report dataset for @Dates Param
create table #123
(dates varchar(25) null)
INSERT INTO #123 (dates)
SELECT '1/4/1996'
UNION ALL
SELECT ' 10/11/1996'
UNION ALL
SELECT '1/1/2008'
UNION ALL
SELECT '12/16/2011'
UNION ALL
SELECT '5/02/2011'
union all
SELECT '05/10/2011'
select * from #123
drop table #123
--This is how I filter in my procedure
declare @Dates varchar(25)
select Name, age
WHERE (p.dates IN (SELECT LTRIM(RTRIM(Item)) FROM dbo.fnbreakString(@Dates, ',')) Or @Dates IS NULL)
---Function is below:
ALTER FUNCTION [dbo].[fnbreakString](@Text varchar(max),@Delimiter varchar(2)= ' ')
RETURNS @Strings TABLE
(
Position int IDENTITY PRIMARY KEY,
Item varchar(max)
)
AS
BEGIN
DECLARE @index int
SET @index = -1
WHILE (LEN(@Text) > 0)
BEGIN
SET @index = CHARINDEX(@Delimiter,@Text)
IF (@Index = 0) And (LEN(@Text) > 0)
BEGIN
INSERT INTO @Strings VALUES (@Text)
BREAK
END
IF (@Index > 1)
BEGIN
INSERT INTO @Strings VALUES (LEFT(@Text,@Index - 1))
SET @Text = RIGHT(@Text, (LEN(@Text) - @index))
END
ELSE
SET @Text = RIGHT(@Text, (LEN(@Text) - @index))
END
RETURN
END
May 25, 2012 at 1:54 pm
It looks like your stored procedure contains an input parameter defined as varchar(25), which needs to encapsulate the entire comma-separated string passed in from your multi-select SSRS report. Based on your definition the third (as well as any subsequent) value is truncated; I'd look at expanding that input parameter so that it can accept all possible values as defined in your SSRS parameter.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply