Get a List of Report Subscriptions in SSRS

Clearinghouse Repository Polaris Repository
, ,
1

This query is a little different in that it’s not hitting your standard Polaris databases, but rather it’s pulling data from your SQL Server Reporting Services system (SSRS). I needed to get some info about all of the current report subscriptions and who was receiving which reports. There may be a better way to do it in SSRS but hey, I’m a SQL guy, so this was what worked well for me.

SELECT 
    c.Name AS ReportName,
    c.Path AS ReportPath,
    s.SubscriptionID,
    s.Description,
    s.LastStatus,
    s.LastRunTime,
    s.EventType,
    s.DeliveryExtension,
    s.ModifiedDate,
    st.ScheduleID,
    st.Name AS ScheduleName,
    st.StartDate,
    st.LastRunTime AS ScheduleLastRun,
    st.NextRunTime
FROM 
    ReportServer.dbo.Subscriptions s
INNER JOIN 
    ReportServer.dbo.Catalog c 
    ON (s.Report_OID = c.ItemID)
LEFT JOIN 
    ReportServer.dbo.ReportSchedule rs 
    ON (s.SubscriptionID = rs.SubscriptionID)
LEFT JOIN 
    ReportServer.dbo.Schedule st 
    ON (rs.ScheduleID = st.ScheduleID)
ORDER BY 
    c.Path, 
    s.ModifiedDate DESC;
1 Like