Script to determine SQL Server Reporting Services parameters, path and default values

By: Sankar Reddy   |   Comments (27)   |   Related: > Reporting Services Parameters

Problem
Our company has hundreds of reports with daily, weekly, monthly, quarterly, yearly and ad-hoc schedules. Is there a way to identify all the reports with their path, parameters and default values as well as if the parameters are hidden or visible when executing the reports?  How do I check this information programmatically without manually reviewing each report?  Is this information stored in system tables or DMV's that I can query?

Solution
SQL Server Reporting Services stores a lot of metadata about the reports in a rich set of tables in the ReportServer database. For this solution, we will take advantage of the dbo.Catalog table where the report characteristics are stored in a column named Content.  The data is in XML format, but it is stored as image data type.  Luckily, SQL Server 2005 offers a rich set of XML functions and we will be using those to generate the data rather than having to review the reports manually.

--Find all the reports, and thier parameters and thier default values
;WITH XMLNAMESPACES (
DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd --ReportDefinition
)
SELECT 
   NAME
   , PATH
   , 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 ReportServer.dbo.Catalog 
   WHERE CONTENT IS NOT NULL AND TYPE = 2
   ) A
CROSS APPLY ReportXML.nodes('/Report/ReportParameters/ReportParameter') R(x)
--WHERE NAME = 'Sales_Report'
--Use the where clause above to look for a specific report
ORDER BY NAME

The result from the script above will look something like this:

Next Steps

• Next time you need to determine report parameters, consider the script from this tip as an option to drill into that information.
• This tip also sheds some light into where the report metadata is stored and how to work with the data.  As you face similar challenges, consider this script as a baseline to begin to address your needs.
• Take a look at more Reporting Services and XML tips on MSSQLTips.

About the author

Sankar Reddy is a Database Engineer/DBA and SQL Server MVP. He has been working with SQL Server since 2003 in a variety of roles.

View all my tips