Managing Reports...

I am working on a project where i had to clean up unused,not needed reports from our portal. As part of the project the unused subscriptions also had to be removed. The portal has several folders with a quite a number of reports. In order to make the task easier, I utlised the tables available in the ReportServer Database, which hold data regarding the reports and its schedules. Some of the important tables to be aware of are 1. Catalog, 2. Subscriptions, 3. ExecutionLog, 4. ReportSchedule, 5. Schedule. One can write queries involving these tables to get a list of reports which have subscriptions, the reports which have been accessed by users and when the reports were pulled. I essentially made a SSRS report out of the queries and used the report to manage by cleanup operation. I would like to thank Devin Knight for his blog entry on the same subject, http://www.bidn.com/articles/reporting-services/110/monitoring-reporting-services-subscriptions.

Here are some of the queries which I used: The following query gives the Report name, the last time it was run, the status of the run and the user who accessed the report. The LastRun Time and Last Status comes from the Subscriptions table.

select Catalog.Name,UserName,MAX(Subscriptions.LastRunTime),
LastStatus from Catalog
inner join ExecutionLog
ON ReportID = ItemID
inner join Subscriptions
On Report_OID=ReportID
WHERE Catalog.Path like '/Your Path/%' -- This is the report path on the report server.
GROUP BY Catalog.Name,UserName,LastStatus
GO

One can also use the following query to find out which user is getting subscriptions (there could be a better way..)
select Name,UserName,MAX(LastRunTime) As LastSentDate,LastStatus from Catalog

inner join ExecutionLog
ON ReportID = ItemID
inner join Subscriptions
On Report_OID=ReportID
WHERE LastStatus LIKE '%FirstName.LastName%' --One can enter a email address or a pattern here.
 
The tables in the ReportServer database are helpful while managing and adminitering the Reporting Server.