SSRS_subreports.sql

SSRS_subreports.sql

USE ReportServerDB

IF OBJECT_ID('tempdb..#ResultsTable') IS NOT NULL
	DROP TABLE #ResultsTable;

CREATE TABLE #ResultsTable (
	ReportID UNIQUEIDENTIFIER NOT NULL
	,ReportName NVARCHAR(425) COLLATE Latin1_General_CI_AS_KS_WS NOT NULL
	,ReportPath NVARCHAR(425) COLLATE Latin1_General_CI_AS_KS_WS NOT NULL
	,SubreportName NVARCHAR(256) COLLATE Latin1_General_CI_AS_KS_WS NOT NULL
	);

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition' AS a)
	,report_list
AS (
	SELECT [ReportID] = cat.[ItemID]
		,[ReportName] = cat.[Name]
		,[ReportPath] = cat.[Path]
		,[xmlColumn] = CAST(CAST(cat.[Content] AS VARBINARY(MAX)) AS XML)
	FROM [dbo].[Catalog] AS cat
	WHERE cat.[Content] IS NOT NULL
		AND cat.[Type] = 2
	)
INSERT INTO #ResultsTable
SELECT rpt.[ReportID]
	,rpt.[ReportName]
	,rpt.[ReportPath]
	,rpta.x.value('.', 'NVARCHAR(256)') AS SubreportName
FROM report_list AS rpt
CROSS APPLY xmlColumn.nodes('//a:ReportName') AS rpta(x);

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' AS a)
	,report_list
AS (
	SELECT [ReportID] = cat.[ItemID]
		,[ReportName] = cat.[Name]
		,[ReportPath] = cat.[Path]
		,[xmlColumn] = CAST(CAST(cat.[Content] AS VARBINARY(MAX)) AS XML)
	FROM [dbo].[Catalog] AS cat
	WHERE cat.[Content] IS NOT NULL
		AND cat.[Type] = 2
	)
INSERT INTO #ResultsTable
SELECT rpt.[ReportID]
	,rpt.[ReportName]
	,rpt.[ReportPath]
	,rpta.x.value('.', 'NVARCHAR(256)') AS SubreportName
FROM report_list AS rpt
CROSS APPLY xmlColumn.nodes('//a:ReportName') AS rpta(x);

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition' AS a)
	,report_list
AS (
	SELECT [ReportID] = cat.[ItemID]
		,[ReportName] = cat.[Name]
		,[ReportPath] = cat.[Path]
		,[xmlColumn] = CAST(CAST(cat.[Content] AS VARBINARY(MAX)) AS XML)
	FROM [dbo].[Catalog] AS cat
	WHERE cat.[Content] IS NOT NULL
		AND cat.[Type] = 2
	)
INSERT INTO #ResultsTable
SELECT rpt.[ReportID]
	,rpt.[ReportName]
	,rpt.[ReportPath]
	,rpta.x.value('.', 'NVARCHAR(256)') AS SubreportName
FROM report_list AS rpt
CROSS APPLY xmlColumn.nodes('//a:ReportName') AS rpta(x);

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition' AS a)
	,report_list
AS (
	SELECT [ReportID] = cat.[ItemID]
		,[ReportName] = cat.[Name]
		,[ReportPath] = cat.[Path]
		,[xmlColumn] = CAST(CAST(cat.[Content] AS VARBINARY(MAX)) AS XML)
	FROM [dbo].[Catalog] AS cat
	WHERE cat.[Content] IS NOT NULL
		AND cat.[Type] = 2
	)
INSERT INTO #ResultsTable
SELECT rpt.[ReportID]
	,rpt.[ReportName]
	,rpt.[ReportPath]
	,rpta.x.value('.', 'NVARCHAR(256)') AS SubreportName
FROM report_list AS rpt
CROSS APPLY xmlColumn.nodes('//a:ReportName') AS rpta(x);