--------------------------------------------
--	Retrieve Report Definitions (rdl)
--	from SSRS system Catalog.
--------------------------------------------
IF (OBJECT_ID(N'tempdb..#ReportDefn') IS NOT NULL)
	DROP TABLE #ReportDefn ;

SELECT
	cat.*
	cat.[ItemID]
	,[RDLReportName]		= cat.[Name]
	,cat.[Path]
	,c.[Type]
	,[rdlText]			= CONVERT([varchar](MAX), CONVERT(VARBINARY(MAX), cat.[Content]))
	,[RDLDoc]			= CONVERT([xml], CONVERT(VARBINARY(MAX), cat.[Content]))
	,[HasSubreport]		= 0
INTO	#ReportDefn
FROM   Reportserver.dbo.Catalog cat WITH (NOLOCK)
WHERE
	(cat.[Type] = 2) ;


--SELECT * FROM #ReportDefn


--	Set indicator for reports that contain subreports
--	and convert their rdl contents to XML data type.
UPDATE	#ReportDefn
SET
	[HasSubreport]	= 1
WHERE
	(PATINDEX('%SUBREPORT%', [rdlText]) > 0) ;

/*
SELECT	*
FROM	#ReportDefn
WHERE
	([HasSubreport] = 1)
*/

--	Process only reports that contain subreports.
DELETE
FROM	#ReportDefn
WHERE
	([HasSubreport] = 0) ;



--	Report Datasources.
--; WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition','http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition' AS RD)
SELECT
	[ItemID]
	,[RDLReportName]
	,[DataSourceName]		= datsrc.p.value('@Name','VARCHAR(255)') 
	,[DataSourceReference]	= datsrc.p.value('(*:DataSourceReference/text())[1]', 'nvarchar(255)')
	,[SecurityType]			= datsrc.p.value('(*:SecurityType/text())[1]', 'nvarchar(255)')
	,[DataSourceID]			= datsrc.p.value('(*:DataSourceID/text())[1]', 'nvarchar(255)')
FROM	#ReportDefn rptdef
CROSS APPLY rptdef.[RDLDoc].nodes('/*:Report/*:DataSources/*:DataSource') AS datsrc (p)
ORDER BY
	[ItemID] ;


--	Report Datasets.
--; WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition','http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition' AS RD)
SELECT
	[ItemID]
	,[RDLReportName]
	,[DataSetName]		= datset.p.value('@Name','VARCHAR(255)')
	,[DataSourceName]	= qry.p.value('(*:DataSourceName/text())[1]', 'nvarchar(128)')
	,[CommandType]		= ISNULL(datset.p.value('(*:CommandType/text())[1]', 'nvarchar(128)'), 'T-SQL')
	,[CommandText]		= qry.p.value('(*:CommandText/text())[1]', 'nvarchar(max)') 
FROM	#ReportDefn rptdef
CROSS APPLY rptdef.[RDLDoc].nodes('/*:Report/*:DataSets/*:DataSet') AS datset (p)
CROSS APPLY datset.p.nodes('*:Query') AS qry (p)
ORDER BY
	[ItemID] ;


--	Dataset Query Parameters.
--; WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition','http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition' AS RD)
SELECT
	[ItemID]
	,[RDLReportName]
	,[DataSetName]			= datset.p.value('@Name','VARCHAR(255)')
	,[DataSourceName]		= qry.p.value('(*:DataSourceName/text())[1]', 'nvarchar(255)')
	,[QueryParameterName]	= qparam.p.value('@Name','VARCHAR(255)')
	,[Value]				= qparam.p.value('(*:Value/text())[1]', 'nvarchar(255)')
FROM	#ReportDefn rptdef
CROSS APPLY rptdef.[RDLDoc].nodes('/*:Report/*:DataSets/*:DataSet') AS datset (p)
CROSS APPLY datset.p.nodes('*:Query') AS qry (p)
CROSS APPLY qry.p.nodes('*:QueryParameters/*:QueryParameter') AS qparam (p)
ORDER BY
	[ItemID] ;


--	Dataset Fields.
--; WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition','http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition' AS RD)
SELECT
	[ItemID]
	,[RDLReportName]
	,[DataSetName]			= datset.p.value('@Name','VARCHAR(255)')
	,[DataSourceName]		= qry.p.value('(*:DataSourceName/text())[1]', 'nvarchar(255)')
	,[FieldName]			= fld.p.value('@Name','VARCHAR(255)')
	,[DataField]			= fld.p.value('(*:DataField/text())[1]', 'nvarchar(255)')
	,[TypeName]				= fld.p.value('(*:TypeName/text())[1]', 'nvarchar(255)')
FROM	#ReportDefn rptdef
CROSS APPLY rptdef.[RDLDoc].nodes('/*:Report/*:DataSets/*:DataSet') AS datset (p)
CROSS APPLY datset.p.nodes('*:Query') AS qry (p)
CROSS APPLY datset.p.nodes('*:Fields/*:Field') AS fld (p)
ORDER BY
	[ItemID] ;


--	Report Parameters.
--; WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition','http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition' AS RD)
SELECT
	[ItemID]
	,[RDLReportName]
	,[ReportParameterName]			= rptparam.p.value('@Name','VARCHAR(255)')
	,[DataType]						= rptparam.p.value('(*:DataType/text())[1]', 'nvarchar(255)')
	,[Prompt]						= rptparam.p.value('(*:Prompt/text())[1]', 'nvarchar(255)')
	,[Hidden]						= rptparam.p.value('(*:Hidden/text())[1]', 'nvarchar(255)')
	,[Value]						= defval.p.value('(*:Value/text())[1]', 'nvarchar(255)')
	,[DataSetName]					= CAST(NULL AS [nvarchar](255))
	,[ValueField]					= CAST(NULL AS [nvarchar](255))
FROM	#ReportDefn rptdef
CROSS APPLY rptdef.[RDLDoc].nodes('/*:Report/*:ReportParameters/*:ReportParameter') AS rptparam (p)
CROSS APPLY rptparam.p.nodes('*:DefaultValue/*:Values') AS defval (p)

UNION

SELECT
	[ItemID]
	,[RDLReportName]
	,[ReportParameterName]			= rptparam.p.value('@Name','VARCHAR(255)')
	,[DataType]						= rptparam.p.value('(*:DataType/text())[1]', 'nvarchar(255)')
	,[Prompt]						= rptparam.p.value('(*:Prompt/text())[1]', 'nvarchar(255)')
	,[Hidden]						= rptparam.p.value('(*:Hidden/text())[1]', 'nvarchar(255)')
	,[Value]						= defval.p.value('(*:Values/*:Value/text())[1]', 'nvarchar(255)')
	,[DataSetName]					= defref.p.value('(*:DataSetName/text())[1]', 'nvarchar(255)')
	,[ValueField]					= defref.p.value('(*:ValueField/text())[1]', 'nvarchar(255)')
FROM	#ReportDefn rptdef
CROSS APPLY rptdef.[RDLDoc].nodes('/*:Report/*:ReportParameters/*:ReportParameter') AS rptparam (p)
CROSS APPLY rptparam.p.nodes('*:DefaultValue') AS defval (p)
CROSS APPLY defval.p.nodes('*:DataSetReference') AS defref (p)
ORDER BY
	[ItemID] ;


--	Report Parameter Valid Values.
--; WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition','http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition' AS RD)
SELECT
	[ItemID]
	,[RDLReportName]
	,[ReportParameterName]			= rptparam.p.value('@Name','VARCHAR(255)')
	,[DataType]						= rptparam.p.value('(*:DataType/text())[1]', 'nvarchar(255)')
	,[Prompt]						= rptparam.p.value('(*:Prompt/text())[1]', 'nvarchar(255)')
	,[Hidden]						= rptparam.p.value('(*:Hidden/text())[1]', 'nvarchar(255)')
	,[Value]						= defval.p.value('(*:Value/text())[1]', 'nvarchar(255)')
	,[DataSetName]					= CAST(NULL AS [nvarchar](255))
	,[ValueField]					= CAST(NULL AS [nvarchar](255))
	,[LabelField]					= CAST(NULL AS [nvarchar](255))
FROM	#ReportDefn rptdef
CROSS APPLY rptdef.[RDLDoc].nodes('/*:Report/*:ReportParameters/*:ReportParameter') AS rptparam (p)
CROSS APPLY rptparam.p.nodes('*:ValidValues/*:Values') AS defval (p)

UNION

SELECT
	[ItemID]
	,[RDLReportName]
	,[ReportParameterName]			= rptparam.p.value('@Name','VARCHAR(255)')
	,[DataType]						= rptparam.p.value('(*:DataType/text())[1]', 'nvarchar(255)')
	,[Prompt]						= rptparam.p.value('(*:Prompt/text())[1]', 'nvarchar(255)')
	,[Hidden]						= rptparam.p.value('(*:Hidden/text())[1]', 'nvarchar(255)')
	,[Value]						= defval.p.value('(*:Values/*:Value/text())[1]', 'nvarchar(255)')
	,[DataSetName]					= defref.p.value('(*:DataSetName/text())[1]', 'nvarchar(255)')
	,[ValueField]					= defref.p.value('(*:ValueField/text())[1]', 'nvarchar(255)')
	,[LabelField]					= defref.p.value('(*:LabelField/text())[1]', 'nvarchar(255)')
FROM	#ReportDefn rptdef
CROSS APPLY rptdef.[RDLDoc].nodes('/*:Report/*:ReportParameters/*:ReportParameter') AS rptparam (p)
CROSS APPLY rptparam.p.nodes('*:ValidValues') AS defval (p)
CROSS APPLY defval.p.nodes('*:DataSetReference') AS defref (p)
ORDER BY
	[ItemID] ;


--	Subreports.
--; WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition','http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition' AS RD)
SELECT
	[ItemID]
	,[RDLReportName]
	,[Subreport]			= subrpt.p.value('@Name','VARCHAR(255)')
	,[ReportName]		= subrpt.p.value('(*:ReportName/text())[1]', 'nvarchar(255)')
	,[Top]				= subrpt.p.value('(*:Top/text())[1]', 'nvarchar(255)')
	,[Left]				= subrpt.p.value('(*:Left/text())[1]', 'nvarchar(255)')
	,[Height]			= subrpt.p.value('(*:Height/text())[1]', 'nvarchar(255)')
	,[Width]			= subrpt.p.value('(*:Width/text())[1]', 'nvarchar(255)')
FROM	#ReportDefn rptdef
CROSS APPLY rptdef.[RDLDoc].nodes('//*:Subreport') AS subrpt (p)
ORDER BY
	[ItemID] ;


--	Subreport Parameters.
--; WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition','http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition' AS RD)
SELECT
	[ItemID]
	,[RDLReportName]
	,[Subreport]			= subrpt.p.value('@Name','VARCHAR(255)')
	,[ReportName]		= subrpt.p.value('(*:ReportName/text())[1]', 'nvarchar(255)')
	,[ParameterName]	= parm.p.value('@Name','VARCHAR(255)')
	,[Value]			= parm.p.value('(*:Value/text())[1]', 'nvarchar(255)')
FROM	#ReportDefn rptdef
CROSS APPLY rptdef.[RDLDoc].nodes('//*:Subreport') AS subrpt (p)CROSS APPLY subrpt.p.nodes('*:Parameters/*:Parameter') AS parm (p)
ORDER BY
	[ItemID] ;
