BI Stuff
Find metadata regarding your SSRS Reports
Needed to find info regarding hundreds of SSRS reports. Here is a great place to start.
https://gallery.technet.microsoft.com/scriptcenter/42440a6b-c5b1-4acc-9632-d608d1c40a5c
This will help you find execution results, users, data sources etc....
Here is another link on this topic.
http://bretstateham.com/extracting-ssrs-report-rdl-xml-from-the-reportserver-database/
https://gallery.technet.microsoft.com/scriptcenter/42440a6b-c5b1-4acc-9632-d608d1c40a5c
This will help you find execution results, users, data sources etc....
Here is another link on this topic.
http://bretstateham.com/extracting-ssrs-report-rdl-xml-from-the-reportserver-database/
Find SubReports in RDL file
Needed to find SubReports and the parent report. You must have the proper XMLNAMESSPACES declared or it will not work. I am not great at querying XML so this is my best attempt. Would like to be able to drill down one more level to the parameters associated with the SubReport.
;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition')
Select
a.name,
a.path,
s_report.s.value('.','VARCHAR(250)') as subreport
from
(
select c.name,c.path, CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) as X
FROM Reportserver.dbo.Catalog C with(nolock)
where c.type = 2
) a
CROSS APPLY X.nodes('//Subreport/ReportName') s_report(s)
So you can ignore the previous query. It works but I found the a query that a developer friend gave me that he uses. Its awesome. Its to long to paste it all here. I have attached the file. Here is a brief snippet. I obviously need to get up to speed on my my XPATH.
-- 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] ;
;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition')
Select
a.name,
a.path,
s_report.s.value('.','VARCHAR(250)') as subreport
from
(
select c.name,c.path, CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) as X
FROM Reportserver.dbo.Catalog C with(nolock)
where c.type = 2
) a
CROSS APPLY X.nodes('//Subreport/ReportName') s_report(s)
So you can ignore the previous query. It works but I found the a query that a developer friend gave me that he uses. Its awesome. Its to long to paste it all here. I have attached the file. Here is a brief snippet. I obviously need to get up to speed on my my XPATH.
-- 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] ;
sql_server_parsing_of_ssrs_reports.sql | |
File Size: | 9 kb |
File Type: | sql |