Get SSRS Report Parameters through the Database - Web Design With .Net

Here is a quick snippet for all those SSRS developers.

use ReportServer
go

declare @ReportPath varchar(200)
set @ReportPath = &#039;/Sample Reports/Allied (NA) - 1 Filter Image&#039;

SELECT
  ROW_NUMBER() OVER (ORDER BY tmp.Name),
  ParameterName = Params.p.value(&#039;Name[1]&#039;,&#039;varchar(255)&#039;),
  ParameterLabel = Params.p.value(&#039;Prompt[1]&#039;,&#039;varchar(255)&#039;),
  DefaultValue = Params.p.query(&#039;DefaultValues/Value&#039;),
  ParameterXml = Params.p.query(&#039;.&#039;),
  ReportName = tmp.Name,
  ReportPath = tmp.Path
FROM
  (SELECT Name, Path, ReportParams = CONVERT(XML,Parameter) FROM dbo.Catalog WHERE Path = @ReportPath) tmp
  CROSS APPLY ReportParams.nodes(&#039;(Parameters/Parameter)&#039;) AS Params(p)
WHERE NOT Params.p.value(&#039;Name[1]&#039;,&#039;varchar(255)&#039;) IN (&#039;ReportPath&#039;,&#039;ReportNumber&#039;)

Til next time ...