While working with a client a couple weeks back, I was asked to determine the count of reports that were being executed under each folder off the root directory on the Reporting Services instance. Each of the folders off the root directory represented a department at the client.
For the output, I decided that I wanted to break out the reports executed by each hour of the day to see where the traffic was occurring. Then the columns would be named after the first level folders off the root directory with counts of the number of reports executed under each folder.
The script is setup to take any date range you’d like. In the case with the client, I was looking at that data for the first of the month. In the script below, I expanded it to execute for the entire year.
DECLARE @ColumnList table (FirstLevelFolder nvarchar(128))
INSERT INTO @ColumnList
SELECT DISTINCT REPLACE(LEFT(Path, CHARINDEX('/',Path + '/',2)),'/','')
FROM dbo.ExecutionLog el
INNER JOIN dbo.Catalog c ON el.ReportID = c.ItemID
WHERE el.TimeStart BETWEEN @StartDate AND @EndDate
AND Path ''
DECLARE @Columns nvarchar(max)
,@SQL nvarchar(max)
SELECT @Columns = STUFF((
SELECT ',' + QUOTENAME(FirstLevelFolder)
FROM @ColumnList
ORDER BY FirstLevelFolder
FOR XML PATH('')
), 1, 1, '')
SET @SQL = 'SELECT StartHour, '
+ @Columns
+ 'FROM (
SELECT DATEPART(hh, TimeStart) as StartHour, REPLACE(LEFT(Path, CHARINDEX(''/'',Path,2)),''/'','''') as Path, COUNT(*) as Executions
FROM dbo.ExecutionLog el
INNER JOIN dbo.Catalog c ON el.ReportID = c.ItemID
WHERE el.TimeStart BETWEEN @StartDate AND @EndDate
GROUP BY DATEPART(hh, el.TimeStart), REPLACE(LEFT(Path, CHARINDEX(''/'',Path,2)),''/'','''')) x '
+ 'PIVOT ' +
+ '(SUM(Executions) FOR Path IN (' + @Columns + ')) AS pt;';
EXEC sp_executesql @SQL, N'@StartDate datetime, @EndDate datetime', @StartDate = @StartDate, @EndDate = @EndDate
There are two things to take away from this script. The first is that logging in the reporting services provides a lot of information that can be used to see what the users are doing. And second, with a little dynamic SQL data can be pivoted without knowing what the end columns will be.
Strate SQL 