Archive

Posts Tagged ‘SSRS’

SSRS: List Dataset associated with Report parameters

October 20, 2011 Leave a comment

;WITH
XMLNAMESPACES
(DEFAULT ‘http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition’
,’http://schemas.microsoft.com/sqlserver/reporting/reportdesigner’
AS rd)
,DEF AS
(SELECT RPT.ReportPath ,
ISNULL
((ISNULL(R.RptNode.value(‘(./DefaultValue/DataSetReference/DataSetName)[1]’, ‘nvarchar(425)’),R.RptNode.value(‘(./ValidValues/DataSetReference/DataSetName)[1]’, ‘nvarchar(425)’))),
R.RptNode.value(‘(./DefaultValue/Values/Value)[1]’, ‘nvarchar(425)’))
AS DataSetName
,R.RptNode.value(‘@Name[1]’, ‘nvarchar(425)’) AS ParameterName
FROM (SELECT RPT.Path AS ReportPath
,RPT.name AS ReportName
,CONVERT(xml, CONVERT(varbinary(max), RPT.content)) AS contentXML
FROM [ReportServer$SQLEXPRESS].[dbo].[Catalog] AS RPT
WHERE RPT.Type = 2 — 2 = Reports
) AS RPT
CROSS APPLY RPT.contentXML.nodes(‘/Report/ReportParameters/ReportParameter’) AS R(RptNode)
)
SELECT DEF.ReportPath
,DEF.ParameterName
,DEF.DataSetName
FROM DEF
— Optional filter:
–WHERE DEF.ReportPath LIKE ‘Report_PATH’
ORDER BY DEF.ReportPath
,DEF.ParameterName
,DEF.DataSetName

Advertisements
Categories: SSRS Tags: