Scheduled Refresh notifications (On-Premise)

The problem is the call to the subscription succeeds though the subscription itself fails. Which is, as you say, a bit rubbish!

You can add a second SQL Step to each SQLAgentJob and basically force a failure using some code like the stuff below. You can then just add your "operators" to the Notifications for the SQL Agent job and set the first step up to call the second on success and both steps to Fail the job!

This will generate a SQL Agent ALERT to the operators!  You could of course log the details of the query into a table somewhere  before you call RAISEERROR so you would have the details of what had failed.

Figuring out why the **bleep** thing failed is another matter entirely.

DECLARE @SQLAgentJobID UNIQUEIDENTIFIER;


SET @SQLAgentJobID = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID)));

IF EXISTS(

	SELECT	sj.name AS SQLAgentJobName
		   ,c.Name AS ReportName
		   ,c.[Path] AS ReportPath
		   ,s.[Description] AS SubscriptionName
		   ,rs.SubscriptionID
		   ,s.LastStatus
		   ,s.EventType
		   ,s.LastRunTime
		   ,sj.date_created
		   ,sj.date_modified
	FROM	ReportServer.dbo.ReportSchedule rs
			INNER JOIN msdb.dbo.sysjobs sj
				ON rs.ScheduleID = CAST(sj.name AS UNIQUEIDENTIFIER)
				   AND 101 = sj.category_id
			--INNER JOIN ReportServer.dbo.ReportSchedule c
			--    ON CAST(sj.name AS uniqueidentifier) = c.ScheduleID
			INNER JOIN ReportServer.dbo.Subscriptions s
				ON rs.SubscriptionID = s.SubscriptionID
			INNER JOIN ReportServer.dbo.[Catalog] c
				ON s.Report_OID = c.ItemID
	-- to find specific report last status 
	-- WHERE e.name = 'Usage Stats'
	-- to find failed status
	WHERE	LastStatus <> 'Completed Data Refresh'
	-- for a specific SQL Agent Job
			AND sj.name = @SQLAgentJobID)
BEGIN
	--LOG the query results above to something if you want a simple way of finding out what has failed
        RAISERROR('Data Refresh Failed!',16,1);
END
-- NOTE you have to look in subscription history for any error messages/details (good luck with that!)

PS Have I mentioned how shoddy and third rate the logging and monitoring is for PBI SSRS Server?