Server Management Report Samples

11/05/2008 21:36:06


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.
Important:
SQL Server samples and sample databases must be downloaded and installed before you can view or work with them. For more information, see Considerations for Installing SQL Server Samples and Sample Databases.



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 2008 Reporting Services (SSRS) (Evaluation, Standard, Developer, and Enterprise edition)
*
Microsoft SQL Server Integration Services (Required for the Execution Log reports)
  • Microsoft SQL Server Database Services
Client
  • Microsoft Internet Explorer 6.0 or later, 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 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

SQL Server samples and sample databases must be downloaded and installed before you can view or work with them. For more information, see Considerations for Installing SQL Server Samples and Sample Databases.

Deploying Sample Reports

  1. From the Business Intelligence Development Studio toolbar, click File, point toOpen and then click Project/Solution.
  2. Browse to <drive:>\Program Files\Microsoft SQL Server\100\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 2008 Express 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:
  4. From the Project menu, select Properties.
  5. In Overwrite Data Sources, select True.
  6. In TargetServerURL, type the following URL: http://localhost/reportserver$SQLExpress
  7. Click OK to close the Solution Property Pages dialog box.
  8. In Solution Explorer, in Shared Data Sources, double-click master.rds.
  9. 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
  10. Click OK to close the Shared Data Source dialog box.
  11. On the standard toolbar, select the Production solution configuration. (The default configuration is Debug.)
  12. 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, because SSIS is not available in SQL Server Express. 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. Internal data is extracted from the report server database and copied to the user-defined database 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 <drive:>\Program Files\Microsoft SQL Server\100\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 Configuration Manager 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 RSExecutionLogUpdate.dtsx, and then click Open. By default, this folder is <drive:>\Program Files\Microsoft SQL Server\100\Samples\Reporting Services\Report Samples\Server Management Sample Reports\Execution Log Sample Reports. Open RSExecutionLogUpdate.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 64-bit Considerations for Integration Services.*
  9. On the Configurations tab, click Add. Browse to the folder containing RSExecutionLogUpdate.dtsConfig. By default, this folder is <drive:>\Program Files\Microsoft SQL Server\100\Samples\Reporting Services\Report Samples\Server Management Sample Reports\Execution Log Sample Reports. Open RSExecutionLogUpdate.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 Job at Step. 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 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 http://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

Tasks
How to: Publish Report Samples Considerations for Installing SQL Server Samples and Sample Databases
Other Resources
AdventureWorks Sample Databases Reporting Services Tutorials How to: Upload Report Samples (Report Manager)
Help and Information
  • Getting SQL Server 2008 Assistance* © 2008 Microsoft Corporation. All rights reserved.

Last edited Nov 6, 2008 at 10:05 PM by bonniefe, version 8

Comments

duckettb Jan 13 at 7:14 PM 
I downloaded this package of samples but the package does not contain the server management sample files. I followed techgirl441's suggestion to go to the source control and downloaded an older version. The Katmai_trunk contains the file but fails while unzipping the files with 7-zip. So this download in essentially useless or the server management samples.

techgirl441 Mar 9, 2011 at 7:46 PM 
This is a bit tricky. The instructions that link through Microsoft website isn't clear. I finally found all the scripts as they mentioned. Please follow this link.

http://msftrsprodsamples.codeplex.com/SourceControl/list/changesets

on the right of the page, click download. That should contain all the sample scripts that you can work on for report execution log.

Mabs Feb 7, 2011 at 3:47 PM 
This download contains only the SSRS2008R2 x64 MSI included. I'm running an x86 machine. I have a saved version of the x86 download but it contains no Server Management Application. I have spent the best part of my morning trying to find an x86 download with no success. Very frustrating!

rbauer Jan 18, 2011 at 3:10 PM 
When I download and install, I am not getting the above scripts or packages. What am I doing wrong, I'm thinking they should be in the download? I'm runnin Win7 64 bit. Thanks.

Abbas_141 Aug 10, 2010 at 1:06 PM 
I am getting error on Destination connection....
The user I am using for Destination (RsExecutionLog db) have ddladmin,datawriter, datareader and execute permissions....
datareader permission are graneted to user fetching data from Source (ReportServer)!
Is there any list of instruction(s) for configuring RsExecutionLog_Update.dtsx file properly?

Darwin101 Jan 20, 2010 at 1:55 PM 
RSExecutionLog_Update Fails if parameters are more than 4000 char lenght.