Reports from Reporting Server

Introduction

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

Using the code

We can read and bind reports from the server using the following 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)