-- Written by Steve Stedman http://SteveStedman.com SELECT c.Name, c.[Path], COUNT(*) AS TimesRun, MAX(l.TimeStart) AS [LastRun], ( SELECT SUBSTRING( ( SELECT CAST(', ' AS VARCHAR(MAX))+CAST(c1.Name AS VARCHAR(MAX)) FROM [ReportServer].[dbo].[Catalog] AS c INNER JOIN [ReportServer].[dbo].[DataSource] AS d ON c.ItemID = d.ItemID INNER JOIN [ReportServer].[dbo].[Catalog] c1 ON d.Link = c1.ItemID WHERE c.Type = 2 AND c.ItemId = l.ReportId FOR XML PATH('') ), 3, 10000000) AS list ) AS DataSources, ( SELECT SUBSTRING( ( SELECT CAST(', ' AS VARCHAR(MAX))+CAST(REPLACE(t.UserName, 'DOMAIN_NAME\', '') AS VARCHAR(MAX)) FROM ( SELECT TOP 100000 l2.UserName+'('+CAST(COUNT(*) AS VARCHAR(100))+')' AS UserName FROM [ReportServer].[dbo].[ExecutionLog](NOLOCK) AS l2 WHERE l2.ReportID = l.ReportId GROUP BY l2.UserName ORDER BY COUNT(*) DESC ) AS t FOR XML PATH('') ), 3, 10000000) ) AS UsedBy FROM [ReportServer].[dbo].[ExecutionLog](NOLOCK) AS l INNER JOIN [ReportServer].[dbo].[Catalog](NOLOCK) AS c ON l.ReportID = C.ItemID WHERE c.Type = 2 -- Only show reports 1=folder, 2=Report, 3=Resource, 4=Linked Report, 5=Data Source GROUP BY l.ReportId, c.Name, c.[Path];
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter