Daily Archives: March 21, 2010

How to Display Report Execution Time in SQL Server Reporting Services (SSRS)

Often in Reporting Applications, faster rendering of reports is very essential. Especially in case of Operational Reporting Systems with large number of concurrent users, Reporting Rendering SLA is defined during the requirement phase of the project and is a very critical component of the Requirements / Reporting Application.
 
In these kind of solutions its good to display the total time a report takes for rendering from the moment the View Report button is clicked in the report manager. This feature can be used as a means of communicating the exact report rendering time to the users and also to identify the reports / scenarios which are exceeding / missing the rendering SLA.
 
Now let us see how to display the execution time in an SSRS report.
 
Since SSRS uses functions based on .Net framework we can use the following expression to get the report rendering time in seconds:
 
System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds
 
Similary to display the time in terms of minutes and hours we can just replace Seconds in the above expression with Minutes and Hours respectively.
 
Though execution time in terms of Hours is rarely/never used.
 
To make the display more user friendly or more presentable we can use the following expression:
 
=”Execution Time: ” +
CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours) + ” hour(s)” + ” , ” +
CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes) + ” minute(s)” + “, ” +
CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds) + ” second(s)”
 
Here is a sample report displaying the execution time in terms of Hours, Minutes and Seconds as shown below.
 
 
As shown in the above screenshot the report takes 1 Min 3 Seconds or 63 Seconds to render.
 
Now let us verify how accurate this number is by querying the SSRS report tables which hold the history/log of a report execution.
 
I ran the following query in the ReportServer database:
 
SELECT TOP 1
ReportID
, TimeStart
, TimeEnd
, TimeDataRetrieval
, TimeProcessing
, TimeRendering
, TimeDataRetrieval + TimeProcessing + TimeRendering AS TotalRenderingTime
FROM dbo.ExecutionLog
WHERE ReportID = ’90ABF38F-E280-4B75-B807-343AFB5FE696′
ORDER BY TimeStart DESC

In the above query, replace ’90ABF38F-E280-4B75-B807-343AFB5FE696′ with your Report ID.
And here are the results of the above query:

From the above screenshot we can see that the total time is 63172 milliseconds or ~63 Seconds or 1 Min & 3 Seconds, which is same as the Execution Time displayed in the previous screenshot.

Please let me know your comments / opinions about this article by leaving a comment below.

Note: This demonstration is tested in SSRS 2008. The implementation should be almost the same for SSRS 2005 as well.

Advertisements