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.

Difference between Data Analysis and Data Analytics

We get mostly confused over these two terms and believe people mostly use anyone of these very confidently without understanding major difference.

Both terms means a crunching data . . .doing research over data . .  .making sense of data . . . . .putting your head down in data and finally come out with a result/ decision/ visualization based on hard work done.

But major difference is Analysis is for past data and Analytics is for predictions. For example:

a) Creating a Line Chart out of data analysis and with slope of line, understanding how was the pace of business.

a’) Creating a Line Chart our of data analytics and with slope of line, predicting upcoming pace of business.

b) Imagine websites showing data analysis on raising and dropping of share markets stocks.

b’) Similarly imagine experts showing data analytics on raising and dropping of share markets stocks future for viewers to decide better.

Analytics is not something of a rocket science. It is simple if we use simple techniques like statistics, math, qualitative analysis.

 

So People in the field of Data Analysis and Data Analytics are different, might use different tools, may earn different money.

So be along with this Data Science journey. More clarification and learning topics are on the way.