Uncategorized

List of all reports with last execution statistics

Query to extract the list of all reports from Reporting Services with the last execution details. Tested with SSRS 2019. Transact-SQL 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], [me].[UserName] [LastExecUser], [me].[Parameters] [LastExecParameters], [me].[TimeStart] [LastExecTimeStart], [me].[TimeEnd] [LastExecTimeEnd], [me].[TimeDataRetrieval] [LastExecTimeDataRetrieval], [me].[TimeProcessing] [LastExecTimeProcessing], [me].[TimeRendering] [LastExecTimeRendering], [me].[TotalDuration] [LastExecTotalDuration] FROM [ReportServer].[dbo].[Catalog] [c] LEFT JOIN [ReportServer].[dbo].[Users] [uc] ON [c].[CreatedByID] = [uc].[UserID] LEFT JOIN [ReportServer].[dbo].[Users] [um] ON [c].[ModifiedByID] = [um].[UserID] LEFT JOIN (SELECT [el].[ReportID], [UserName], [el].[Parameters], [el].[TimeStart], [el].[TimeEnd], [el].[TimeDataRetrieval], [el].[TimeProcessing], [el].[TimeRendering], DATEDIFF(ms, [el].[TimeStart], [el].[TimeEnd]) [TotalDuration] FROM [ReportServer].[dbo].[ExecutionLogStorage] [el] WHERE [el].[LogEntryId] IN ( select MAX([el].[LogEntryId]) [LogEntryID] FROM [ReportServer].[dbo].[ExecutionLogStorage] [el] INNER JOIN [ReportServer].[dbo].[Catalog] [c] ON [c].[ItemID] = [el].[ReportID] GROUP BY [el].[ReportID]) ) [me] ON [me].[ReportID] = [c].[ItemID] WHERE [c].[Type] = 2 1234567891011121314151617181920212223242526272829 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], [me].[UserName] [LastExecUser], [me].[Parameters] [LastExecParameters], [me].[TimeStart] [LastExecTimeStart], [me].[TimeEnd] [LastExecTimeEnd], [me].[TimeDataRetrieval] [LastExecTimeDataRetrieval], [me].[TimeProcessing] [LastExecTimeProcessing], [me].[TimeRendering] [LastExecTimeRendering], [me].[TotalDuration] [LastExecTotalDuration]FROM [ReportServer].[dbo].[Catalog] [c] LEFT JOIN [ReportServer].[dbo].[Users] [uc] ON [c].[CreatedByID] = [uc].[UserID] LEFT JOIN [ReportServer].[dbo].[Users] [um] ON [c].[ModifiedByID] = [um].[UserID] LEFT JOIN (SELECT [el].[ReportID], [UserName], [el].[Parameters], [el].[TimeStart], [el].[TimeEnd],   [el].[TimeDataRetrieval], [el].[TimeProcessing], [el].[TimeRendering],   DATEDIFF(ms, [el].[TimeStart], [el].[TimeEnd]) [TotalDuration]    FROM [ReportServer].[dbo].[ExecutionLogStorage] [el]    WHERE [el].[LogEntryId] IN ( select MAX([el].[LogEntryId]) [LogEntryID] FROM [ReportServer].[dbo].[ExecutionLogStorage] [el] INNER JOIN [ReportServer].[dbo].[Catalog] [c] ON [c].[ItemID] = [el].[ReportID] GROUP BY [el].[ReportID]) ) [me] ON [me].[ReportID] = [c].[ItemID]WHERE [c].[Type] = 2  
Uncategorized

View report parameters details

Script to view all parameters in all reports found in the Reporting Services reports catalog table. Transact-SQL ;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 [State], [p].[Param].[value]('PromptUser[1]', 'varchar(10)') AS [PromptUser], [p].[Param].[value]('DynamicPrompt[1]', 'varchar(10)') AS [DynamicPrompt], [p].[Param].[value]('Prompt[1]', 'varchar(100)') AS [Prompt], [p].[Param].[value]('DynamicValidValues[1]', 'varchar(10)') AS [DynamicValidValues], [p].[Param].[value]('DynamicDefaultValue[1]', 'varchar(10)') AS [DynamicDefaultValue], [p].[Param].[exist]('Dependencies[1]/Dependency') AS [HasDependencies], [p].[Param].[exist]('DefaultValues[1]/Value') AS [HasDefaultValues], [p].[Param].[exist]('Values[1]/Value') AS [HasValues], [p].[Param].[value]('Dependencies[1]', 'nvarchar(4000)') AS [Dependencies], [p].[Param].[value]('DefaultValues[1]', 'nvarchar(4000)') AS [DefaultValues], [p].[Param].[value]('Values[1]', 'nvarchar(4000)') AS [Values] FROM (SELECT [c].[Path] [ReportPath], [c].[Name] [ReportName], CONVERT(XML, [c].[Parameter]) AS [ParamsXML] FROM [dbo].[Catalog] [c] WHERE [c].[Type] = 2 ) [r] CROSS APPLY [r].[ParamsXML].[nodes]('/Parameters/Parameter') AS [p]([Param]) ) SELECT [ReportPath], [ReportName], [ParameterName], [Type], [Nullable], [AllowBlank], [MultiValue], [UsedInQuery], [State], [PromptUser], [DynamicPrompt], [Prompt], [DynamicValidValues], [DynamicDefaultValue], [HasDependencies], [HasDefaultValues], [HasValues], [Dependencies], [DefaultValues], [Values] FROM [params] ORDER BY [params].[ReportPath] 12345678910111213141516171819202122232425262728293031323334353637383940 ;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 [State], [p].[Param].[value]('PromptUser[1]', 'varchar(10)') AS [PromptUser], [p].[Param].[value]('DynamicPrompt[1]', 'varchar(10)') AS [DynamicPrompt], [p].[Param].[value]('Prompt[1]', 'varchar(100)') AS [Prompt], [p].[Param].[value]('DynamicValidValues[1]', 'varchar(10)') AS [DynamicValidValues], [p].[Param].[value]('DynamicDefaultValue[1]', 'varchar(10)') AS [DynamicDefaultValue], [p].[Param].[exist]('Dependencies[1]/Dependency') AS [HasDependencies], [p].[Param].[exist]('DefaultValues[1]/Value') AS [HasDefaultValues], [p].[Param].[exist]('Values[1]/Value') AS [HasValues], [p].[Param].[value]('Dependencies[1]', 'nvarchar(4000)') AS [Dependencies], [p].[Param].[value]('DefaultValues[1]', 'nvarchar(4000)') AS [DefaultValues], [p].[Param].[value]('Values[1]', 'nvarchar(4000)') AS [Values]     FROM (SELECT [c].[Path] [ReportPath], [c].[Name] [ReportName], CONVERT(XML, [c].[Parameter]) AS [ParamsXML]    FROM [dbo].[Catalog] [c]           WHERE [c].[Type] = 2         ) [r]      CROSS APPLY [r].[ParamsXML].[nodes]('/Parameters/Parameter') AS [p]([Param])     ) SELECT [ReportPath], [ReportName], [ParameterName], [Type], [Nullable], [AllowBlank], [MultiValue], [UsedInQuery], [State], [PromptUser], [DynamicPrompt], [Prompt], [DynamicValidValues], [DynamicDefaultValue], [HasDependencies], [HasDefaultValues], [HasValues], [Dependencies], [DefaultValues], [Values] FROM [params] ORDER BY [params].[ReportPath]
Uncategorized

Reports execution history

Run this script against ReportServer database to obtain basic historical overview of reports execution. Remember that default log history retention is 60 days. Transact-SQL 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 'FindString' WHEN 6 THEN 'GetDocumentMap' WHEN 7 THEN 'Toggle' WHEN 8 THEN 'Sort' WHEN 9 THEN 'Execute' ELSE 'Unknown' END [ItemAction], [EL].[TimeStart], YEAR([EL].[TimeStart]) [Start_Year], MONTH([EL].[TimeStart]) [Start_Month], DATENAME(MONTH,[EL].[TimeStart]) [Start_Month_Name], DATENAME(DW,[EL].[TimeStart]) [Start_Day_Of_Week], DATEPART(WEEKDAY,[EL].[TimeStart]) [Start_Day_Number_of_Week], [EL].[TimeEnd], [EL].[TimeDataRetrieval], [EL].[TimeProcessing], [EL].[TimeRendering], CASE([EL].[Source]) WHEN 1 THEN 'Live' WHEN 2 THEN 'Cache' WHEN 3 THEN 'Snapshot' WHEN 4 THEN 'History' WHEN 5 THEN 'AdHoc' WHEN 6 THEN 'Session' WHEN 7 THEN 'Rdce' ELSE 'Unknown' END [Source], [EL].[Status], [EL].[ByteCount],[EL].[RowCount], [EL].[Parameters], [C].[Description], [CBY].[UserName] [CreatedBy], [C].[CreationDate], [MBY].[UserName] [ModifiedBy], [C].[ModifiedDate] FROM [dbo].[ExecutionLogStorage] [EL] LEFT JOIN [dbo].[Catalog] [C] ON [EL].[ReportID] = [C].[ItemID] LEFT JOIN [dbo].[Users] [CBY] ON [C].[CreatedByID] = [CBY].[UserID] LEFT JOIN [dbo].[Users] [MBY] ON [C].[ModifiedByID] = [MBY].[UserID] WHERE [EL].[Source] = 1 AND [C].[Type] = 2 ORDER BY c.[Path], c.[Name] GO 1234567891011121314151617181920212223242526272829303132333435363738394041424344 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 'FindString' WHEN 6 THEN 'GetDocumentMap' WHEN 7 THEN 'Toggle' WHEN 8 THEN 'Sort' WHEN 9 THEN 'Execute' ELSE 'Unknown' END [ItemAction], [EL].[TimeStart], YEAR([EL].[TimeStart]) [Start_Year], MONTH([EL].[TimeStart]) [Start_Month], DATENAME(MONTH,[EL].[TimeStart]) [Start_Month_Name], DATENAME(DW,[EL].[TimeStart]) [Start_Day_Of_Week], DATEPART(WEEKDAY,[EL].[TimeStart]) [Start_Day_Number_of_Week], [EL].[TimeEnd], [EL].[TimeDataRetrieval], [EL].[TimeProcessing], [EL].[TimeRendering], CASE([EL].[Source]) WHEN 1 THEN 'Live' WHEN 2 THEN 'Cache' WHEN 3 THEN 'Snapshot' WHEN 4 THEN 'History' WHEN 5 THEN 'AdHoc' WHEN 6 THEN 'Session' WHEN 7 THEN 'Rdce' ELSE 'Unknown' END [Source], [EL].[Status], [EL].[ByteCount],[EL].[RowCount], [EL].[Parameters], [C].[Description], [CBY].[UserName] [CreatedBy], [C].[CreationDate], [MBY].[UserName] [ModifiedBy], [C].[ModifiedDate]FROM [dbo].[ExecutionLogStorage] [EL] LEFT JOIN [dbo].[Catalog] [C] ON [EL].[ReportID] = [C].[ItemID] LEFT JOIN [dbo].[Users] [CBY] ON [C].[CreatedByID] = [CBY].[UserID] LEFT JOIN [dbo].[Users] [MBY] ON [C].[ModifiedByID] = [MBY].[UserID]WHERE [EL].[Source] = 1 AND [C].[Type] = 2ORDER BY c.[Path], c.[Name]GO  
Uncategorized

List all data sources with connection string detail

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. Transact-SQL WITH [XmlData] AS ( SELECT [ItemID], [Name], [Type], [Xml] = TRY_CONVERT(XML, CONVERT(VARBINARY(MAX), [Content])) FROM [dbo].[Catalog] ), [SharedDS] AS ( SELECT [ds].[ItemID], [c].[Name] [SharedDS], [ds].[Name] [LocalDS], [Xml].[value]('(/*:DataSourceDefinition/*:Extension)[1]', 'NVARCHAR(260)') [DataProvider], [Xml].[value]('(/*:DataSourceDefinition/*:ConnectString)[1]', 'NVARCHAR(MAX)') [ConnString] FROM [dbo].[DataSource] [ds] INNER JOIN [XmlData] [c] ON [ds].[Link] = [c].[ItemID] ), [DS] AS ( SELECT [r].[ItemID], [sds].[SharedDS], [r].[LocalDS], CAST ((CASE WHEN [sds].[SharedDS] IS NOT NULL THEN 1 ELSE 0 END) AS BIT) [IsSharedDS], ISNULL([r].[DataProvider], [sds].[DataProvider]) [DataProvider], ISNULL([r].[ConnString], [sds].[ConnString]) [ConnString] FROM ( SELECT [c].[ItemID], [DataSourceXml].[value]('@Name', 'NVARCHAR(260)') [LocalDS], [DataSourceXml].[value]('(*:ConnectionProperties/*:DataProvider)[1]', 'NVARCHAR(260)') [DataProvider], [DataSourceXml].[value]('(*:ConnectionProperties/*:ConnectString)[1]', 'NVARCHAR(MAX)') [ConnString] FROM [XmlData] [c] CROSS APPLY [Xml].[nodes]('/*:Report/*:DataSources/*:DataSource') [DataSource]([DataSourceXml]) WHERE [c].[Type] = 2 ) [r] LEFT JOIN [SharedDS] [sds] ON [r].[ItemID] = [sds].[ItemID] AND [r].[LocalDS] = [sds].[LocalDS] ), [Main] AS ( SELECT [ItemID], [QueryXml].[value]('@Name', 'NVARCHAR(256)') [DataSet], [QueryXml].[value]('(*:Query/*:DataSourceName)[1]', 'NVARCHAR(260)') [DataSource], [QueryXml].[value]('(*:Query/*:CommandType)[1]', 'NVARCHAR(15)') [CommandType], [QueryXml].[value]('(*:Query/*:CommandText)[1]', 'NVARCHAR(MAX)') [CommandText] FROM [XmlData] CROSS APPLY [Xml].[nodes]('/*:Report/*:DataSets/*:DataSet') [QueryData]([QueryXml]) ) SELECT [c].[Path] [ReportPath], [c].[Name] [ReportName], [ds].[LocalDS], [ds].[SharedDS], [ds].[IsSharedDS], [ds].[DataProvider], [ds].[ConnString], [m].[DataSet], ISNULL([m].[CommandType], 'Text') [CommandType], [m].[CommandText] FROM [Main] [m] INNER JOIN [DS] [ds] ON [ds].[ItemID] = [m].[ItemID] AND [m].[DataSource] = [ds].[LocalDS] INNER JOIN [dbo].[Catalog] [c] ON [ds].[ItemID] = [c].[ItemID] ORDER BY [ReportPath], [ReportName] GO 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849 WITH [XmlData] AS (    SELECT [ItemID], [Name], [Type],         [Xml] = TRY_CONVERT(XML, CONVERT(VARBINARY(MAX), [Content]))    FROM [dbo].[Catalog]),[SharedDS] AS (    SELECT [ds].[ItemID], [c].[Name] [SharedDS], [ds].[Name] [LocalDS], [Xml].[value]('(/*:DataSourceDefinition/*:Extension)[1]', 'NVARCHAR(260)') [DataProvider],        [Xml].[value]('(/*:DataSourceDefinition/*:ConnectString)[1]', 'NVARCHAR(MAX)') [ConnString]    FROM [dbo].[DataSource] [ds]        INNER JOIN [XmlData] [c] ON [ds].[Link] = [c].[ItemID]),[DS] AS (    SELECT        [r].[ItemID], [sds].[SharedDS], [r].[LocalDS],         CAST ((CASE WHEN [sds].[SharedDS] IS NOT NULL THEN 1 ELSE 0 END) AS BIT) [IsSharedDS], ISNULL([r].[DataProvider], [sds].[DataProvider]) [DataProvider], ISNULL([r].[ConnString], [sds].[ConnString]) [ConnString]    FROM ( SELECT [c].[ItemID],               [DataSourceXml].[value]('@Name', 'NVARCHAR(260)') [LocalDS],               [DataSourceXml].[value]('(*:ConnectionProperties/*:DataProvider)[1]', 'NVARCHAR(260)') [DataProvider],               [DataSourceXml].[value]('(*:ConnectionProperties/*:ConnectString)[1]', 'NVARCHAR(MAX)') [ConnString]            FROM [XmlData] [c]                CROSS APPLY [Xml].[nodes]('/*:Report/*:DataSources/*:DataSource') [DataSource]([DataSourceXml])            WHERE [c].[Type] = 2        ) [r]        LEFT JOIN [SharedDS] [sds] ON [r].[ItemID] = [sds].[ItemID] AND [r].[LocalDS] = [sds].[LocalDS]),[Main] AS (    SELECT [ItemID],        [QueryXml].[value]('@Name', 'NVARCHAR(256)') [DataSet],        [QueryXml].[value]('(*:Query/*:DataSourceName)[1]', 'NVARCHAR(260)') [DataSource],        [QueryXml].[value]('(*:Query/*:CommandType)[1]', 'NVARCHAR(15)') [CommandType],        [QueryXml].[value]('(*:Query/*:CommandText)[1]', 'NVARCHAR(MAX)') [CommandText]    FROM [XmlData]        CROSS APPLY [Xml].[nodes]('/*:Report/*:DataSets/*:DataSet') [QueryData]([QueryXml]))SELECT     [c].[Path] [ReportPath], [c].[Name] [ReportName], [ds].[LocalDS], [ds].[SharedDS], [ds].[IsSharedDS],    [ds].[DataProvider], [ds].[ConnString], [m].[DataSet], ISNULL([m].[CommandType], 'Text') [CommandType], [m].[CommandText]FROM [Main] [m]    INNER JOIN [DS] [ds] ON [ds].[ItemID] = [m].[ItemID] AND [m].[DataSource] = [ds].[LocalDS]    INNER JOIN [dbo].[Catalog] [c] ON [ds].[ItemID] = [c].[ItemID]ORDER BY [ReportPath], [ReportName]GO