Cached Shared Dataset: ExecutionTime?

Question

SQL Server Reporting Services (SSRS) 2008 R2

I've set up a shared dataset that takes a few minutes to execute. It is shared between three reports, and is the core data for these reports. (Some other datasets exist in the reports for parameter population.)

I've enabled caching on the dataset, and would like to add the dataset execution time to the reports' footers. I suppose I could add a column to the dataset with current time in SQL, but is there any SSRS function that will give this to me?

Thanks...
Jamie F

Answer 1

You can add something like this to the footer:

="Render Duration: " +
IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).TotalSeconds < 1, "0 seconds",
(
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)", ""))
)

So a basic render time, I know thats not exactly what your looking for but the only way i know of getting the data retrieval time is from the report execution table in your report server database.

Hope it helps.

Answer 2

I couldn't find a pure SSRS method to get this, so as mentioned in my original post, I added a field/column to the shared dataset query. It now begins with:

SELECT
GETDATE() AS DataSetExecutionTime,
...
...

Then in the footer of the report I have a

=First(Fields!DataSetExecutionTime.Value, "DataSetName")

Thanks for the help Daytona...

jf