Optimizing Microsoft SQL Server 2000 Reporting Services: Performance and Access Reports from the Execution Log

6. Navigate to the following folder (or it’s equivalent, substituting your own CD drive for “F:” below):

F:extrasExecution Log Sample Reports

The contents of the Execution Log Sample Reports folder appear similar to that shown in Figure 3.

Figure 3: The Sample Execution Log Reports Appear

7. Select Edit —> Select All from the Windows Explorer main menu. The contents of the Execution Log Sample Reports folder become highlighted.

8. Select Edit —> Copy from the main menu.

9. Navigate to the new SSP-RS002 folder we created above.

10. Right-click within the folder.

11. Select Paste from the context menu that appears, as depicted in Figure 4.

Figure 4: Select Paste to Place the Sample File Copies

The copied files (there should be eleven) appear as shown in Figure 5.

Figure 5: The Sample Report Files Appear

Our next step will be to upload the samples into Report Manager, where we can review them and see what information they can deliver to the information consumers. The first step of this process will be to establish a Data Source Connection. We will use Report Manager, versus the Report Designer interface, for this purpose, but we should keep in mind that the upload / publication process can also be managed from within Report Designer.

Creating a Data Source Connection

As most of us are aware, the purpose of the Report Server is to serve, or to act as a presentation platform for, our reports. It connects to data sources to retrieve the data that it presents in the reports it “hosts.” When we refer to a data source in Reporting Services, we are referring to a collection of properties, in effect, that represents a connection to a given data source. This collection of properties has a name, as it does in other applications where data sources exist (a scenario with which most of us are familiar). Data sources contain the following, where applicable:

  • Specification of the data processing extension we use to process queries of the type for which we intend to use the connection;

  • A connection string that allows us to locate the source;

  • Access credentials involved in allowing us to read the data within the source.

A data source connection can be embedded in a report (where it is typically defined within the creation process) or it can be defined as a shared data source item that is managed by a Report Server. We will be establishing a shared data source item for the Execution Log reports, so that the entire set can reference the same self-contained, underlying data source. In addition to ease of referencing in the reports, our shared data source will provide the benefit of maintenance from a single location, as we shall see.

We will create a shared data source for the reports before we upload them to provide an immediate mechanism to link them to the data they are intended to present. The data source connection will be independent of the reports themselves. Our set of sample reports will share data housed in a single source, the RSExecutionLog database, an excellent application of a shared data source.

NOTE: For the setup of the RSExecutionLog database, which we will require to complete prospective steps of our practice example, see Execution Log Reporting: Preparation as a Data Source.

Our first step is to start Report Manager.

1. Click Start.

2. Navigate to the Reporting Services program group that installs within a typical setup. The equivalent on my PC appears as depicted in Figure 6.

Figure 6: Navigate to Report Manager …



No comments yet... Be the first to leave a reply!