SSRS:如何查找哪些报表具有子报表?

Question

我有很多SQL Server Reporting Services(SSRS(报告(*.rdl(。我想知道这些报表中的哪些正在使用子报表。我该怎么做？查看更简单的方法，而不是打开每个报表并确定是否正在使用子报表。

谢谢

Answer 1

我认为这应该为您提供所需的(感谢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%'

Answer 2

下面的查询将返回具有子报表的已部署报表的列表。下面是Microsoft参考和用于引用旧版本的 SSRS 的链接。看起来唯一的区别是更改 CTE 部分中XMLNAMESPACESSSRS 的版本。

查询以返回所有子报表

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);