Display Parameter Prompts and values in RDL

Question

User-1118764531 posted

I have a requirement to render a seperate page in RDL with parameter promts and value(s) passed. I see that internal ReportServer database's tables store these values in couple of fields but I cannot find if/where actual prompts used by RDL are stored.

Thanks in advance,

TW

Answer 1

User-734925760 posted

Hi,

According to your description, if you design the parameters in the report, the parameters will not be stored in any place after you run the sql query. So I suggest you create a Trigger for the tables which you will use for the report, as you select data from the table, we will run the trigger to insert the key value (parameters value) into a table. As you want to use the parameters, you can get them for the table.

There is a document about RDL with parameters, please refer to the link below:

http://www.codeproject.com/Articles/13733/Adding-input-parameters-to-the-SQL-Report

Hope it's useful for you.

Best Regards,

Michelle Ge

Answer 2

User-830595639 posted

Hi,

To get the parameter details use below query.

SELECT Name as ReportName
        ,ParameterName = Paravalue.value('Name[1]', 'VARCHAR(250)')
       ,ParameterType = Paravalue.value('Type[1]', 'VARCHAR(250)')
       ,ISNullable = Paravalue.value('Nullable[1]', 'VARCHAR(250)')
       ,ISAllowBlank = Paravalue.value('AllowBlank[1]', 'VARCHAR(250)')
       ,ISMultiValue = Paravalue.value('MultiValue[1]', 'VARCHAR(250)')
       ,ISUsedInQuery = Paravalue.value('UsedInQuery[1]', 'VARCHAR(250)')
       ,ParameterPrompt = Paravalue.value('Prompt[1]', 'VARCHAR(250)')
       ,DynamicPrompt = Paravalue.value('DynamicPrompt[1]', 'VARCHAR(250)')
       ,PromptUser = Paravalue.value('PromptUser[1]', 'VARCHAR(250)')
       ,State = Paravalue.value('State[1]', 'VARCHAR(250)')
 FROM (  
         SELECT top 1 C.Name,CONVERT(XML,C.Parameter) AS ParameterXML
           FROM  ReportServer.dbo.Catalog C
          WHERE  C.Content is not null
        AND  C.Type  = 2
        AND  C.Name  =  'your report name'
      ) a
CROSS APPLY ParameterXML.nodes('//Parameters/Parameter') p ( Paravalue )