From time to time reports fail and you need know about them so that they can be sent to the relevant recipients without any delay. The script below has been used to create a report with data driven subscription.
SELECT
'Failed Subscriptions' as Type,
convert(char(40),c.path) as Folder_Path,
c.name as Report_Name,
case when len(convert(char(8000),parameters)) =19 then 'No Parameters' else
convert(char(700),parameters) end as parameters,
s.description as Recipients,s.lastruntime,
convert(char(500),laststatus) as Error,
'PathName' + replace(replace(c.path,'/','%2f'),' ','+') + '&ViewMode=Detail'
as [ReportURL],
'Pathname' + replace(replace(c.path,'/','%2f'),' ','+')
+ '&SelectedTabId=PropertiesTab&ViewMode=Detail&SelectedSubTabId=SubscriptionsTab'
as [SubsURL],
right(UserName,len(username)-12) as created_by,
case when s1.eventtype <>'SharedSchedule' then 'ReportSpecific' else s1.Name end as Schedule_Name
from subscriptions s
join catalog c on s.report_oid=c.itemid
join users u on s.ownerid = u.userid
LEFT OUTER join reportschedule rs on rs.subscriptionid = s.subscriptionid
LEFT OUTER join schedule s1 on s1.scheduleid = rs.scheduleid
where
(laststatus like '%fail%'
or LastStatus like 'error:%')
