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

About Dattatrey Sindol (Datta)

Datta is a Microsoft BI Enthusiast, passionate developer, and a blogger. View Full Profile

Posted on March 21, 2010, in Code Snippets, SQL Server, SSRS, SSRS Snippets, SSRS Tips, Tips 'N' Tricks and tagged , , , , . Bookmark the permalink. 6 Comments.

  1. The problem with this solution is that the Rendertime is allways updatet after change to next Page or printing, because of NOW().

  2. Hi There,Yes you are right. Everytime you refresh the report or start navigating from one page to another, it will lead to change in the time.Best Regards,Datta

  3. All I needed is this. I don’t think anyone could explain it as simple and logical as you did. Thank you very much!

  1. Pingback: Handling Large Number of Categories in a SSRS Pie Chart « Datta's Ramblings on Business Intelligence 'N' Life

  2. Pingback: SQL Server Reporting Services Tips and Tricks to Improve the End-User Experience | Datta's Ramblings on Business Intelligence 'N' Life

  3. Pingback: SSRS – SQL Server Reporting Services Report Manager Tips and Tricks | Datta's Ramblings on Business Intelligence 'N' Life

What are your thoughts?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: