(888) 685-3101 ext. 2

Problem: You’re an admin in SQL Server Reporting Services (SSRS). You want to see everybody’s subscriptions on all reports. Yet you can only see your own.

Solution: You can do this easily. You just can’t do it in Report Manager. But remember that Report Manager is just a front end to a SQL Server database called ReportServer. There you will find the subscriptions you seek. You can query the catalog, subscriptions and users tables, along with a few others if you want scheduling and status information.

I picked up the query below from HerbD at this msdn thread. You can run it as a query in Management Studio or create a report with it — that way you can look at it in Report Manager if you want to. But my guess is that this tends to be an ad hoc need, so I’d just run it in SSMS.

USE ReportServer
SELECT c.Name
, c.Type
, c.Description
, u.UserName AS CreatedBy
, c.CreationDate
, c.ModifiedDate
, s.Description AS Subscription
, s.DeliveryExtension AS SubscriptionDelivery
, d.Name AS DataSource
, s.LastStatus
, s.LastRunTime
, s.Parameters
, sch.StartDate AS ScheduleStarted
, sch.LastRunTime AS LastSubRun
, sch.NextRunTime
, c.Path
FROM Catalog c
INNER JOIN
Subscriptions s ON c.ItemID = s.Report_OID
INNER JOIN
DataSource d ON c.ItemID = d.ItemID
LEFT OUTER JOIN
Users u ON u.UserID = c.CreatedByID
LEFT OUTER JOIN
ReportSchedule rs ON c.ItemID = rs.ReportID
LEFT OUTER JOIN
Schedule sch ON rs.ScheduleID = sch.ScheduleID
WHERE (c.Type = 2)
ORDER BY c.Name
Share This