Query all SSRS Reports, shared and embeded Datasources, and locations

As the SSRS reports details are available in ReportServer DB, sometime that's a good idea to take a look into reports' detail such as data sources which in some point may help to make a better decision to keep them embedded or utilize a shared one.

By customizing the folllowing script easily more information can be queried.

Use ReportServer GO ;WITH SSRS_Rprt_DtaSrc AS ( SELECT Catalog.Name AS [Report Name] , Catalog.path AS [Report Location] , DataSource.Name AS [Data Source Name] , DataSource.Link AS Link FROM [Catalog] INNER JOIN DataSource ON Catalog.ItemID = DataSource.ItemID ) SELECT [Report Name] , [Report Location] , [Data Source Name] , Ctlg.Name AS [Data Source Item] FROM SSRS_Rprt_DtaSrc INNER JOIN [Catalog] AS Ctlg ON SSRS_Rprt_DtaSrc.Link = Ctlg.[ItemID] ORDER BY [Report Location]

Query all SSRS Reports, shared and embeded Datasources, and locations