List all the SSRS Report Subscription and Job linked with subscriptions.

SSRS Reports provide automation using Subscriptions. Specially Data Driven subscriptions provide more flexibility in automation.

As soon as a SSRS Report Subscription is created a SQL Agent Job is created in SSRS Report Database Server, which execute on schedule and make sure subscriptions are executed. Job names are UNIQUE IDENTIFIERs and not so friendly. To associate SSRS Report to Subscriptions to SQL Agent Job, following query can be used. And later we can update Job like enable/disable from backend OR get SQL query for subscription execution and use it in ad-hoc reequipment and do many more backend stuff.

Use the query and update it if needed for you. Let me know in comment if you need more help with this topic.

SELECT sj.[name] AS [JobName]
	,c.[Name] AS [ReportName]
	,C.Path AS ReportPath
	,Su.Description AS SubscriptionName
	,su.LastStatus
	,su.LastRunTime
	,rs.SUBSCRIPTIONID
	,sj.JOB_ID
	,c.ComponentID
	,CASE 
		WHEN su.DataSettings IS NULL
			AND EventType = 'TimedSubscription'
			THEN 'Standard'
		WHEN su.DataSettings IS NOT NULL
			AND EventType = 'TimedSubscription'
			THEN 'Data Driven'
		END SubscriptionType
FROM msdb..sysjobs AS sj
LEFT JOIN ReportServer..ReportSchedule AS rs ON sj.[name] = CAST(rs.ScheduleID AS NVARCHAR(128))
LEFT JOIN ReportServer..Subscriptions AS su ON rs.SubscriptionID = su.SubscriptionID
LEFT JOIN ReportServer..[Catalog] c ON su.Report_OID = c.ItemID
WHERE c.[Name] = 'DAILYETLEXECUTIONREPORT'
ORDER BY [ReportName]

Query helps you:

1. Find the Job name associated with each SSRS Report Subscription
2. Find Last Status of subscription execution
3. Find type of Subscription – Data Driven or Standard
4. Using Job information we can do many more things.