Optimizing Microsoft SQL Server Reporting Services: Performance and Access Reports from the Execution Log
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 …
3. Click Report Manager to initialize the application.
NOTE: If Report Manager does not appear in the manner shown, whether because you declined setup of the program group, a disablement of the feature, or other, unknown reason, simply get there by typing the appropriate URL into the address bar of your web browser. The default URL is as follows:
As an example, my <webservername> would be MOTHER1, the name of my server, and would appear, in this approach, in my browser Address line as shown in Figure 7.
Figure 7: Navigate to Report Manager … Alternative Route
We arrive at the Report Manager Folder View. Let’s create a new folder, within which we can isolate the reports we will be uploading.
4. Click New Folder.
The New Folder page appears.
5. Type the following into the Name box:
Execution Log Reports
6. Type the following into the Description box:
Performance & Auditing Reports based upon the Report Server Execution Log
With our input, the New Folder page appears as depicted in Figure 8.
Figure 8: The New Folder Page, with Input