OK, so in my last
post I talked about how SQL server reporting services does not handle multi-value parameters and I would need to work around it. So I thought I'd share my work around for those that might need to do the same.
Basically I ended up writing a User Defined Function and compiling it on the SQL server I was pulling the reporting data from. This function will parse a string using the supplied delimiter and then return a table with an index and a bigint. I needed to return a recordset of bigints for a comparison but you may need another data type.
<code>
CREATE FUNCTION StringToBigIntTable (
@TargetString varchar(8000),
@SearchChar varchar(1)
)
RETURNS @Set TABLE (
idPos smallint not null identity,
ExtractedInt bigint null
)
AS
BEGIN
SET @TargetString = COALESCE(@TargetString,'')
DECLARE @SearchCharPos smallint,
@LastSearchCharPos smallint
SET @SearchCharPos = 0
WHILE 1=1
BEGIN
SET @LastSearchCharPos = @SearchCharPos
SET @SearchCharPos = CHARINDEX( @SearchChar, @TargetString, @SearchCharPos + 1 )
IF @SearchCharPos = 0
BEGIN
INSERT @Set( ExtractedInt ) VALUES ( Convert(bigint, SUBSTRING( @TargetString, @LastSearchCharPos + 1, DATALENGTH( @TargetString ) )) )
BREAK
END
ELSE
INSERT @Set( ExtractedInt ) VALUES ( Convert(bigint, SUBSTRING( @TargetString, @LastSearchCharPos + 1, @SearchCharPos - @LastSearchCharPos - 1 )) )
END
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
</code>
Then in my data view for my report (the RDL file) I use the following sql statement , calling my function to parse the parameter and return a recordset of bigints:
<code>
SELECT *
FROM vwExpectedReport
WHERE ProgramID IN (select ExtractedInt from
dbo.StringToBigIntTable(@ProgramIDs, ','))
</code>
I hope this helps someone down the road.