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

9. Repeat steps 1 through 7 above, for each of the following files in the SSP-RS002 folder (giving each a logical name within the respective Name box), from which we uploaded  todays reports.rdl:

  • longest running reports.rdl

  • report parameters.rdl

  • reportsbymonth.rdl

  • reportsbyuser.rdl

  • reportsexecutedbyday.rdl

  • reportsize.rdl

  • reportsuccessrate.rdl

Once we have uploaded the individual reports, we can see them appear listed on the Execution Log Reports page, Folder View, Content tab, as depicted in Figure 16.

Figure 16: The Execution Log Reports Appear in the Folder View – Content Tab

Having uploaded the eight reports listed above, we can click on each to ascertain that the report is executing properly.

10. Open each report, executing it to overview the data it presents and other features.

We now have a working data source, as well as a group of sample reports that not only provide a “quick start” for the information consumers’ performance and auditing reporting efforts, but which also act as a good set of reports to follow when constructing new reports along the same lines. We will create a custom report next, to obtain a little more familiarity with the source data, as well as with a representative report’s layout and other characteristics.

11. Close the browser, when finished exploring the various reports.

Create a Custom Audit Report

We will perform modifications to a copy of one of the existing Execution Log reports we have uploaded, to customize it to meet a local requirement. The procedure that we follow would be similar for modifications of any of the sample reports, with obvious differences arising in varying layouts (use of a table data region versus a matrix data region, etc.), and so forth. As we have mentioned already, the reports provide an excellent starting point from which to build a more “environment – sensitive set,” and we will pose a simple scenario where this is just the action we take.

Let’s say that, upon review of the Execution Log reports we have uploaded, the client information consumers group with whom we are working are excited with the results we have been able to obtain so quickly. Almost immediately, requests for modifications are communicated, but, then, that was just the reaction we had hoped for: this sort of feedback will get us to the ultimate report destination far faster than beginning with a blank drafting board, and asking the group (a team with only minimal exposure to Reporting Services in the first place) to describe “everything they wish to see in a performance / access report based upon the Execution Log.”

One of the first requests is for a modest set of changes to a report that has met with immediate popularity: the Today’s Reports report. The information consumers tell us that, with the addition of 1) a report type (report, snapshot, etc.) and 2) the physical location of the report (i.e., the folder within which it is stored), Today’s Reports will be perfect to fit a current requirement. While more elaborate requests will no doubt follow, we agree to make the changes once we confirm our understanding of them.

First, we’ll launch Reporting Services’ Report Designer, found in Microsoft Visual Studio .NET 2003.

1. Click Start.

2. Navigate to the Microsoft Visual Studio .NET 2003 in the Programs group, as appropriate. The equivalent on my PC appears as shown in Figure 17.

Figure 17: Accessing Microsoft Visual Studio .NET 2003

Visual Studio .NET opens in the Design Environment.

3. Select File —> Open from the main menu.

4. Click Project from the cascading menu, as depicted in Figure 18.

Figure 18: Selecting a New Project

The Open Project dialog appears.



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