cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
eijer
Helper I
Helper I

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
d_gosbell
Super User II
Super User II

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

stpnet
Impactful Individual
Impactful Individual

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.

 

 

@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
Impactful Individual
Impactful Individual

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
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps