The Business Intelligence Blog

September 20, 2009

Creating an SSRS report using Stored Procedure

Filed under: SSRS — Shahfaisal @ 7:56 pm

Creating an SSRS report using Stored Procedure is little bit tricky when the Stored Procedure expects multiple values being passed to parameters. All the user selected parameters are passed as a single string to the stored procedure,  so you have to use some kind of a function to split the string using ‘,’ as the delimiter. I use the function below. In the where clause of the stored procedure code, use this function to capture multiple parameters and split them to make it seem like an array. I hope this will help.

CREATE FUNCTION [dbo].[udf_SplitString]
(
@Keyword VARCHAR(5000)
)
RETURNS @SplitKeyword TABLE (Keyword VARCHAR(1000))
AS
BEGIN
DECLARE @Word VARCHAR(500)
DECLARE @TempKeyword TABLE (Keyword VARCHAR(5000))
DECLARE @Delimiter VARCHAR(10)
SET @Delimiter = ‘,’
WHILE (CHARINDEX(@Delimiter, @Keyword, 1)>0)
BEGIN
SET @Word = SUBSTRING(@Keyword, 1 , CHARINDEX(@Delimiter, @Keyword, 1) – 1)
SET @Keyword = SUBSTRING(@Keyword, CHARINDEX(@Delimiter, @Keyword, 1) + 1, LEN(@Keyword))
INSERT INTO @TempKeyword VALUES(@Word)
END
INSERT INTO @TempKeyword VALUES(@Keyword)
INSERT @SplitKeyword
SELECT * FROM @TempKeyword
RETURN
END

Advertisements

1 Comment »

  1. really this site wonderful.

    Comment by srinivasarao.goli — November 16, 2012 @ 12:09 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: