Inventory of the reports and subscriptions on MS Reporting Server

  • Iulian -207023

    SSCertifiable

    Points: 7509

    I need to see what reports are scheduled to run in MS Reporting Server.

    I tried

    USE ReportServer

    SELECT * FROM Subscriptions

    But I got XML data that is not easy to read, I would like this XML data to look like a table.

    What are the options for making an inventory of the reports and subscriptions on MS Reporting Server?

    Thank you,

    Iulian

  • Iulian -207023

    SSCertifiable

    Points: 7509

    I found this query by googleing and it works, I have got the inventory of the reports.

    USE ReportServer

    SELECT

    CatalogParent.Name ParentName,

    Catalog.Name ReportName,

    ReportCreatedByUsers.UserName ReportCreatedByUserName,

    Catalog.CreationDate ReportCreationDate,

    ReportModifiedByUsers.UserName ReportModifiedByUserName,

    Catalog.ModifiedDate ReportModifiedDate,

    CountExecution.CountStart TotalExecutions,

    ExecutionLog.InstanceName LastExecutedInstanceName,

    ExecutionLog.UserName LastExecutedUserName,

    ExecutionLog.Format LastExecutedFormat,

    ExecutionLog.TimeStart LastExecutedTimeStart,

    ExecutionLog.TimeEnd LastExecutedTimeEnd,

    ExecutionLog.TimeDataRetrieval LastExecutedTimeDataRetrieval,

    ExecutionLog.TimeProcessing LastExecutedTimeProcessing,

    ExecutionLog.TimeRendering LastExecutedTimeRendering,

    ExecutionLog.Status LastExecutedStatus,

    ExecutionLog.ByteCount LastExecutedByteCount,

    ExecutionLog.[RowCount] LastExecutedRowCount,

    SubscriptionOwner.UserName SubscriptionOwnerUserName,

    SubscriptionModifiedByUsers.UserName SubscriptionModifiedByUserName,

    Subscriptions.ModifiedDate SubscriptionModifiedDate,

    Subscriptions.Description SubscriptionDescription,

    Subscriptions.LastStatus SubscriptionLastStatus,

    Subscriptions.LastRunTime SubscriptionLastRunTime

    FROM

    dbo.Catalog

    JOIN

    dbo.Catalog CatalogParent

    ON Catalog.ParentID = CatalogParent.ItemID

    JOIN

    dbo.Users ReportCreatedByUsers

    ON Catalog.CreatedByID = ReportCreatedByUsers.UserID

    JOIN

    dbo.Users ReportModifiedByUsers

    ON Catalog.ModifiedByID = ReportModifiedByUsers.UserID

    LEFT JOIN

    (

    SELECT

    ReportID,

    MAX(TimeStart) LastTimeStart

    FROM

    dbo.ExecutionLog

    GROUP BY

    ReportID

    ) LatestExecution

    ON Catalog.ItemID = LatestExecution.ReportID

    LEFT JOIN

    (

    SELECT

    ReportID,

    COUNT(TimeStart) CountStart

    FROM

    dbo.ExecutionLog

    GROUP BY

    ReportID

    ) CountExecution

    ON Catalog.ItemID = CountExecution.ReportID

    LEFT JOIN

    dbo.ExecutionLog

    ON LatestExecution.ReportID = ExecutionLog.ReportID

    AND LatestExecution.LastTimeStart = ExecutionLog.TimeStart

    LEFT JOIN

    dbo.Subscriptions

    ON Catalog.ItemID = Subscriptions.Report_OID

    LEFT JOIN

    dbo.Users SubscriptionOwner

    ON Subscriptions.OwnerID = SubscriptionOwner.UserID

    LEFT JOIN

    dbo.Users SubscriptionModifiedByUsers

    ON Subscriptions.OwnerID = SubscriptionModifiedByUsers.UserID

    ORDER BY

    CatalogParent.Name,

    Catalog.Name

    Regards,

    Iulian

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply