CODEWITHSUNDEEP
reporting-servicesssrs-2012
John81
John81

Reputation: 4084

Can I get and display the Cache Last Run Date in a SSRS Report?

Is there a way to get the last run date from the cache refresh page for a SSRS report and have that displayed on the report? I'd like the user to know when the data was last refreshed.

enter image description here

Upvotes: 1

Views: 2338

Answers (2)

liver.larson
liver.larson

Reputation: 164

Sorry to contribute to old thread, but wasn't finding a lot of hits on this question, so figured I'd add this:

If you include a calculated field in your source query, it is evaluated when the query is run and records along with the rest of your data. For example:

select field1 , getdate() as QueryDateTime from SQLServerTable

and you can then use that value as min/max/first in your report (by definition is the same on every record).

This has to be done by the server dishing up the data, not as a calculated field in SSRS, because those are evaluated at run time, same as now() expression, or global execution time variable.

One downside of course is that you're recording that data and storing it, then having to retrieve a bunch of redundant data when pulling it, so it's not really efficient from a purist I/O perspective. I suspect the cost of one column of a single date value is not too much to worry about in most cases.

Upvotes: 0

stubaker
stubaker

Reputation: 1958

You can query the ReportServer database directly to accomplish this:

SELECT MAX(els.TimeEnd) AS LastCacheRefresh
FROM dbo.ExecutionLogStorage AS els
    INNER JOIN dbo.Catalog AS cat ON els.ReportID = cat.ItemID
WHERE els.RequestType = 2 --Refresh Cache
    AND els.Status = 'rsSuccess'
    AND cat.Name = 'MyReport'

Also FYI, Microsoft does not support querying the ReportServer database directly, which means fields/schema could change in later versions of SSRS.

Upvotes: 4

Related Questions