Way to see schedules I made in one screen?

[eijer]

All,

Is there a way to see my schedule on one screen? It is too inconvenient to visit each item and check the schedule.

[d_gosbell]

Yes, just click on the "Gear" Icon in the right hand side of the header and choose the "My Subscriptions" option

[Anonymous]

If you are looking to find out the status of all your data refreshes on the PBI Server you can query the backend DB. This is of course dangerous as MS can change the underlying schema. But you can find out the status of those refreshes and also get some info back on individual errors. We have a couple of SSRS reports for this that we get pumped out to us regularly so we can monitor what's failed.

[eijer]

@Anonymous 

I want to see all the schedule that I've made, not the result of the schedule that I've made.

Thanx for your help.

[Anonymous]

If you are investigating the last status of all the schedules then you obviously have to list all the schedules.

Our SQL code looks somehting like this but be wanred MS can change the underlying schema anytime they like and this code may well break or not do what you expect it to.

SELECT
	sj.name AS SQLAgentJobName
	,c.name AS ReportName
	,c.[path] AS ReportPath
	,c.ItemID
		, CASE 
		WHEN s.laststatus = 'Completed Data Refresh' THEN s.laststatus
		WHEN LEFT(s.laststatus,19) =  'Data Refresh failed' THEN 'Data Refresh failed'
		ELSE 'Status Unknown'
		END AS ShortStatus
	, REPLACE(  LEFT(c.[path], CHARINDEX('/',c.[path],2)) , '/' ,'') AS basepath
	,s.[Description] as SubscriptionName
	, rs.SubscriptionID
	, s.laststatus

	,sh.SubscriptionHistoryID
	, CASE s.laststatus
		WHEN 'Completed Data Refresh' THEN 1
		ELSE 0
		END AS successFlag
	, CASE s.laststatus
		WHEN 'Completed Data Refresh' THEN 0
		ELSE 1
		END AS FailFlag
	, s.eventtype
	, s.LastRunTime
	, CASE jsch.next_run_date
            WHEN 0 THEN cast('n/a' as char(10))
            ELSE	convert(char(10), 
							CONVERT(datetime, 
									CONVERT(char(8),
											jsch.next_run_date)
									)
							,120)  
					+ ' ' 
					+ left(
							STUFF(
									(stuff(
											(replicate(
														'0', 
														6 - len(jsch.next_run_time)
													  )
											)
											+ convert(VARCHAR(6),
														jsch.next_run_time
													  )
											,3
											,0,
											':'
											)
									)
									,6
									,0,
									':'
								  ),
							8)
        END AS NextRunTime
	, sj.date_created
	, sj.date_modified
	, 1 AS ct
FROM ReportServer.dbo.ReportSchedule rs 
		INNER JOIN msdb.dbo.sysjobs sj
            ON rs.ScheduleID = CAST(sj.name AS uniqueidentifier)
			and 101 = sj.category_id  --NOTE this is a fixed ID for the category cretaed by the SSRS-PBI installer
			LEFT OUTER JOIN msdb.dbo.sysjobschedules jsch
				ON sj.job_id = jsch.job_id
        INNER JOIN ReportServer.dbo.Subscriptions s
            ON  rs.SubscriptionID = s.SubscriptionID
			AND 'DataModelRefresh' = s.eventTYpe
        INNER JOIN ReportServer.dbo.[Catalog] c
            ON s.report_oid = c.itemid
		LEFT OUTER JOIN (SELECT MAX(SubscriptionHistoryID) AS SubscriptionHistoryID, SubscriptionID FROM dbo.SubscriptionHistory GROUP BY SubscriptionID) sh
			ON rs.SubscriptionID = sh.SubscriptionID