Reports and SSRS - 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 )

ċ
usp_GetReportParameters.sql
(2k)
Andy Hughes,
Jul 25, 2012, 11:41 AM
Comments