After reading a tweet by Ray Martinez, I decided to share the scripts for Reporting Services that I often use.

One of the scripts I used a lot, is a script that shows the Reports that were successfully executed in 2012. For these Reports you will see the last execution time, rows and bytes returned, and how long it took to gather and show the results:

USE ReportServer
SELECT
CL.Name AS ReportName,
CL.Description AS ReportDescription,
CL.Path AS ReportPath,
CL.CreationDate AS ReportCreationDate,
SUM(1) AS TotalNumberOfTimesExecuted,
MAX(EL.TimeStart) AS LastTimeExecuted,
AVG(EL.[RowCount]) AS AVG_NumberOfRows,
AVG(EL.TimeDataRetrieval) AS AVG_DataRetrievalTime,
AVG(EL.TimeProcessing) AS AVG_TimeProcessing,
AVG(EL.TimeRendering) AS AVG_TimeRendering
FROM ExecutionLog EL
JOIN Catalog CL
ON CL.ItemID = EL.ReportID
WHERE 1 = 1
AND CL.Name IS NOT NULL
AND EL.Status ='rsSuccess'
GROUP BY
CL.Name,
CL.Path,
CL.CreationDate,
CL.Description
HAVING YEAR(MAX(EL.TimeStart)) = 2012
ORDER BY COUNT(EL.ReportID) DESC

The second script I want to share with you, shows the first 1000 successfully executed Reports. The data that is returned includes Report format, parameters used to query the data, information about the returned resultset and time needed to return and render the data and Report, etc. I uses a top 1000 because our Report server returned a lot of rows, where I only needed a few for my analysis.

USE ReportServer
SELECT TOP 1000
EL.InstanceName AS SQLInstanceName,
EL.UserName AS ExecuterUserName,
EL.Format AS ReportFormat,
EL.Parameters AS ReportParameters,
EL.TimeStart AS TimeStarted,
EL.TimeEnd AS TimeEnded,
EL.TimeDataRetrieval AS TimeDataRetrieval,
EL.TimeProcessing AS TimeProcessing,
EL.TimeRendering AS TimeRendering,
EL2.Source AS Source,
EL.ByteCount AS ReportInBytes,
EL.[RowCount] AS ReportRows,
CL.Name AS ReportName,
CL.Path AS ReportPath,
CL.Hidden AS ReportHidden,
CL.CreationDate AS CreationDate,
CL.ModifiedDate AS ModifiedDate,
EL2.Format AS RenderingFormat,
EL2.ReportAction AS ReportAction,
EL2.Status AS ExectionResult,
DS.Name AS DataSourceName,
DS.Extension AS DataSourceExtension
FROM ExecutionLog EL
JOIN Catalog CL
ON CL.ItemID = EL.ReportID
LEFT JOIN ExecutionLog2 EL2
ON EL2.ReportPath = CL.Path
JOIN DataSource DS
ON DS.ItemID = CL.ItemID
WHERE 1 = 1
AND EL.Status = 'rsSuccess'
ORDER BY EL.TimeStart DESC

The next script returns an overview of the folder and Reports on your Report server. For every object on your server, you can see the creation- and modify date. The joined Executionlog table is used to get the number of times the Report was executed, by which user, and how much data was returned in which time:

USE ReportServer
SELECT
CASE CL.Type
WHEN 1 THEN 'Folder'
WHEN 2 THEN 'Report'
WHEN 3 THEN 'Resource'
WHEN 4 THEN 'Linked Report'
WHEN 5 THEN 'Data Source'
END AS ObjectType,
CP.Name AS ParentName,
CL.Name AS Name,
CL.Path AS Path,
CU.UserName AS CreatedBy,
CL.CreationDate AS CreationDate,
UM.UserName AS ModifiedBy,
CL.ModifiedDate AS ModifiedDate,
CE.CountStart AS TotalExecutions,
EL.InstanceName AS LastExecutedInstanceName,
EL.UserName AS LastExecuter,
EL.Format AS LastFormat,
EL.TimeStart AS LastTimeStarted,
EL.TimeEnd AS LastTimeEnded,
EL.TimeDataRetrieval AS LastTimeDataRetrieval,
EL.TimeProcessing AS LastTimeProcessing,
EL.TimeRendering AS LastTimeRendering,
EL.Status AS LastResult,
EL.ByteCount AS LastByteCount,
EL.[RowCount] AS LastRowCount,
SO.UserName AS SubscriptionOwner,
SU.UserName AS SubscriptionModifiedBy,
SS.ModifiedDate AS SubscriptionModifiedDate,
SS.Description AS SubscriptionDescription,
SS.LastStatus AS SubscriptionLastResult,
SS.LastRunTime AS SubscriptionLastRunTime
FROM Catalog CL
JOIN Catalog CP
ON CP.ItemID = CL.ParentID
JOIN Users CU
ON CU.UserID = CL.CreatedByID
JOIN Users UM
ON UM.UserID = CL.ModifiedByID
LEFT JOIN ( SELECT
ReportID,
MAX(TimeStart) LastTimeStart
FROM ExecutionLog
GROUP BY ReportID) LE
ON LE.ReportID = CL.ItemID
LEFT JOIN ( SELECT
ReportID,
COUNT(TimeStart) CountStart
FROM ExecutionLog
GROUP BY ReportID) CE
ON CE.ReportID = CL.ItemID
LEFT JOIN ExecutionLog EL
ON EL.ReportID = LE.ReportID
AND EL.TimeStart = LE.LastTimeStart
LEFT JOIN Subscriptions SS
ON SS.Report_OID = CL.ItemID
LEFT JOIN Users SO
ON SO.UserID = SS.OwnerID
LEFT JOIN Users SU
ON SU.UserID = SS.ModifiedByID
WHERE 1 = 1
ORDER BY CP.Name, CL.Name ASC

The last query is a short one. This returns the Reports that don’t inherit permissions, that are set in the site settings menu.

USE ReportServer
SELECT
Path,
Name
FROM Catalog
WHERE PolicyRoot = 1

Hopefully there will be more where this came from. So follow my blog, or contact me by email or Twitter!