创建SSRS报表展示RDL及订阅的历史订阅失败累计计数并支持钻取

需求背景 #

现有每日推送前一日订阅失败数据的定时订阅任务，需新增如下能力：

1. 创建SSRS报表，展示每个RDL、每个订阅（多订阅场景）的历史订阅失败累计运行计数
2. 支持钻取查看具体的失败原因

实现逻辑说明 #

1. SQL逻辑调整 #

原有参考SQL仅统计近24小时的失败数据，需调整时间过滤规则适配全量历史统计需求，同时新增分组聚合逻辑计算每个订阅的累计失败次数。

2. SSRS报表配置 #

• 主报表：按RDL报表所属目录、RDL名称、订阅ID分组，展示每个订阅的累计失败次数、创建人、修改人等基础信息
• 钻取功能：将RDL名称/订阅描述设为交互触发项，点击后跳转至子报表，子报表根据传入的RDL标识、订阅ID过滤出对应所有失败明细，展示每次失败的时间、具体错误原因等信息
• 可配置时间参数，支持自定义查询指定时间段内的失败统计数据，适配不同排查需求

优化后参考SQL #

-- 全量订阅失败明细临时表
IF OBJECT_ID('tempdb..#SubsFailHistory') IS NOT NULL DROP TABLE #SubsFailHistory
SELECT 
    LEFT(ca.[Path],CHARINDEX('/',SUBSTRING(ca.[Path],2,255))) RootPath,
    ca.[Name] AS RDLReportName,
    c.SubscriptionID,
    c.[Description] AS SubscriptionDesc,
    c.ExtensionSettings,
    c.LastStatus AS FailReason,
    cu.UserName AS CreatedByUserName,
    mu.UserName AS ModifiedByUserName,
    c.LastRunTime AS FailTime,
    c.ModifiedDate
INTO #SubsFailHistory
FROM [ReportServer].[dbo].[Subscriptions] c
LEFT JOIN [ReportServer].[dbo].[Catalog] ca ON ca.ItemID = c.Report_OID
LEFT JOIN [ReportServer].[dbo].[Users] cu ON cu.UserID = c.OwnerID
LEFT JOIN [ReportServer].[dbo].[Users] mu ON mu.UserID = c.ModifiedByID
-- 如需限定统计时间范围可放开下方注释调整时间阈值
-- WHERE c.LastRunTime >= DATEADD(day,-180,getdate())
WHERE c.[Description] <> 'DupeCheck'
AND (
    LastStatus like '%; [1-9]% errors%'
    OR LastStatus like '%failure%'
    OR LastStatus  = 'An error has occurred during report processing.'
    OR LastStatus = 'The subscription contains parameter values that are not valid.'
    OR LastStatus LIKE 'Error: %'
)

-- 主报表累计统计查询
SELECT 
    RootPath,
    RDLReportName,
    SubscriptionID,
    SubscriptionDesc,
    CreatedByUserName,
    ModifiedByUserName,
    COUNT(1) AS TotalFailCount
FROM #SubsFailHistory
GROUP BY RootPath,RDLReportName,SubscriptionID,SubscriptionDesc,CreatedByUserName,ModifiedByUserName
ORDER BY TotalFailCount DESC

-- 钻取子表明细查询（传入对应SubscriptionID即可）
/*
SELECT FailTime,FailReason,ExtensionSettings 
FROM #SubsFailHistory 
WHERE SubscriptionID = @TargetSubscriptionID
ORDER BY FailTime DESC
*/

原有定时推送任务兼容 #

原有每日推送近24小时失败数据的任务无需调整，继续使用原有限定c.LastRunTime >= DATEADD(hh,-24,getdate()的SQL逻辑即可。

内容的提问来源于stack exchange，提问作者efox12