SSRS: How to find which reports have subreports?

Question

I have a lot of SQL Server Reporting Services (SSRS) reports (*.rdl). I want to know which of these reports are using subreports. How can I do that? Looking at an easier way instead of opening each report and figuring out if a subreport is being used.

Thanks

What kind of solution are you looking for? You can interrogate RDL files, or reports DB. The `ReportServer` DB has the table called `Catalog` and you should be able to link children to parents in there. — ajeh, Jun 21 '18 at 22:09
I dont want to open RDL files 1 by 1 to see if they have a sub report. How to query the `Catalog` table for parent and child? I tried using its `ItemID` and `ParentID` columns thinking that a report with a subreport will be different than a report without a subreport but thats not the case. — srh, Jun 21 '18 at 22:52
If my answer sorted you out, can you please click on the check mark beside the answer to toggle it from greyed out to filled in. :) — aduguid, Sep 01 '18 at 14:40

score 2 · Answer 1 · answered Jun 21 '18 at 23:16

I think this should provide you with what you need (With thanks to Bret Stateham ):

 --The first CTE gets the content as a varbinary(max)
 --as well as the other important columns for all reports,
 --data sources and shared datasets.
 WITH ItemContentBinaries AS
 (
   SELECT
      ItemID,Name,[Type]
     ,CASE Type
        WHEN 2 THEN 'Report'
        WHEN 5 THEN 'Data Source'
        WHEN 7 THEN 'Report Part'
        WHEN 8 THEN 'Shared Dataset'
        ELSE 'Other'
      END AS TypeDescription
     ,CONVERT(varbinary(max),Content) AS Content
   FROM ReportServer.dbo.Catalog
   WHERE Type IN (2,5,7,8)
 ),
 --The second CTE strips off the BOM if it exists...
 ItemContentNoBOM AS
 (
   SELECT
      ItemID,Name,[Type],TypeDescription
     ,CASE
        WHEN LEFT(Content,3) = 0xEFBBBF
          THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content)))
        ELSE
          Content
      END AS Content
   FROM ItemContentBinaries
 )
 --The outer query gets the content in its varbinary, varchar and xml representations...
 ,VarcharContent as
 (
 SELECT
    ItemID,Name,[Type],TypeDescription
   ,Content --varbinary
   ,CONVERT(varchar(max),Content) AS ContentVarchar --varchar
   ,CONVERT(xml,Content) AS ContentXML --xml
 FROM ItemContentNoBOM
 )
 SELECT * FROM VarcharContent where ContentVarchar like '%<subreport%'

score 2 · Answer 2 · edited Jun 20 '20 at 09:12

The following query below will return a list of deployed reports that have subreports. Here's the Microsoft reference and a link for referencing older versions of SSRS. It looks like the only difference is changing the version of SSRS in XMLNAMESPACES part of the CTE.

Query to return all subreports

WITH
XMLNAMESPACES 
( 
    'http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition' AS rdl
)
, 
report_list
AS
(
    SELECT 
          [ReportID] = cat.[ItemID]
        , [ReportName] = cat.[Name]
        , [ReportPath] = cat.[Path]
        , [xmlColumn] = CAST(CAST(cat.[Content] AS VARBINARY(MAX)) AS XML)
    FROM  
        [ReportServer].[dbo].[Catalog] AS cat
    WHERE 
        1=1
        AND cat.[Content] IS NOT NULL
        AND cat.[Type] = 2 
)
SELECT 
      rpt.[ReportID]
    , rpt.[ReportName]
    , rpt.[ReportPath]
    , [SubReportName] = srpt.x.value('(//rdl:ReportName)[1]', 'NVARCHAR(256)')
FROM 
    report_list AS rpt
    CROSS APPLY xmlColumn.nodes('//rdl:Subreport') AS srpt(x);

If you put `1=1` at the top of a `WHERE` condition, it enables you to freely change the rest of the conditions when debugging a query. The SQL query engine will end up ignoring the `1=1` so it should have no performance impact. [Reference](https://stackoverflow.com/q/242822/9059424) — aduguid, Feb 15 '19 at 02:40

SSRS: How to find which reports have subreports?

2 Answers2

Query to return all subreports