How to Display the Elapsed Processing Time of a Report in SSRS
The amount of time it takes to process a report can be useful information for performance auditing. Although there isn’t a built-in field in SQL Server Reporting Services that holds the elapsed processing time, it’s easy to create this functionality using the ExecutionTime built-in field and the Now VB.Net function in SSRS.
For instructor-led SQL Server Training see our complete course schedule.
The ExecutionTime is the moment when report processing starts. When used in the expression of a textbox, the Now function returns the time when the report is rendered. Rendering comes right after report processing. This means we can easily calculate the processing time simply by subtracting Now from ExecutionTime.
Function Implementation
One way to implement the logic is to put it into a custom function and then call the function from the expression of a textbox. This is clean, the code is easy to read and so it’s also easy to maintain. The downside is that you have more than one component to the solution; so, if you want to copy this functionality into other reports, you’ll have to copy and paste both the function and the textbox. My preference would be for this approach because “clean and easy to maintain” usually leads to smoother running programs!
If you’ve never created custom code in SSRS before, read my article on Using Custom Code in SSRS to see how it’s done. Then create the following function that will both calculate the elapsed processing time and return it as a string in the format mm:ss.
1 2 3 4 5 6 7 8 | Function ProcessingTime(StartTime As Date, EndTime as Date) As String Dim ElapsedMins As Integer = DateDiff(DateInterval.Minute, StartTime, EndTime) mod 60 Dim ElapsedSecs As Integer = DateDiff(DateInterval.Second, StartTime, EndTime) mod 60 Return ElapsedMins.ToString() & ":" & Right("0" & ElapsedSecs.ToString(), 2) End Function |
Single Expression Implementation
Another way to implement the logic is to use a single expression in a textbox (right-click the textbox and select Expression…) that does everything. This is a bit messier, the expression has a whole bunch of things piled into it making it harder to read and maintain, but all you have to do to put it into another report is copy just that one textbox containing the following expression, which also displays the results in the format mm:ss.
1 2 | =DateDiff(DateInterval.Minute, Globals!ExecutionTime, Now()) mod 60 & ":" & Right("0" & DateDiff(DateInterval.Second, Globals!ExecutionTime, Now()) mod 60, 2) |
Enjoy!
Peter Avila
SQL Server Instructor – Interface Technical Training
Phoenix, AZ
An Overview of Office 365 – Administration Portal and Admin Center
This is part 1 of our 5-part Office 365 free training course. In this Office 365 training video, instructor Spike Xavier introduces some of the most popular services found in Microsoft Office 365 including the Admin Portal and Admin Center. For instructor-led Office 365 training classes, see our course schedule: Spike Xavier SharePoint Instructor – … Continue reading An Overview of Office 365 – Administration Portal and Admin Center
Creating Users and Managing Passwords in Microsoft Office 365
In this Office 365 training video, instructor Spike Xavier demonstrates how to create users and manage passwords in Office 365. For instructor-led Office 365 training classes, see our course schedulle: Spike Xavier SharePoint Instructor – Interface Technical Training Phoenix, AZ 20347: Enabling and Managing Office 365
JavaScript for C# Developers – September 24, 2014
Is JavaScript worth taking the time to learn if I’m a server-side .NET developer? How much of C# carries over to JavaScript? In this recorded video from Dan Wahlin’s webinar on September 24,2014, Dan answers these questions and more while also discussing similarities between the languages, key differences, and the future of JavaScript (ES6). If … Continue reading JavaScript for C# Developers – September 24, 2014
Write a Comment
See what people are saying...