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.