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

InstanceName	nvarchar(38)	NOT NULL	Name of the report server instance that handled the request. Usually YOURSERVERNAME\MSSQLSERVER
ReportID	uniqueidentifier	NULL	The 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).
UserName	nvarchar(260)	NULL	Windows authenticated username and domain of the person running the report (eg. MYDOMAIN\myusername)
RequestType	bit	NOT NULL	User or System. Can be 1 or 0. This was zero "0" when I would run a report as a user.
Format	nvarchar(26)	NULL	This is the rendering format. Mostly RPL if viewed in MS Internet Explorer.
Parameters	ntext	NULL	Parameters and the values they were submitted with.
TimeStart	datetime	NOT NULL	Time report started to run.
TimeEnd	datetime	NOT NULL	Time report finished running? Need to check what finished?
TimeDataRetrieval	int	NOT NULL	Milliseconds spent retrieving the data.
TimeProcessing	int	NOT NULL	Milliseconds spent processing the report.
TimeRendering	int	NOT NULL	Milliseconds spent rendering the report.
Source	int	NOT NULL	Source of the report exection (1=Live, 2=Cache, 3=Snapshot, 4=History)
Status	nvarchar(32)	NOT NULL	either rsSuccess or an error code; if multiple errors occur, only the first error is recorded
ByteCount	bigint	NOT NULL	Size of rendered reports in bytes.
RowCount	bigint	NOT NULL	Number 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]
      ,b.[Name]
      ,a.[UserName]
      ,a.[RequestType]
      ,a.[Format]
      ,a.[Parameters]
      ,a.[TimeStart]
      ,a.[TimeEnd]
      ,a.[TimeDataRetrieval]
      ,a.[TimeProcessing]
      ,a.[TimeRendering]
      ,a.[Source]
      ,a.[Status]
      ,a.[ByteCount]
      ,a.[RowCount]
  FROM [ReportServer].[dbo].[ExecutionLog] a
  INNER JOIN [ReportServer].[dbo].[Catalog] b
  ON a.ReportID = b.ItemID

The Top 5 Most Frequent

-- Top 5 Most Frequent:
SELECT TOP 5
	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
FROM
(
	SELECT 
		TimeStart, TimeEnd, Catalog.Name, TimeDataRetrieval, TimeProcessing, TimeRendering, ByteCount, [RowCount]
	FROM
	Catalog INNER JOIN ExecutionLog ON Catalog.ItemID = ExecutionLog.ReportID
	WHERE ExecutionLog.TimeStart BETWEEN @DateFrom AND @DateTo AND Type = 2
) AS RE
GROUP BY
	Name
ORDER BY
	COUNT(Name) DESC
	, Name

< Prev		Next >