Hello Everyone,

We have 1000s of SSRS reports hosted on the SQL 2008 R2. All reports are supposed to have all parameters "hidden". Random reports have been reported to expose the parameter when developers accidently deploy to test servers. Is there a way to identify the reports and the parameter property (Hidden, Visible, Internal) from the Report server Database?

I checked executionlog2 but can not find the parameter property.

Appreciate your time and thoughts.

Thanks in advance.

Hi Raj_MSBI,

According to your description, you want to query the report server database to find out the visibility of parameters, right?

Within the Report Server database, the table dbo.Catalog stores all reports along with their properties and parameters. The content within the Parameter column are XML, so we need to CAST() to XML because being able to access their contents using the query like below:

--Find all the reports, and thier parameters and thier default values
;WITH XMLNAMESPACES (
DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/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

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

If you have any question, please feel free to ask.

Best regards,
Qiu