Is there a good way in TSQL to:

1. List all SSRS reports containing sub-reports

2. List the SSRS sub reports?

thanks

Hi scott,

You can follow the below link

https://www.mssqltips.com/sqlservertip/2742/sql-server-reporting-services-2008-r2-report-server-database-helpful-queries/

and refer the first query and for getting all sub reports filter on ItemType as Linked Report.

Hi scott_m,

In Reporting Services, Catalog table within report server database stores the master data about all the objects related to SSRS. In your scenario, you can use the query below to get a list of reports that reference subreports, including the path of those subreports:

USE ReportServer

/* declare and populate temp table */
DECLARE @tab TABLE (Path NVARCHAR(MAX), xmlColumn XML)
INSERT @tab 
SELECT top 1500 Path, CONVERT(XML, CONVERT(VARBINARY(MAX), Content)) FROM Catalog WHERE Type=2;

/* find subreports in RS 2000-based main reports */
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition' as rdl)
SELECT Path as ReportPath, 
n.x.value('(//rdl:ReportName)[1]', 'nvarchar(256)') as ReferencedReport
FROM @tab
CROSS APPLY xmlColumn.nodes('//rdl:Subreport') n(x);

/* find subreports in RS 2005-based main reports */
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition' as rdl)
SELECT Path as ReportPath, 
n.x.value('(//rdl:ReportName)[1]', 'nvarchar(256)') as ReferencedReport
FROM @tab
CROSS APPLY xmlColumn.nodes('//rdl:Subreport') n(x);

/* find subreports in RS 2008-based main reports */
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' as rdl)
SELECT Path as ReportPath, 
n.x.value('(//rdl:ReportName)[1]', 'nvarchar(256)') as ReferencedReport
FROM @tab
CROSS APPLY xmlColumn.nodes('//rdl:Subreport') n(x); 

Reference:
Subreport Usage
Find the Report Definition Schema Version (SSRS)

If you have any question, please feel free to ask.

Best regards,
Qiuyun Yu

If you have any feedback on our support, please click her