SQL Server Reporting Services查询

420 发表于 Dev

420

我想要一份关于成功报告，成功失败报告的报告，其中包括报告名称，创建报告的用户，失败原因，报告路径，订阅，时间表等附加信息，例如报告名称查看历史数据以显示趋势。

我可以使用哪些表来获取报告所需的数据？

我发现了一个查询，该查询可以为我提供执行此报告所需的部分信息，但是由于它包含的表已不再存在于sql-server 2012的报告服务器DB中，因此它已过时

任何帮助或指导将不胜感激。

[链接到过时的查询] https://social.msdn.microsoft.com/Forums/sqlserver/zh-CN/c6b35fff-2a46-46ce-bc1e-c166a4d65c2d/ssrs-notification-of-success-or-failure-of -report-generation？forum = sqlreportingservices

[链接到我有关此主题的上一个问题]不兼容的SQL Server Reporting Services查询

阿杜古

这是我用来搜索报告服务执行日志的查询。另外，在GitHub上有一些示例报告

DECLARE @all_value AS VARCHAR(10)
DECLARE @LogStatus AS VARCHAR(50)
DECLARE @ReportFolder AS VARCHAR(450)
DECLARE @ReportName AS VARCHAR(450)
DECLARE @UserName AS VARCHAR(260)
DECLARE @StartDate AS DATETIME
DECLARE @EndDate AS DATETIME

SET @all_value = '<ALL>'
SET @LogStatus = '<ALL>' --filter your execution log status here
SET @ReportFolder = '...A Report Folder Name...'
SET @ReportName = '<ALL>' 
SET @UserName = '<ALL>'
SET @StartDate = NULL
SET @EndDate = NULL



;WITH
report_users 
AS
(
    SELECT UserID, UserName, SimpleUserName = UPPER(RIGHT(UserName, (LEN(UserName)-CHARINDEX('\',UserName)))) FROM dbo.Users
)
,
report_catalog
AS
(
    SELECT    
        rpt.ItemID
      , rpt.CreatedById
      , rpt.ModifiedById
      , rpt.[Type]
      , rpt.[Name] 
      , ReportName = rpt.[Name] 
      , rpt.[Description]
      , rpt.Parameter
      , CreationDate = CONVERT(DATETIME, CONVERT(VARCHAR(11), rpt.CreationDate, 13))
      , ModifiedDate = CONVERT(DATETIME, CONVERT(VARCHAR(11), rpt.ModifiedDate, 13))
      , ReportFolder = SUBSTRING(rpt.[Path], 2, Len(rpt.[Path])-Len(rpt.[Name])-2) 
      , rpt.[Path]
      , URL_ReportFolder = 'http://' + Host_Name() + '/Reports/Pages/Report.aspx?ItemPath=%2f'  + SUBSTRING(rpt.[Path], 2, Len(rpt.[Path])-Len(rpt.[Name])-2)  + '&ViewMode=List'
      , URL_Report = 'http://' + Host_Name() + '/Reports/Pages/Report.aspx?ItemPath=%2f'  + SUBSTRING(rpt.[Path], 2, Len(rpt.[Path])-Len(rpt.[Name])-2)  + '%2f' + rpt.[Name]
      , ReportDefinition = CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), rpt.Content))  
      , HostName = Host_Name()
    FROM 
      dbo.Catalog AS rpt
    WHERE 
      1=1
      AND rpt.[Type] = 2
)
    SELECT 
      ReportPath = rpt.[Path]
    , rpt.ReportFolder
    , ReportName = rpt.[Name]
    , rpt.URL_ReportFolder
    , rpt.URL_Report 
    , URL_Report_Filtered = rpt.URL_Report + '&rs:Command=Render&' + CONVERT(VARCHAR(2000), el.[Parameters])
    , UserName = usr.SimpleUserName
    , el.[Status]
    , el.TimeStart
    , el.[RowCount]
    , el.ByteCount
    , el.[Format]
    , el.[Parameters]
    , TotalSeconds = CONVERT(CHAR(8),DATEADD(ms,(el.TimeDataRetrieval + el.TimeProcessing + el.TimeRendering),0),108)
    , TimeDataRetrieval = CONVERT(CHAR(8),DATEADD(ms,el.TimeDataRetrieval,0),108) 
    , TimeProcessing = CONVERT(CHAR(8),DATEADD(ms,el.TimeProcessing,0),108)  
    , TimeRendering = CONVERT(CHAR(8),DATEADD(ms,el.TimeRendering,0),108) 
    , OrderbyDate = CAST(TimeStart AS DATETIME) 
FROM 
    report_catalog AS rpt 
    LEFT JOIN dbo.ExecutionLog AS el ON el.ReportID = rpt.ItemID
    LEFT JOIN report_users AS usr ON el.UserName = usr.UserName
WHERE 
    1=1
    AND (@all_value IN(@LogStatus) OR el.[Status] IN(@LogStatus))
    AND (@all_value IN(@ReportFolder) OR rpt.ReportFolder IN(@ReportFolder))
    AND (@all_value IN(@ReportName) OR rpt.ReportName IN(@ReportName))
    AND (@all_value IN(@UserName) OR usr.SimpleUserName IN(@UserName))
    AND (@StartDate IS NULL OR CONVERT(DATETIME, CONVERT(VARCHAR(11),el.TimeStart,13)) >= @StartDate)
    AND (@EndDate IS NULL OR CONVERT(DATETIME, CONVERT(VARCHAR(11),el.TimeStart,13)) <= @EndDate)