cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
eijer Regular Visitor
Regular Visitor

Way to see schedules I made in one screen?

All,

 

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

4 REPLIES 4
Super User
Super User

Re: Way to see schedules I made in one screen?

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

stpnet Established Member
Established Member

Re: Way to see schedules I made in one screen?

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.

 

 

Highlighted
eijer Regular Visitor
Regular Visitor

Re: Way to see schedules I made in one screen?

@stpnet 

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.

stpnet Established Member
Established Member

Re: Way to see schedules I made in one screen?

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

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Kudoed Authors (Last 30 Days)