Reports from Reporting Server

Introduction

Here is some code to list out the reports from the reporting server.

Using the code

SET @sql = ';with XMLNAMESPACES (DEFAULT ''http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition'',  
	 ''http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'' AS rd)  
	  SELECT x.value(''@Name'',''VARCHAR(100)'') AS ReportParameterName,  
	  x.value (''DataType[1]'', ''VARCHAR(100)'') AS DataType,  
	  x.value (''AllowBlank[1]'', ''VARCHAR(50)'') AS AllowBlank,  
	  x.value (''Prompt[1]'', ''VARCHAR(100)'') AS Prompt,  
	  x.value (''Hidden[1]'', ''VARCHAR(100)'') AS Hidden,  
	  x.value (''data(DefaultValue/Values/Value)[1]'', ''VARCHAR(100)'') AS Value  
	  FROM (  
		SELECT  PATH  
		  , NAME  
		  , CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS ReportXML   
		FROM '+@strReportServerDB+'.dbo.Catalog   
		WHERE CONTENT IS NOT NULL AND TYPE = 2  
		) A  
		CROSS APPLY ReportXML.nodes(''/Report/ReportParameters/ReportParameter'') R(x)  
		WHERE NAME = '''+ @strReport +''' ORDER BY NAME  
	 '  
	 --print @sql  
	 exec(@sql)