Archive

Posts Tagged ‘SSRS’

T-SQL : Get all folder names from SSRS Reports Manager/Server

We all know, we can query the report server database and get very useful information, such as reports details, directory and schedules etc. This particular thread belongs to one such category, this query will provide you all available folders from a report server


select
ltrim(rtrim(Name))
from [dbo].[Catalog]
where type =1
and ParentID is not null

I will soon post other queries that will provide much more useful properties/information from report server.

Happy Querying 😉

Advertisements

Split Comma Separated values

September 6, 2012 1 comment

In this thread I would like to share my SQL scripts to split the comma separate values in to  individual values. This script can be effectively used when passing multivalue parameters from the SSRS report to stored procedures.

TSQL – CODE:

DECLARE @text VARCHAR (50)

DECLARE @Delimiter CHAR (1)

DECLARE @length INT

DECLARE @table TABLE

(

value VARCHAR(50 )

)

SET @text = '1,2,4,887,77889,1,132,4,5,455,4478'

SET @Delimiter = ','

SET @length = Len( @text)

WHILE ( @length != 0 )

BEGIN

IF( Charindex(@delimiter , @text) <> 0 )

BEGIN

INSERT INTO @table(value )

SELECT LEFT( @text, Charindex(@Delimiter , @text) - 1 )

SET @text = RIGHT(@text , Len (@text) - Charindex(@Delimiter , @text))

SET @length= Len(@text )

END

ELSE

BEGIN

INSERT INTO @table(value )

SELECT @text

SET @length = 0

END

END

SELECT *

FROM   @table

Result:

image_thumb

This can be also created as a function in SQL Server as shown below,

CREATE FUNCTION dbo.Split(@text      VARCHAR (MAX),

@Delimiter CHAR (1))

RETURNS @table TABLE (VALUE VARCHAR(50))

AS

Begin

DECLARE @length INT

SET @length = Len(@text)

WHILE ( @length != 0 )

BEGIN

IF( Charindex(@delimiter, @text) <> 0 )

BEGIN

INSERT INTO @table(value)

SELECT LEFT(@text, Charindex(@Delimiter, @text) - 1)

SET @text = RIGHT(@text, Len (@text) - Charindex(@Delimiter, @text))

SET @length= Len(@text)

END

ELSE

BEGIN

INSERT INTO @table(value)

SELECT @text

SET @length = 0

END

END

RETURN

END;

To use the function, execute below code,

SELECT * FROM dbo.SPLIT ( '1,2,4,887,77889,1,132,4,5,455,4478',',') 

OUTPUT:

c

%d bloggers like this: