Query to extract the list of all reports from Reporting Services with the last execution details. Tested with SSRS 2019. SELECT [c].[ItemID] [ReportID], [c].[Path] [ReportPath], [c].[Name] [ReportName], [c].[Description], ‘http://report_browser_urlp-app.ud4d.com/Reports/report’ + REPLACE([c].[Path] , ‘ ‘, ‘%20’) [ReportLink], [c].[Property], [c].[Hidden] [IsHidden], [uc].[UserName] [CreatedBy], [c].[CreationDate], [um].[UserName] [ModifiedBy], [c].[ModifiedDate], [c].[MimeType], [c].[SnapshotLimit], [c].[ExecutionFlag], [c].[ExecutionTime],[c].[SubType], [c].[ContentSize],…
View report parameters details
30. 8. 2020
Script to view all parameters in all reports found in the Reporting Services reports catalog table. ;WITH [params] AS ( SELECT [r].[ReportPath], [r].[ReportName], [p].[Param].[value](‘Name[1]’, ‘nvarchar(500)’) AS [ParameterName], [p].[Param].[value](‘Type[1]’, ‘nvarchar(500)’) AS [Type], [p].[Param].[value](‘Nullable[1]’, ‘varchar(10)’) AS [Nullable], [p].[Param].[value](‘AllowBlank[1]’, ‘varchar(10)’) AS [AllowBlank], [p].[Param].[value](‘MultiValue[1]’, ‘varchar(10)’) AS [MultiValue], [p].[Param].[value](‘UsedInQuery[1]’, ‘varchar(10)’) AS [UsedInQuery], [p].[Param].[value](‘State[1]’, ‘varchar(100)’) AS…
Reports execution history
26. 11. 2019
Run this script against ReportServer database to obtain basic historical overview of reports execution. Remember that default log history retention is 60 days. SELECT [EL].[LogEntryId], [EL].[InstanceName], [C].[Path], [C].[Name] [ReportName], [EL].[UserName], CASE([EL].[ReportAction]) WHEN 1 THEN ‘Render’ WHEN 2 THEN ‘BookmarkNavigation’ WHEN 3 THEN ‘DocumentMapNavigation’ WHEN 4 THEN ‘DrillThrough’ WHEN 5 THEN…
Following query is handful in case of migrating reports to different Report Server or when shared data sources should be changed, renamed or deleted. Query takes some time to be executed because of XML parsing. WITH [XmlData] AS ( SELECT [ItemID], [Name], [Type], [Xml] = TRY_CONVERT(XML, CONVERT(VARBINARY(MAX), [Content])) FROM [dbo].[Catalog]…