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.
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.