Get Report Parameter List

Applicability:

SQL Server 2000: Not Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: Sorna Kumar Muthuraj

Date: 9 Jul 2011

Description

Retrieves the report parameters and their attributes for one or many reports stored in the Report Server Database Catalog table.

The attributes are listed on MSDN in the ReportDefinition.xsd for SQL Server 2008. There are also more definitions in the online SQL Server 2008 manual.

NOTE: It is assumed that the Report Server Database has its default name of 'ReportServer'

Code

Stored Procedure:

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 )