Querying SSRS Reports, their Properties and Parameters

If you use SSRS’s WSDL interface, you have the ListChildren() and FindItems() methods to retrieve the uploaded report definition files (.rdl) in ReportService (2005), but I am not sure how to retrieve the parameter list.

Of course, there is the LoadReport() method in ReportExecutionService, the return value of which gives you access to the Parameter list, but that’s a different web service (URL and WSDL).

Originally I was looking for a solution that would involve only one of the two web services.

Back to square one: why not query the Report Service database directly? It contains a table [Catalog] which stores all reports (and more) along with their properties and parameters.

The Property and Parameter columns are defined as NTEXT, but their contents is XML, so we need to CAST() to XML because being able to access their contents.

The following TSQL example queries the reports for the language setting and the list of parameters:

with c as (
    select Name, Path, 
        CAST(Property as xml) as Property, 
        CAST(Parameter as xml) AS Parameter, 
        Type, CreationDate, ModifiedDate
    from Catalog
select Name, Path, Type, CreationDate, ModifiedDate,
        property.query('/Properties/Language/text()')) as Language,
    convert(nvarchar, p2.p.query('Name/text()')) as PName,
    convert(nvarchar, p2.p.query('Type/text()')) as PDatatype,
    convert(nvarchar, p2.p.query('Nullable/text()')) as PNullable,
    convert(nvarchar, p2.p.query('Prompt/text()')) as PPrompt
from c
outer apply Parameter.nodes('/Parameters/Parameter') as p2(p)
--where Type = 2


  • Use CROSS APPLY rather than OUTER APPLY to retrieve only report definitions with at least one parameter.
  • Filter for Type according to this enum definition.
  • Change NVARCHAR to NVARCHAR(length) if XML data is longer than supported by plain NVARCHAR.

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 )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: