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?
