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.



Others may find bits of it useful!








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: