SSRS: Showing the correct execution time on a two page report?
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)", ""))
)
Answer
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)", ""))
)