Subreport Usage

  • Article

In a previous post I showed how to quickly find reports that use custom report items (CRIs).  The same idea can be applied to subreports as well.  As an administrator of a report server, you may access the catalog database directly. Keep in mind that any queries you execute directly against the report server’s catalog database may affect the performance of the overall system. Make sure that you never take any locks on objects in that database, as this is not supported and interferes with the function of the report server.

The query below runs against the current version of the report server catalog database, and may not necessarily work for future RS versions.  It uses XQuery and checks for all three different RDL namespaces since the catalog database stores a copy of the original report in exactly the format it was originally uploaded, so that (maybe several years) later, you can still retrieve exactly the report you published as a 2003/10 or 2005/01 report, even if it is internally on-the-fly upgraded to the latest schema version of the report server it is running on.

Use the following query 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 ('https://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 ('https://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 ('https://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);