SSRS: Showing the correct execution time on a two page report?

Question

I'm just wondering how I can show the correct execution time on a report?

Until recently I had been using the following in the footer of my reports as a label expression:

="Execution Time: " +
IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).TotalSeconds < 1, "0 seconds", 
(
IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours & " hour(s), ", "") +
IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes & " minute(s), ", "") +
IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds & " second(s)", ""))
)

Unfortunately (as expected) whenever I browse to a second page of a report or export to PDF this value is re-calculating (so if I render the report in 1s, wait 30s and then export to pdf the execution time would display as 31s).

What is the best method for displaying the execution time of a report without using the above information?

Edit (answer as I can't post one with <100 reputation):

I actually took a different approach for this as I didn't notice any replies here until now. Perhaps not as good as the above answers but I would say a simple solution that could be implemented where the skill to do alternative solutions is unavailable:

As mentioned earlier, I had the following:

="Execution Time: " +
IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).TotalSeconds < 1, "0 seconds", 
(
IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours & " hour(s), ", "") +
IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes & " minute(s), ", "") +
IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds & " second(s)", ""))
)

The problem with this comes into play when you have a two page report or export a report to PDF. If you are to generate a report that has 5s execution time and then wait 30s to export to PDF then the execution time in the footer of the exported report is displayed as 35s. Likewise if you’re on the first page of a report and wait 30s to navigate to the second page. This is because the execution time is calculated on each render (new page or PDF export) using System.DateTime.Now.

To avoid this the time that the report is generated needs to be stored in a variable (I originally intended to store the entire string above however the execution time doesn’t appear to be available to a report variable expression as I’m assuming they’re initialized prior to this being finalized). To do this first go to Report Properties and add a new variable. For this example I’ve named the variable GroupExecutionTime

Add the following expression to the variable in order to initialize it with the current system time (which will be the execution time):

=System.DateTime.Now

After you’ve done this we then need to update the footer label to use the variable value instead of the current system time (report variables aren’t recalculated on each new page render or export where as footer label expressions are).

="Execution Time: " +
IIf(Variables!GroupExecutionTime.Value.Subtract(Globals!ExecutionTime).TotalSeconds < 1, "0 seconds", 
(
IIf(Variables!GroupExecutionTime.Value.Subtract(Globals!ExecutionTime).Hours > 0, Variables!GroupExecutionTime.Value.Subtract(Globals!ExecutionTime).Hours & " hour(s), ", "") +
IIf(Variables!GroupExecutionTime.Value.Subtract(Globals!ExecutionTime).Minutes > 0, Variables!GroupExecutionTime.Value.Subtract(Globals!ExecutionTime).Minutes & " minute(s), ", "") +
IIf(Variables!GroupExecutionTime.Value.Subtract(Globals!ExecutionTime).Seconds > 0, Variables!GroupExecutionTime.Value.Subtract(Globals!ExecutionTime).Seconds & " second(s)", ""))
)

Solution 1

I actually took a different approach for this as I didn't notice any replies here until now. Perhaps now as best as the above but I would say a simple solution that could be implemented where the skill to do alternative solutions is unavailable:

As mentioned earlier, I had the following:

="Execution Time: " +
IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).TotalSeconds < 1, "0 seconds", 
(
IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours & " hour(s), ", "") +
IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes & " minute(s), ", "") +
IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds & " second(s)", ""))
)

The problem with this comes into play when you have a two page report or export a report to PDF. If you are to generate a report that has 5s execution time and then wait 30s to export to PDF then the execution time in the footer of the exported report is displayed as 35s. Likewise if you’re on the first page of a report and wait 30s to navigate to the second page. This is because the execution time is calculated on each render (new page or PDF export) using System.DateTime.Now.

To avoid this the time that the report is generated needs to be stored in a variable (I originally intended to store the entire string above however the execution time doesn’t appear to be available to a report variable expression as I’m assuming they’re initialized prior to this being finalized). To do this first go to Report Properties and add a new variable. For this example I’ve named the variable GroupExecutionTime

Add the following expression to the variable in order to initialize it with the current system time (which will be the execution time):

=System.DateTime.Now

After you’ve done this we then need to update the footer label to use the variable value instead of the current system time (report variables aren’t recalculated on each new page render or export where as footer label expressions are).

="Execution Time: " +
IIf(Variables!GroupExecutionTime.Value.Subtract(Globals!ExecutionTime).TotalSeconds < 1, "0 seconds", 
(
IIf(Variables!GroupExecutionTime.Value.Subtract(Globals!ExecutionTime).Hours > 0, Variables!GroupExecutionTime.Value.Subtract(Globals!ExecutionTime).Hours & " hour(s), ", "") +
IIf(Variables!GroupExecutionTime.Value.Subtract(Globals!ExecutionTime).Minutes > 0, Variables!GroupExecutionTime.Value.Subtract(Globals!ExecutionTime).Minutes & " minute(s), ", "") +
IIf(Variables!GroupExecutionTime.Value.Subtract(Globals!ExecutionTime).Seconds > 0, Variables!GroupExecutionTime.Value.Subtract(Globals!ExecutionTime).Seconds & " second(s)", ""))
)

Solution 2

I'd build this using custom code:

Public Dim ExecText As String

Public Function ExecTime() As String
    If (ExecText = "") Then
        ExecText = // Calculate execution time here
    End If

    ExecTime = "Execution Time: " + ExecText
End Function

Then in your footer text box just put

=Code!ExecTime()

and your execution time will calculate just once for the first footer and after that will return the previously calculated result.

Solution 3

I'm slowly moving towards adding the execution time as a field to reports' datasets. This is particularly helpful when using cached datasets: the execution time of the report isn't really what the user wants; they want to know when the data was current.

SELECT
   GetDate() AS ExecTime,
   ...other SQL...
...

Comments

[Michael A]

Depends on your scenario - I'm in an office where I'm expected to display both sets of information

[Michael A]

This seems a lot worse then the approach I took? Am I missing something? This will also recalculate when you change pages or export to excel - so it doesn't really work

[Michael A]

Please see my edit - this will recalculate whenever you change pages, or PDF. I already answered this question in the original post (the edit)

[Nick.McDermaid]

A suggested enhancement: move all of that IIf code into your Report properties so it doesn't have to be repeated in each page.

[Michael A]

This is what the accepted answer (answered four years ago) is already doing.

[Glen]

I just had a title (textbox) at the top (i.e. no footer) and this approach worked for me.