The ReportServer Database

Amazing, I have just spent all morning on Microsoft websites to determine what number is the TimeDataRetrieval column displaying. Thank you I know it's an INT. There is just a serious lack of documentation as to what this database is and how it populates its data.

View: ExecutionLog
InstanceNamenvarchar(38)NOT NULLName of the report server instance that handled the request. Usually YOURSERVERNAME\MSSQLSERVER
ReportIDuniqueidentifierNULLThe ID of the report (looks like a hexadecimal SSID). It's the unique ID of the report but not unique in the table (can be referenced many times).
UserNamenvarchar(260)NULLWindows authenticated username and domain of the person running the report (eg. MYDOMAIN\myusername)
RequestTypebitNOT NULLUser or System. Can be 1 or 0. This was zero "0" when I would run a report as a user.
Formatnvarchar(26)NULLThis is the rendering format. Mostly RPL if viewed in MS Internet Explorer.
ParametersntextNULLParameters and the values they were submitted with.
TimeStartdatetimeNOT NULLTime report started to run.
TimeEnddatetimeNOT NULLTime report finished running? Need to check what finished?
TimeDataRetrievalintNOT NULLMilliseconds spent retrieving the data.
TimeProcessingintNOT NULLMilliseconds spent processing the report.
TimeRenderingintNOT NULLMilliseconds spent rendering the report.
SourceintNOT NULLSource of the report exection (1=Live, 2=Cache, 3=Snapshot, 4=History)
Statusnvarchar(32)NOT NULLeither rsSuccess or an error code; if multiple errors occur, only the first error is recorded
ByteCountbigintNOT NULLSize of rendered reports in bytes.
RowCountbigintNOT NULLNumber of rows returned from queries.

Conversions Needed:
  1. I want to convert a start and end date to a single time value (hours minutes seconds mseconds)
  2. I want to total 3 of the columns (which measure in milliseconds) and convert to a time value with milliseconds showing.
  3. Now why don't these match?
  4. Using in an aggregate, how do I get the milliseconds between start and end times instead of summing the remaining columns?
  5. Correction, sum will only give me the total time but what if a report has been run twice more often then another, then is it slower? Not necessarily.
  6. Like all measures in bytes, I would like a thousand separator, and lose the decimal ".00".
  7. I need the size of the report but the value has to be returned as part of the aggregate query.
  8. I want the SQL to return just numbers and let the MDX query add the thousand separator.

My answer for everything:
  1. CAST((TimeEnd - TimeStart) AS TIME)
  2. CAST(CONVERT(CHAR, DATEADD(millisecond, [TimeDataRetrieval]+[TimeProcessing]+[TimeRendering], '00:00:00'), 121) AS TIME)
  3. Unaccounted time.
  4. SUM(DATEDIFF(MILLISECOND, TimeStart, TimeEnd)) [TimeTakenSum]
  5. AVG(DATEDIFF(MILLISECOND, TimeStart, TimeEnd)) [TimeTakenAvg]
  6. LEFT(CAST(CONVERT(varchar, CAST(SUM(ByteCount) AS money), 1) AS varchar),CHARINDEX('.',CAST(CONVERT(varchar, CAST(SUM(ByteCount) AS money), 1) AS varchar))-1) AS [ByteCountSum]
  7. LEFT(CAST(CONVERT(varchar, CAST(AVG(ByteCount) AS money), 1) AS varchar),CHARINDEX('.',CAST(CONVERT(varchar, CAST(AVG(ByteCount) AS money), 1) AS varchar))-1) AS [ByteCountAvg]
  8. Use the data format: "$#,##0;($#,##0)"
Show me the Name!
A quick query to show the execution log with at least the report name for us human users:
SELECT a.[InstanceName]
  FROM [ReportServer].[dbo].[ExecutionLog] a
  INNER JOIN [ReportServer].[dbo].[Catalog] b
  ON a.ReportID = b.ItemID
  1.  SELECT a.[InstanceName] 
  2.        ,b.[Name] 
  3.        ,a.[UserName] 
  4.        ,a.[RequestType] 
  5.        ,a.[Format] 
  6.        ,a.[Parameters] 
  7.        ,a.[TimeStart] 
  8.        ,a.[TimeEnd] 
  9.        ,a.[TimeDataRetrieval] 
  10.        ,a.[TimeProcessing] 
  11.        ,a.[TimeRendering] 
  12.        ,a.[Source] 
  13.        ,a.[Status] 
  14.        ,a.[ByteCount] 
  15.        ,a.[RowCount] 
  16.    FROM [ReportServer].[dbo].[ExecutionLog] a 
  17.    INNER JOIN [ReportServer].[dbo].[Catalog] b 
  18.    ON a.ReportID = b.ItemID 

The Top 5 Most Frequent
-- Top 5 Most Frequent:
	COUNT(Name) AS ExecutionCount
	, Name
	, CAST(CONVERT(CHAR, DATEADD(millisecond, AVG(DATEDIFF(MILLISECOND, TimeStart, TimeEnd)), '00:00:00'), 121) AS TIME) [TimeTakenAvg]
	, AVG(ByteCount) AS [ByteCountAvg]
	, AVG([RowCount]) AS [RowCountAvg]
	, SUM(ByteCount) AS [ByteCountSum]
	, SUM([RowCount]) AS [RowCountSum]
	, SUM(TimeDataRetrieval) AS TimeDataRetrievalSum
	, SUM(TimeProcessing) AS TimeProcessingSum
	, SUM(TimeRendering) AS TimeRenderingSum
		TimeStart, TimeEnd, Catalog.Name, TimeDataRetrieval, TimeProcessing, TimeRendering, ByteCount, [RowCount]
	Catalog INNER JOIN ExecutionLog ON Catalog.ItemID = ExecutionLog.ReportID
	WHERE ExecutionLog.TimeStart BETWEEN @DateFrom AND @DateTo AND Type = 2
	, Name
  1.  -- Top 5 Most Frequent: 
  2.  SELECT TOP 5 
  3.      COUNT(Name) AS ExecutionCount 
  4.      , Name 
  5.      , CAST(CONVERT(CHAR, DATEADD(millisecond, AVG(DATEDIFF(MILLISECOND, TimeStart, TimeEnd)), '00:00:00'), 121) AS TIME) [TimeTakenAvg] 
  6.      , AVG(ByteCount) AS [ByteCountAvg] 
  7.      , AVG([RowCount]) AS [RowCountAvg] 
  8.      , SUM(ByteCount) AS [ByteCountSum] 
  9.      , SUM([RowCount]) AS [RowCountSum] 
  10.      , SUM(TimeDataRetrieval) AS TimeDataRetrievalSum 
  11.      , SUM(TimeProcessing) AS TimeProcessingSum 
  12.      , SUM(TimeRendering) AS TimeRenderingSum 
  13.  FROM 
  14.  ( 
  15.      SELECT 
  16.          TimeStart, TimeEnd, Catalog.Name, TimeDataRetrieval, TimeProcessing, TimeRendering, ByteCount, [RowCount] 
  17.      FROM 
  18.      Catalog INNER JOIN ExecutionLog ON Catalog.ItemID = ExecutionLog.ReportID 
  19.      WHERE ExecutionLog.TimeStart BETWEEN @DateFrom AND @DateTo AND Type = 2 
  20.  ) AS RE 
  21.  GROUP BY 
  22.      Name 
  23.  ORDER BY 
  24.      COUNT(Name) DESC 
  25.      , Name 
Category: Databases :: Article: 375

Credit where Credit is Due:

Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman

Related Articles

Joes Revolver Map


Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Donate to Joel Lipman via PayPal

Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2023 Joel Lipman .com. All Rights Reserved.