Server Management Report Samples

  • Article

The Server Management sample reports include two sets of reports:

  • Server Management reports are a set of predefined report definition files that use SQL Server metadata as a source of information for reports. You can use the sample reports to view server information or as templates for designing new reports.
  • Execution Log sample reports includes predefined report files, sample database scripts, and a sample SQL Server Integration Services package that you can use to view execution log data for a report server.

Note

If you are using SQL Server 2005 Express Edition with Advanced Services, you must modify the report server URL before publishing the sample reports to the report server. Also, be aware that the Execution Log sample reports are not supported in SQL Server 2005 Express Edition with Advanced Services. The Execution Log sample reports use data provided by SQL Server Integration Services, which is not available in SQL Server Express.

Language

Report Definition Language (RDL)

Server Management Report Files

The following table provides descriptions about each report.

Report Description

Schema Table Extended Properties

Provides types and extended properties for each column in a table, categorized by table and schema.

Columns by Table and Schema Subreport

A subreport used by the Schema Table Extended Properties report.

Report Execution Log Files

The following table provides descriptions about each report, script file, and package.

Report Description

Createtables.sql

Creates the table structure for storing report execution log data.

Cleanup.sql

Removes data from the tables so that you can refresh the tables with new data.

RSReportExecutionLog_Update.dtsx

Extracts internal data from the report server database and copies it to a user-defined database that contains report execution log data.

RSReportExecutionLog_Update.dtsConfig

Specifies settings for the package.

Execution Status Codes.rdl

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.

Execution Summary.rdl

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.

Report Summary.rdl

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

Prerequisites

Your system must meet the following requirements to use the sample reports.

Server

  • Microsoft SQL Server 2005 Reporting Services (Evaluation, Standard, Developer, and Enterprise Edition) or SQL Server 2005 Express Edition with Advanced Services
  • Microsoft SQL Server 2005 Integration Services (Required for the Execution Log reports)
  • Microsoft SQL Server 2005 Database Services

Client

  • Microsoft Internet Explorer 6.0 with scripting enabled

  • Business Intelligence Development Studio (optional)

    Note

    Business Intelligence (BI) Development Studio is not supported on Itanium-based computers. However, support for BI Development Studio is available for x64-based computers. If the SQL Server 2005 sample databases have been deployed on an Itanium-based computer, use BI Development Studio on either an x86-based or x64-based computer to modify and run the samples.

Installing Report Samples

The SQL Server samples are not installed automatically during setup. For instructions about how to install the samples, see Installing Samples.

Deploying Sample Reports

  1. From the Business Intelligence Development Studio toolbar, click File, point to Open and then click Project/Solution.
  2. Browse to C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Report Samples\Server Management Sample Reports, select the file Server Management Sample Reports.sln and click Open.
  3. If you are using SQL Server 2005 Express Edition with Advanced Services, you must modify the target report server URL property and change the default connection string that specifies a connection to the master system database. The default values in this sample are not correct for a SQL Server Express installation:
    1. From the Project menu, select Properties.
    2. In Overwrite Data Sources, select True.
    3. In TargetServerURL, type the following URL:
      https://localhost/reportserver$SQLExpress
    4. Click OK to close the Solution Property Pages dialog box.
    5. In Solution Explorer, in Shared Data Sources, double-click master.rds.
    6. In the Shared Data Source dialog box, in the General tab, modify the connection string to use the following value:
      Data Source=localhost\SQLExpress; Initial Catalog=master
    7. Click OK to close the Shared Data Source dialog box.
  4. On the standard toolbar, select the Production solution configuration. (The default configuration is Debug.)
  5. On the Build menu, click Deploy Server Management Sample Reports. When you deploy the solution, the reports are published to the report server.

If you are running SQL Server Express, you can skip the following sections that explain how to create a report execution log database and configure an SSIS package. If you do not know how to view reports that you just published, be sure to read the last section in this topic that explains how to open the reports in Report Manager.

Create a Database for Report Execution Data

The Execution Log reports retrieve data from a user-defined database that is created and populated by a package you run in SQL Server Integration Services.

  1. In SQL Server Management Studio, in Object Explorer, connect to a Database Engine instance.
  2. Right-click Databases and select New Databases.
  3. In Database Name, type RSExecutionLog and then click OK.
  4. On the Databases node, right-click RSExecutionLog and select New Query.
  5. On the File menu, point to Open, and then click File. Browse to the folder containing Createtables.sql. By default, this folder is C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Report Samples\Server Management Sample Reports\Execution Log Sample Reports. Open Createtables.sql in the query window.
  6. In the Available Databases list box on the toolbar in SQL Server Management Studio, select RSExecutionLog.
  7. Click Execute on the toolbar to run the query.

Configure and Run the Package

You can create a SQL Server Agent service job to run the package. To set up your computer for querying report execution log data, follow these steps:

  1. Verify that SQL Server Integration Services is enabled and running. Use SQL Server Surface Area Configuration tool to enable and start the service.

  2. In Management Studio, open SQL Server Agent, right-click Jobs, and then click New Job.

  3. In the New Job dialog box, on the General page, type RSExecutionLog_Update as the job name.

  4. On the Steps page of the New Job dialog box, click New. The New Job Step dialog box appears.

  5. On the Step page, for Step Name, type RSExecutionLog_Update.

  6. For Type, select SQL Server Integration Services Package. A collection of tabbed pages appear when you select a package execution type. The next set of steps refers to these tabbed pages.

  7. On the General tab, for Package Source, select File System.

  8. For the Package field, click (…) to browse to the folder containing RSExecutionLog_Update.dtsx, and then click OK.
    By default, this folder is C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Report Samples\Server Management Sample Reports\Execution Log Sample Reports. Open RSExecutionLog_Update.dtsx.

    Note

    If you are running the package on a 64-bit computer, you must set the PreCompile property to True. For more information, see Integration Services Considerations on 64-bit Computers.

  9. On the Configurations tab, click Add. Browse to the folder containing RSExecutionLog_Update.dtsConfig.
    By default, this folder is C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Report Samples\Server Management Sample Reports\Execution Log Sample Reports. Open RSExecutionLog_Update.dtsConfig.

  10. On the Data Sources tab, select the check boxes for the source and destination connection managers.
    The configuration file specifies the default report server database, named reportserver, as the source. The destination is RSExecutionLog. If you are using different databases, modify the data source names.
    Verify that the user name and password are valid for accessing the data sources. By default, the configuration file specifies the System Administrator account. If you are not the SQL Server administrator, replace the user name and password with your credentials.

  11. Click OK to save the step.

  12. Click OK to save the job. You can now run the job to execute the package.
    Although you can create a schedule to run this job at regular intervals, be aware that the new data will be appended to existing data. If you do not want to include historical data in the reports, run the Cleanup.sql script to clear the tables.

  13. In the Jobs node, right-click RSExecutionLog_Update, and click Start. SQL Server Agent executes the package, populating the RSExecutionLog database with data from the report server database.

Retrieving Report Execution Log Data in the Sample Reports

Some of the report execution log sample reports require that you provide a start date and end date to retrieve report execution log data that was created between those dates. When setting an end date, specify the date that is one day ahead (for example, if you want to view log information that was created on August 11, specify August 12 as the end date).

The reason for specifying dates a day ahead is that the sample reports are configured to use the default time of 00:00:00 (which is the start of the 24-hour clock). If you specify an end date of August 11, the report will show server activity through August 10 23:59:59. To view report execution log data that occurred during the hours of August 11, you should specify August 12 as the end date.

Viewing Report Samples in Report Manager

After you publish the report samples, you can view them in Report Manager. Viewing reports requires the following permissions:

  • You must have permission to log on to the local SQL Server Database Engine that hosts the databases you are working with. By default, report data sources are configured to use Microsoft Windows integrated security. Your Windows account credentials are used to log on to the database.
  • You must have a role assignment that grants your user account access to the report. If you are a local administrator on the computer, all of these permissions are granted automatically. If you are not a local administrator, you must configure role assignments and login permissions before you view the report.

To view a report in Report Manager, follow these steps:

  1. Open a browser window. Report Manager requires Internet Explorer 6.0 or later with scripting enabled.
  2. Type the Report Manager URL in the address bar of a Web browser. By default, the URL is http://<webservername>/reports. If you are using SQL Server Express, the default URL is https://localhost/reports$SQLExpress.
  3. Navigate to the folder that contains the reports. By default, it is the Server Management Sample Reports folder.
  4. Click the name of the report that you want to open.

See Also

Other Resources

Samples and Sample Databases
Reporting Services Tutorials
Installing AdventureWorks Sample Databases and Samples
How to: Publish Report Samples (Report Designer)
How to: Upload Report Samples (Report Manager)

Help and Information

Getting SQL Server 2005 Assistance