How to get the list of parameters used in Reports

SELECT a.name as ReportName,
Name = Paravalue.value(‘Name[1]’, ‘VARCHAR(250)’)
,Type = Paravalue.value(‘Type[1]’, ‘VARCHAR(250)’)
,Nullable = Paravalue.value(‘Nullable[1]’, ‘VARCHAR(250)’)
,AllowBlank = Paravalue.value(‘AllowBlank[1]’, ‘VARCHAR(250)’)
,MultiValue = Paravalue.value(‘MultiValue[1]’, ‘VARCHAR(250)’)
,UsedInQuery = Paravalue.value(‘UsedInQuery[1]’, ‘VARCHAR(250)’)
,Prompt = 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 C.Name,CONVERT(XML,C.Parameter) AS ParameterXML
FROM ReportServer_SQL2008_Prod.dbo.Catalog C
WHERE C.Content is not null
AND C.Type = 2
–AND C.Name = ‘%/Dashboard/KnowledgeBase%’
) a
CROSS APPLY ParameterXML.nodes(‘//Parameters/Parameter’) p ( Paravalue )

Advertisements

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s