October 27, 2010 at 1:44 am
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
October 27, 2010 at 2:41 am
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 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply