Home > SSRS > SSRS: List Dataset associated with Report parameters

SSRS: List Dataset associated with Report parameters

;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:
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: