Category Archives: SSRS

SSRS – Reporting Services Database

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.

Leave a comment

Filed under SSRS

Report Data in Page Header/Footer

Reporting Services does not provide out of-the-box support for use of information from your queries in Page Headers and Footers. There are two ways around this.

The first way is to create controls in the body of your report, holding the values you need to display in the header and/or footer. You can set these controls to “hidden”, and place them in some out-of-the-way place, towards the bottom of the report. Then, you can set expressions on controls in your header and footer sections to the value of the control in the body of your report.

In the following example, shown in Figure 27, I placed a textbox control in the body of the report named “textbox1”, with a value of “Hello World”. In the header section, I placed a textbox with a value of “=ReportItems!textbox1.Value”. I then copied the control in the header section and pasted it into the footer section.

Figure : Display report data in headers and footers, using a hidden control in the body

If you preview this report, the value of “textbox1” will be displayed in the header and footer.

Figure: Three times Hello World

A slightly cleaner option, in my opinion, is to create a public function that can be called to set the value of a variable, which can then be used in any or all sections of the report body, header, and footer. Figure shows the embedded code that creates this SetReportTitle function, containing the _Title variable.

Figure: The SetReportTitle function.

In this example, you can then simply set the value of the hidden textbox in the body of your report to “=Code.SetReportTitle(“Report Title”)“. This calls our function and sets the value of the _Title variable to Report Title.

Now, you can set the value of any control in the header or footer to “=Code._Title”. The variable can be used in any or all sections of the report body, header, and footer.

Leave a comment

Filed under SSRS