Get Report Parameter List

DROP PROC usp_GetReportParameters

GO

CREATE PROC usp_GetReportParameters @ReportName NVARCHAR(850) = NULL

AS

/****************************************************

Purpose: To retreive the report parameter definitions

for one or many reports.

Uses the Catalog table in the report server

database (assumed to be called by its default

name of 'ReportServer')

Author: Sorna Kumar Muthuraj

History: 9 Jul 2011

*****************************************************/

SELECT

a.name AS ReportName,

Paravalue.value('Name[1]', 'VARCHAR(250)') AS ParamName,

Paravalue.value('Type[1]', 'VARCHAR(250)') AS ParamDataType,

Paravalue.value('Nullable[1]', 'VARCHAR(250)') AS Nullable,

Paravalue.value('AllowBlank[1]', 'VARCHAR(250)') AS AllowBlank,

Paravalue.value('MultiValue[1]', 'VARCHAR(250)') AS MultiValue,

Paravalue.value('UsedInQuery[1]', 'VARCHAR(250)') AS UsedInQuery,

Paravalue.value('Prompt[1]', 'VARCHAR(250)') AS Prompt,

Paravalue.value('DynamicPrompt[1]', 'VARCHAR(250)') AS DynamicPrompt,

Paravalue.value('PromptUser[1]', 'VARCHAR(250)') AS PromptUser,

Paravalue.value('State[1]', 'VARCHAR(250)') AS State

FROM

(

SELECT

C.Name AS Name,

CONVERT(XML,C.Parameter) AS ParameterXML

FROM ReportServer.dbo.Catalog C

WHERE C.Content IS NOT NULL

AND C.TYPE = 2 -- only reports

AND C.Name LIKE '%' + ISNULL(@ReportName, Name) + '%' -- if param is null, then return all reports

) AS a

CROSS APPLY ParameterXML.nodes('//Parameters/Parameter') p ( Paravalue )