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.

 

 

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 Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,146)