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,
    convert(nvarchar, 
        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

Notes:

• 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.