Actually, I just realized that the URL stuff is old, and working for only SSRS 2005. Here is a new version that includes direct URL links in the SSRS 2008R2 format.
I also added some case statements that translates some of the scheduling numbers into day/month names.
Glad this worked for you, happy I could help!
use ReportServer
GO
; WITH ReportEmailsInfo AS
(
SELECT
S.[SubscriptionID], CONVERT(XML, N'<Root>' + CONVERT(NVARCHAR(MAX), S.[ExtensionSettings]) + N'</Root>') AS X
FROM
ReportServer.dbo.[Subscriptions] AS S WITH (NOLOCK)
--WHERE
-- S.[DeliveryExtension] = 'Report Server Email' --only get e-mail subscriptions, not other delivery types
)
, CTE_DataMinusURLs AS
(
SELECT
REI.SubscriptionID
, S.Description AS SubscriptionName
, CASE WHEN S.DataSettings IS NULL
THEN 'False'
ELSE 'True' END AS DataDriven
, S.DataSettings AS DataDrivenReportSettings
, RS.ReportID
, C.Name AS [ReportName]
, S.LastStatus
--, C.SubType
, CL.NAME AS LinkedParentName
, S.DeliveryExtension
, ReI.X.value('(/Root/ParameterValues/ParameterValue[Name="Subject"]/Value)[1]', 'NVARCHAR(MAX)') AS 'Email_Subject'
, ReI.X.value('(/Root/ParameterValues/ParameterValue[Name="TO"]/Value)[1]', 'NVARCHAR(MAX)') AS 'Email_TO'
, ReI.X.value('(/Root/ParameterValues/ParameterValue[Name="CC"]/Value)[1]', 'NVARCHAR(MAX)') AS 'Email_CC'
, ReI.X.value('(/Root/ParameterValues/ParameterValue[Name="BCC"]/Value)[1]', 'NVARCHAR(MAX)') AS 'Email_BCC'
, ReI.X.value('(/Root/ParameterValues/ParameterValue[Name="ReplyTo"]/Value)[1]', 'NVARCHAR(MAX)') AS 'Email_ReplyTo'
, ReI.X.value('(/Root/ParameterValues/ParameterValue[Name="IncludeReport"]/Value)[1]', 'NVARCHAR(MAX)') AS 'Email_IncludeReport'
, ReI.X.value('(/Root/ParameterValues/ParameterValue[Name="IncludeLink"]/Value)[1]', 'NVARCHAR(MAX)') AS 'Email_IncludeLink'
, ReI.X.value('(/Root/ParameterValues/ParameterValue[Name="Priority"]/Value)[1]', 'NVARCHAR(MAX)') AS 'Email_Priority'
, ReI.X.value('(/Root/ParameterValues/ParameterValue[Name="Comment"]/Value)[1]', 'NVARCHAR(MAX)') AS 'Email_Comment'
, C.Path AS [ReportPath]
, U1.UserName AS [ReportOwner]
, U3.UserName AS [SubCreatedBy]
, U2.UserName AS [SubModifiedBy]
--, S.LastRunTime
-- , SC.ScheduleID
, case when sc.eventtype <>'SharedSchedule' then 'Report-specific' else sc.Name end as Schedule_Name
, SC.StartDate as Schedule_StartDate
, SC.NextRunTime as Schedule_NextRunTime
, SC.LastRunTime AS Schedule_LastRunTime
--, SC.LastRunStatus AS Schedule_LastRunStatus
, SC.EndDate as Schedule_EndDate
, CASE
when SC.recurrencetype = 1 then 'One-off'
when SC.recurrencetype = 2 then 'Hourly'
when SC.recurrencetype = 4 then 'Daily'
when SC.recurrencetype = 5 then 'Monthly'
when SC.recurrencetype = 6 then 'Month-Week' END AS Schedule_RecurrenceType
, SC.MinutesInterval
, SC.DaysInterval
, SC.WeeksInterval
, CASE
WHEN SC.DaysOfWeek = 1 THEN 'Sun'
WHEN SC.DaysOfWeek = 2 THEN 'Mon'
WHEN SC.DaysOfWeek = 4 THEN 'Tue'
WHEN SC.DaysOfWeek = 8 THEN 'Wed'
WHEN SC.DaysOfWeek = 16 THEN 'Thu'
WHEN SC.DaysOfWeek = 32 THEN 'Fri'
WHEN SC.DaysOfWeek = 64 THEN 'Sat'
WHEN SC.DaysOfWeek = 62 THEN 'Mon-Fri'
WHEN SC.DaysOfWeek = 10 THEN 'Mon AND Wed'
WHEN SC.DaysOfWeek = 127 THEN 'All Days'
END AS DaysOfWeek
, case when SC.DaysOfMonth = 1 then '1'
when SC.DaysOfMonth = 2 then '2'
when SC.DaysOfMonth = 4 then '3'
when SC.DaysOfMonth = 8 then '4'
when SC.DaysOfMonth = 16 then '5'
when SC.DaysOfMonth = 32 then '6'
when SC.DaysOfMonth = 64 then '7'
when SC.DaysOfMonth = 128 then '8'
when SC.DaysOfMonth = 256 then '9'
when SC.DaysOfMonth = 512 then '10'
when SC.DaysOfMonth = 1024 then '11'
when SC.DaysOfMonth = 2048 then '12'
when SC.DaysOfMonth = 4096 then '13'
when SC.DaysOfMonth = 8192 then '14'
when SC.DaysOfMonth = 16384 then '15'
when SC.DaysOfMonth = 32768 then '16'
when SC.DaysOfMonth = 65536 then '17'
when SC.DaysOfMonth = 131072 then '18'
when SC.DaysOfMonth = 262144 then '19'
when SC.DaysOfMonth = 524288 then '20'
when SC.DaysOfMonth = 1048576 then '21'
when SC.DaysOfMonth = 2097152 then '22'
when SC.DaysOfMonth = 4194304 then '23'
when SC.DaysOfMonth = 8388608 then '24'
when SC.DaysOfMonth = 16777216 then '25'
when SC.DaysOfMonth = 33554432 then '26'
when SC.DaysOfMonth = 67108864 then '27'
when SC.DaysOfMonth = 134217728 then '28'
when SC.DaysOfMonth = 268435456 then '29'
when SC.DaysOfMonth = 536870912 then '30'
when SC.DaysOfMonth = 1073741824 then '31'
when SC.DaysOfMonth = 8193 then '1st and 14th day'
end as DaysOfMonth
, case
when SC.Month = 4095 then 'Every Month'
when SC.Month = 585 then 'Jan,April,July,October (Quarterly)'
when SC.Month = 1 then 'Jan'
when SC.Month = 2 then 'Feb'
when SC.Month = 4 then 'Mar'
when SC.Month = 8 then 'Apr'
when SC.Month = 16 then 'May'
when SC.Month = 32 then 'Jun'
when SC.Month = 64 then 'Jul'
when SC.Month = 128 then 'Aug'
when SC.Month = 256 then 'Sep'
when SC.Month = 512 then 'Oct'
when SC.Month = 1024 then 'Nov'
when SC.Month = 2048 then 'Dec' end as Month
, SC.MonthlyWeek
, SC.State
, SC.EventType
--, S.MatchData
-- , CL.ItemID AS [ParentID]
-- this is the OLD form for SSRS 2005
-- , [URLDirect] = 'http://reports.tcsedsystem.edu/reports/pages/subscriptionproperties.aspx?itempath=' + C.[Path] + '&IsDataDriven=False&SubscriptionID=' + CAST(S.[SubscriptionID] AS VARCHAR(80)) -- URL direct to the subscription
-- , [URLSubTab] = 'http://reports.tcsedsystem.edu/Reports/Pages/Report.aspx?ItemPath=' + C.[Path] + '&SelectedTabId=SubscriptionsTab' -- URL to the "Subscriptions" tab on the report (which can be used to delete the subscription)
FROM
ReportEmailsInfo AS ReI
JOIN ReportServer.dbo.[Subscriptions] AS S ON REI.SubscriptionID=S.SubscriptionID --actual subscriptions
JOIN ReportServer.dbo.[Catalog] AS C ON S.[Report_OID] = C.[ItemID]
LEFT OUTER JOIN ReportServer.dbo.[ReportSchedule] AS RS ON S.[SubscriptionID] = RS.[SubscriptionID] --Subscription Schedules
LEFT OUTER JOIN ReportServer.dbo.[Schedule] AS SC ON RS.[ScheduleID] = SC.[ScheduleID] --Schedules
LEFT OUTER JOIN ReportServer.dbo.Catalog AS CL ON C.LinkSourceID = CL.ItemID
JOIN ReportServer.dbo.[Users] AS [U1] ON S.[OwnerID] = [U1].[UserID] -- Subscription Owner
JOIN ReportServer.dbo.[Users] AS [U2] ON S.[ModifiedByID] = [U2].[UserID] -- Subscription ModifiedBy
JOIN ReportServer.dbo.[Users] AS [U3] ON SC.[CreatedByID] = [U3].[UserID] -- Subscription CreatedBy
--LEFT OUTER JOIN ReportServer.dbo.[ReportSchedule] AS RS2 ON RS2.ReportID = C.LinkSourceID AND C.LinkSourceID IS NOT NULL
)
SELECT DMU.*
-- new URL link form for SSRS 2008R2
, [URLDirect] =
CASE WHEN DMU.DataDriven = 'False'
THEN
'http://chi-crmsql01/reports/pages/subscriptionproperties.aspx?itempath=' + DMU.[ReportPath] + '&IsDataDriven='+ DMU.DataDriven + '&SubscriptionID=' + CAST(DMU.[SubscriptionID] AS VARCHAR(80)) -- URL direct to the subscription
ELSE
'http://chi-crmsql01/reports/pages/subscriptionproperties.aspx?itempath=' + DMU.[ReportPath] + '&IsDataDriven='+ DMU.DataDriven + '&SubscriptionID=' + CAST(DMU.[SubscriptionID] AS VARCHAR(80)) +
'&RedirectUrl=' + 'http://chi-crmsql01/Reports/Pages/Report.aspx?ItemPath=' + DMU.[ReportPath] + '&SelectedTabId=PropertiesTab&SelectedSubTabId=SubscriptionsTab' -- URL direct to the subscription
END
, [URLSubTab] = 'http://chi-crmsql01/Reports/Pages/Report.aspx?ItemPath=' + DMU.[ReportPath] + '&SelectedTabId=PropertiesTab&SelectedSubTabId=SubscriptionsTab' -- URL to the "Subscriptions" tab on the report (which can be used to delete the subscription)
FROM CTE_DataMinusURLs AS DMU
ORDER BY LinkedParentName, ReportName