Script to determine SSRS parameters, path and default values

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.