Advanced Search

SSRS Report Parmeters and description

/ Published in: SQL
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. DECLARE @reportname VARCHAR(500) = 'New PRD by Agency', @parm xml;
  2.  
  3. SELECT @parm = CONVERT(xml,parameter) --as p, c.Name, c.Description,Path
  4. FROM dbo.Catalog c
  5. WHERE Name = @reportname
  6.  
  7. SELECT c.Name AS ReportName,Description,Path,
  8. ParmValues.Parameter.value('(./Name)[1]','Varchar(100)')AS ParamName,
  9. ParmValues.Parameter.value( '(./Type)[1]','varchar(100)') AS DataType,
  10. ParmValues.Parameter.value( '(./Nullable)[1]','bit') AS NULLABLE,
  11. ParmValues.Parameter.value( '(./AllowBlank)[1]','bit') AS AllowBlank,
  12. ParmValues.Parameter.value( '(./MultiValue)[1]','bit') AS MultiValue,
  13. ParmValues.Parameter.value( '(./UsedInQuery)[1]','bit') AS UsedInQuery,
  14. ParmValues.Parameter.value( '(./Prompt)[1]','varchar(100)') AS Prompt,
  15. ParmValues.Parameter.value( '(./PromptUser)[1]','bit') AS PromptUser,
  16. ParmValues.Parameter.value( '(./DynamicDefaultValue)[1]','bit') AS DynamicDefaultValue
  17. FROM dbo.Catalog c
  18. JOIN @parm.nodes('//Parameter') AS ParmValues(Parameter) ON c.Name = @reportname

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.