Have you been looking for a way to measure report performance?  Want to know who accesses your reports most frequently?  Well there are some free reports on http://msftrsprodsamples.codeplex.com/ that you may already have downloaded and don’t realize it that contains this information. 

When you download the AdventureWorks Report Samples for either 2005 or 2008 a script to create an RSExecutionLog database, an SSIS package to load it, and a set of auditing reports are available in the extremely long file path C:\Program Files\Microsoft SQL Server\100\Samples\Reporting Services\Report Samples\Server Management Sample Reports\Execution Log Sample Reports.  Here are your steps to get setup:

1.       Create a database called RSExecutionLog.

2.       Run the CreateTables.sql file (found in the folder) with a query window open to the RSExecutionLog database.

3.       Create a SQL Agent job that runs the RSExecutionLog_Update.dtsx SSIS package (found in the folder) and schedule it to run as frequently as you need the data to be updated.

4.       I don’t think they did any updating to this SSIS package from 2005 to 2008 so if you’re using 2008 then the job will likely fail if you try and run it now.  Open the package inside BIDS then open the script task and close it.  Save and overwrite the .dtsx file in the extremely long path above.

5.       Run the SQL Agent job now and it should load the RSExecutionLog database for you.

6.       Open the Execution Log Sample Report project (found in the folder) in BIDS.  Notice the reports are not upgraded to 2008 either.  You’re forced to go through the upgrade wizard.  Point it to your Report Server and deploy.

Check the Report Server and you should have 3 reports now detailing your execution results.  The Report Summary with the following description:

“Shows summary report execution information for a specific report within a specified date range.”

The Execution Summary with the following description:

“Shows overall report execution statistics for a specified date range. It shows the number of reports processed each day, the top 10 most requested reports, and the top 10 longest running reports.”

The Execution Status Code with the following description:

“Shows the success and failure rates for all report executions occurring within a specified date range. It also shows which failure codes occurred, and which reports failed to process.”

If you dig through some of the other folders that have been added from the sample reports you will find other useful reports.  For example, the Server Management Sample Reports have some basic database documentation built in. It generates a couple reports using MSDB as the data source.  I did notice to actually use these reports you will need to change some of the defaults that are set to AdventureWorks in the parameters and report actions.  Anyways,  I can easily see how these can be really useful.  Enjoy!