Get SSRS Report Parameters through the Database

Here is a quick snippet for all those SSRS developers.

use ReportServer
go

declare @ReportPath varchar(200)
set @ReportPath = '/Sample Reports/Allied (NA) - 1 Filter Image'

SELECT
  ROW_NUMBER() OVER (ORDER BY tmp.Name),
  ParameterName = Params.p.value('Name[1]','varchar(255)'),
  ParameterLabel = Params.p.value('Prompt[1]','varchar(255)'),
  DefaultValue = Params.p.query('DefaultValues/Value'),
  ParameterXml = Params.p.query('.'),
  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('(Parameters/Parameter)') AS Params(p)
WHERE NOT Params.p.value('Name[1]','varchar(255)') IN ('ReportPath','ReportNumber')

Til next time …

zp8497586rq

Leave a Reply

Your email address will not be published. Required fields are marked *