August 14, 2014 at 12:19 pm
Hi
Not sure if I am explaining this correctly.. but...
The web interface for SSRS which we call Report Store has gotten a bit out of hand...
I would like to write an SP to list all the reports that have been deployed, their name, last use (if possible) and path
Any info, tables etc.. would be great
Thanks
Joe
August 14, 2014 at 5:31 pm
http://stackoverflow.com/questions/1179082/ssrs-tracking-report-usage was interesting and probably will help you.
It mentions that once you determine the reports that nobody uses that you can move them to unclutter your RS instance.
August 15, 2014 at 10:33 am
The solution pointed to by Piet is a good starting point. I'd modify it to do something more along these lines:
SELECT
Catalog.Type,
Catalog.Name,
Catalog.Path,
MAX(ExecutionLog.TimeStart) AS lastUsed
FROM
dbo.Catalog
LEFT JOIN dbo.ExecutionLog
ON Catalog.ItemID = ExecutionLog.ReportID
WHERE
Catalog.Type = 2
GROUP BY
Catalog.Type,
Catalog.Name,
Catalog.Path
The LEFT JOIN ensures that you will get all the reports currently with a NULL in the lastUsed column if there is no longer a row in the Execution Log because the last use has aged out of the execution log.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply