Some report developers don’t realize that there are two databases that you can use to lookup or analyze reporting services information. It’s often useful to write your own reports, based on information stored in these databases.
The first database is ReportServer, which is used by the Report Services to store all the information about reports that have been uploaded to the report manager. Information such as the report catalog, settings, and security are all stored within the ReportServer database. The database ReportServerTempDB stores temporary information such as report snapshots, user sessions, and report execution information.
I have three examples of useful reports created from the ReportServer database. The first report is what I call the Report Index. It provides a list of all the items in the reporting services catalog, with links to render each report in the catalog, as shown in Figure 35. This can prove to be useful as it allows your report users to run just one report and get a list of all reports, without having to navigate through the report manager.
Figure : The Report Index report
I provide an example Report Index.rdl report as part of the code download with this article. You’ll have to point the report to your ReportServer database.
The second report, Report Usage, is basically a metrics-type report, providing details of reports that are being executed and how many times per month. The ReportServer database contains a table called ExecutionLog that, by default, stores every report execution for 60 days. You can update the setting ExecutionLogDaysKept in table ConfigurationInfo to allow for more than 60 days of execution tracking. Again, you can obtain the Report Usage.rdl file from the download file, and an example of the report is shown in Figure .
Figure : The Report Usage report
The third report, Report Users, is similar to the Report Usage report. Report Users report is basically a metrics-type report, providing details of which users are executing the reports. Again, you can obtain the Report Users.rdl file from the download file, and an example of the report is shown in Figure.
You will need to point all the report mentioned in this section to your ReportServer database.