D J Horton Consulting Ltd

Personal reference to useful tips and tricks for SQL Server, SQL Server Reporting Services and Crystal Reports.


My alternative to scraps of paper lying about.








Tuesday, 6 October 2009

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

--Find all the reports, their parameters and 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

taken from Sankar Reddy
http://www.mssqltips.com/tip.asp?tip=1839

No comments: