MSSQL Server Reporting Services : Black Belt Administration: Execution Log Performance and Audit Reports

Tuesday Feb 22nd 2005 by William Pearson
Share:

MSAS Architect Bill Pearson concludes his examination of Execution Log reporting. In this article, we practice the rapid deployment of the core report set and create a sample custom report, to become familiar with basic performance and auditing reporting in Reporting Services.

About the Series ...

This article is a member of the series MSSQL Server 2000 Reporting Services. The series is designed to introduce MSSQL Server 2000 Reporting Services ("Reporting Services"), with the objective of presenting an overview of its features, together with many tips and techniques for real-world use. For more information on the series, as well as the hardware / software requirements to prepare for the exercises we will undertake, please see my initial Database Journal article, A New Paradigm for Enterprise Reporting.

Basic assumptions underlying the series are that you have correctly installed Reporting Services, including Service Pack 1, along with the applications upon which it relies, and that you have access and the other rights / privileges required to complete the steps we undertake in my articles. For details on the specifics of the adjustments necessary to quickly allow full freedom to complete the exercises in this and subsequent articles, as well as important assumptions regarding rights and privileges in general, please see earlier articles in the series, as well as the Reporting Services Books Online.

This article also relies upon sample files that are not automatically installed along with Reporting Services. If the samples have not been installed in, or were removed from, your environment, the samples can be found on the Reporting Services installation CD. We will discuss accessing these files within the steps of our practice session.

About the BlackBelt Articles ...

As we have stated in earlier BlackBelt articles, one of the greatest challenges in writing tutorial / procedural articles is creating each article to be a freestanding document that is complete unto itself. This is important, because it means that readers can complete the lesson without reference to previous articles or access to objects created elsewhere. When our objective is the coverage of a specific technique surrounding one or more components of a report, a given administrative function surrounding all reports, and other scenarios where the focus of the session is not the creation of reports, per se, can be challenging because a report or reports often has to be in place before we can begin to cover the material with which the article concerns itself.

The BlackBelt articles represent an attempt to minimize the setup required in simply getting to a point within an article where we can actually perform hands-on practice with the component(s) under consideration. We will attempt to use existing report samples or other "prefabricated" objects that either come along as part of the installation of the applications involved, or that are readily accessible to virtually any organization that has installed the application. While we will often have to make modifications to the sample involved (we will actually create a copy, to allow the original sample to remain intact), to refine it to provide the backdrop we need to proceed with the object or procedure upon which we wish to concentrate, we will still save a great deal of time and distraction in getting to our objective. In some cases, we will have to start from scratch with preparation, but my intention with the BlackBelt articles will be to avoid this, if at all possible.

NOTE: The practice example we undertake in this article represents an infrequent exception to the "freestanding" objective. To complete the steps we describe in this article, you will need to have prepared for it by completing the steps detailed in the immediately preceding article in the series, Prepare the Execution Log for Reporting.

For more information about the BlackBelt articles, see the section entitled "About the BlackBelt Articles" in BlackBelt Components: Manage Nulls in OLAP Reports.

Overview

We introduced our previous article, Prepare the Execution Log for Reporting, with a discussion about a valuable source of information for performance and auditing analysis, identifying the Report Server Execution Log as a great place to start for this sort of reporting. We noted that the Execution Log captures data specific to individual reports, including when a given report was run, identification of the user who ran it, delivery destination of the report, and which rendering format was used, among other information.

After discussing the nature of Execution Logging in general, we touched upon several of the ways in which it can assist us in understanding the performance of our reports, the actions of users, and a host of other details about the reports we create in Reporting Services. Working within a practice example where we responded to the expressed business needs of a hypothetical group of information consumers, we then performed transformation of the data in the Execution Log to a user-friendly reporting data source. We used the tools provided as samples with the Reporting Services installation to create and populate a MSSQL Server database, noting several of the benefits that would accrue to the information consumers. For the detailed steps we undertook, and to prepare to accomplish the steps of this article, please see Prepare the Execution Log for Reporting.

Our focus in this article will be an examination of some of the uses to which the new Execution Log database might be put. Our examination will consist of hands-on publication of the sample reports provided with Reporting Services as a "starter set;" and then go beyond that set and create a customized report to show the ease with which we might help the information consumers we support to meet general and specific needs. We will propose other considerations that will add value to this already rich resource, and discuss ways in which we can leverage Execution Log reporting to make us better report writers from multiple perspectives.

In this session we will:

  • Review the hypothetical business requirements behind the procedures in this and the preceding article;
  • Review the Execution Log reporting database we created in our last lesson, focusing on its schema and the information it contains;
  • Review possible value-adds that come with Execution Log reporting;
  • Make copies of the sample report set provided with Reporting Services in a convenient location;
  • Upload the sample report copies, for execution within Report Manager;
  • Customize a copy of a popular report, to meet extended business requirements from the information consumers group.

Execution Log Performance and Audit Reports

Objective and Business Scenario

As a basis for our practice examples in both our last and current sessions, we established a Reporting Services business requirement of a group of information consumers at a hypothetical client location. The consumers expressed the need to monitor report activity, for purposes of performance and auditing evaluation, for their recently implemented Reporting Services installation. The consumers asked that we work with them to create a database that is populated and maintained with this data for many potential uses. We accepted the project, informing the group that, while we had created numerous relatively elaborate databases of this sort for clients in the past, our initial pilot in this instance would include Execution Log reporting capabilities that could be implemented rapidly and easily, using the tools that accompany the installation of Reporting Services.

We made it clear that we would likely expand the capabilities to many other measures for overall performance and auditing monitoring after our pilot. We explained that beginning with the prefabricated "starter set" would be efficient, because Reporting Services provided a script to construct a basic reporting database to house the Execution Log data that it generates. In addition to the creation script, a DTS package to transform the data, and to populate (and prospectively update) the new database, was also included. Finally, a set of basic sample reports were included in the same "extras" bundle, which we could run as soon as we had transformed data from the Execution Log to the newly created database.

We pointed out that we had found this approach to be a "quick win" with regard to getting basic functionality in place; we also indicated that this was a great time in the Reporting Services implementation to be making performance and audit analysis capabilities available - just as we were beginning to write our first enterprise reports. The sample reports would serve several complementary, value-added functions, including:

  • Provision of a means for the Administrator to immediately see who was performing what actions within the new system;
  • Provision of an excellent learning tool for new report authors, whereby they could obtain confirmation that their new reports had executed;
  • Presentation of basic report samples, upon which the ultimate users could base requests for customization. (This could save a great deal of time in obtaining the requirements from users who, although they knew they needed these reports, may not have known "where to start" in providing a useful specification for their design.)
  • Facilitation of optimization of reports and their underlying queries, both at times during and after creation, and at various points in time as a part of ongoing system upkeep and maintenance (tuning could be suggested, for example, as data sources grew in size, as the number of information consumers increased, as security needs changed, and so forth).

With the Execution Log data source now in place, we are ready to proceed with aligning and publishing the sample report set to kick off the new reporting efforts. Included on the Reporting Services installation CD is a group of sample Execution Log reports. These reports include the following titles:

  • Longest Running Reports
  • Report Parameters
  • Reports by Month
  • Reports by User
  • Reports Executed by Day
  • Report Size
  • Report Success Rate
  • Today's Reports

The sample files also include the Visual Studio project (.rptproj) and solution (.sln) files for the report set, together with a data source connection file (.rds). The reports and objects are designed to be used with the Execution Log database we created and populated in our last session. A database diagram of the database is shown in Illustration 1.


Illustration 1: Simple Database Diagram (MS Visio) of the New Reporting Database

The Execution Log Database

We discussed reasons for creating a reporting database as opposed to simply using the Execution Log in its original state in our previous session. We then opened and executed the provided table creation script, using MSSQL Server Query Analyzer, to create the schema for our new reporting database. We then loaded and executed the accompanying DTS package to transform the Execution Log data and populate the new database tables.

The information contained in the transformed data includes details that can assist us in determining report efficiency and frequency of usage, together with myriad details that assist in resource planning, monitoring the activities of users, and so forth. Specific data items that we can obtain from our Execution Log database include:

  • The name of the Report Server Instance that handled a given request;
  • The Report Identifier;
  • The User Identifier;
  • The Request Type (either User or System);
  • The Rendering Format of the report;
  • Any Parameter values used for the execution of a given report;
  • The Start and Stop times of a given report process, from which duration is derived;
  • The Percentage of Processing Duration spent retrieving the data, processing the report, and rendering the report;
  • The Source (including Live, Cache, Snapshot, or History) of report execution;
  • Status of Processing for a given report (indicating either a successful process, or the code of the first Error Condition encountered);
  • The size of each generated report.
  • The number of rows returned from query(ies) underlying each report.

Considerations and Comments

Before we can work with the sample reports, we need to take several preparatory steps to establish connectivity. As part of our setup, we will create a Data Source Connection, then upload the report files we have relocated to a convenient location, as well as performing various read, and other, actions with the sample reports. We will also be modifying a copy of a report to customize it to fit a specific consumer need, to get some practice in creating new capabilities within our report set, as well as performing other actions within Reporting Services.

To complete the procedures we will undertake, you will need to have the appropriate access and privileges, at the MSSQL Server 2000 level, and within Reporting Services, MSSQL Server Analysis Manager, and the file system, to perform the respective actions. You will also need access to the Reporting Services installation CD, from when we will be copying the sample report files to our local drive.



Hands-On Procedure

Preparation

Creating and Populating a Folder with the Components We Will Need



To prepare for our session with the Execution Log sample reports, we will create a folder to house the reports, along with other objects that we will need to complete our practice exercises. The folder can be created anywhere on the PC that is convenient from a local perspective.



1.  Right-click Start.

2.  Select Explore to launch Windows Explorer.

3.  Navigate to a convenient place to create the folder that will contain the "collateral" for our practice session.

4.  Create a folder named as follows:

RS014

The folder appears similar to that depicted in Illustration 2.




Illustration 2: New Folder in Place to House Lesson Components

5.  Locate the Reporting Services installation CD on an accessible CD drive.

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

F:\extras\Execution Log Sample Reports

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


Illustration 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 RS014 folder we created above.

10.  Right-click within the folder.

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


Illustration 4: Select Paste to Place the Sample File Copies

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


Illustration 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, as we learned in my Database Journal article Managing Reporting Services: Data Connections and Uploads. We will use Report Manager, versus the Report Designer interface, for this purpose, but keep in mind that the upload / publication process can also be managed from within Report Designer.

Creating a Data Source Connection

We learned in Managing Reporting Services: Data Connections and Uploads that 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 represent 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.

As we have noted in various articles of the series, a data source connection can be embedded in a report (where it is typically defined within the creation process); it can also 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 Prepare the Execution Log for Reporting.

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 Illustration 6.


Illustration 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:

 http://<webservername>/reports

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 Illustration 7.


Illustration 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 Illustration 8.


Illustration 8: The New Folder Page, with Input

7.  Click OK.

We are returned to the Home page Folder View, and see our new folder appear on the Contents tab, as shown in Illustration 9.


Illustration 9: The Execution Log Reports Folder, on the Folder View - Contents Tab

8.  Click the link for the new Execution Log Reports folder, to open it.

We enter the Execution Log Reports folder, which is empty at present.

9.  Click New Data Source atop the Folder View, as depicted in Illustration 10.


Illustration 10: Click New Data Source atop Report Manager Folder View

The New Data Source page appears.

10.  Type the following into the Name box:

RSExecutionLog

11.  Type the following into the Description box:

Shared Data Source for Execution Log Reporting

12.  Ensure that the checkbox to the left of Enable this data source is checked (the default).

13.  Ensure that Microsoft SQL Server is selected in the Connection Type selector.

14.  Type the following into the Connection String text box:

data source="(local)";persist security info=False;initial catalog=RSExecutionLog

15.  Under Connect Using, select Windows NT Integrated Security.

NOTE: Adapt the Data Source and authentication settings to your own environment, as necessary (for example, if the RSExecutionLog database is on another server and access is being attempted across a network, etc.)

With our input, the New Data Source page appears as shown in Illustration 11 below.


Illustration 11: The New Data Source Page, with Input

16.  Click OK to accept the shared data source.

We are returned to the Execution Log Reports page, Folder View, and see our new data source appear on the Contents tab, as depicted in Illustration 12.


Illustration 12: The Data Source Appears on the Folder View, Contents Tab

Having created a data source, we will now upload the sample Execution Log reports from the Report Manager. We will then marry the reports to the common data source we have created.

Procedure

Uploading the Reports

We will perform the upload of the sample Execution Log reports, by taking the following steps within Report Manager:

1.  Click Upload File atop the Folder View, Content tab.

The Upload File page appears.

2.  Click Browse.

Navigate to the location of the sample reports, folder RS014, which we created and populated in the first part of our practice procedure above.

Folder RS014 appears in the Choose File dialog, once located, as shown in Illustration 13.

Click for larger image

Illustration 13: The Target Folder Appears in the Choose File Dialog

3.  Click the RS014 folder to open it.

The sample report definition files (.rdl's) and other object files appear. At this point, we will select the Today's Reports report definition files.

4.  Click the todays reports.rdl file, as depicted in Illustration 14.


Illustration 14: Selecting the Today's Reports File from among the Sample Set

5.  Click the Open button on the dialog.

6.  Change the default name in the Name box to the following:

Today's Reports

The todays reports.rdl file, along with modified title, appears in the respective boxes of the Upload File page, as shown in Illustration 15.


Illustration 15: The Today's Reports File - Selected for Upload

7.  Click OK.

The report file uploads. We can see it appear listed on the Folder view, Content tab, where we saw the new data source appear earlier.

8.  Repeat steps 1 through 7 above, for each of the following files in the RS014 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 Illustration 16.


Illustration 16: The Execution Log Reports Appear in the Folder View - Content Tab

Having uploaded the seven reports listed above, we can click on each to ascertain that the report is executing properly. Should any connectivity issues emerge, see Testing the Reports - and Resolving Data Source Issues in my article Managing Reporting Services: Data Connections and Uploads, as well as the Books Online, for guidance.

9.  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.

10.  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 will 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 Illustration 17.


Illustration 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 Illustration 18.


Illustration 18: Selecting a New Project

The Open Project dialog appears.

5.  Navigate to the executionlog.rptproj file, which we copied into the RS014 folder we created earlier, as shown in Illustration 19.


Illustration 19: Navigate to the Execution Log Sample Reports Project File

6.  Click the executionlog.rptproj file to select it.

7.  Click Open to open the project file.

The project file opens. Let's make a quick clone of the report file we want to modify, so as to keep the other samples intact for now. (I like to keep a "pristine set" around for just such situations as this).

8.  In the Solution Explorer, right-click the Reports folder.

9.  Select Add --> Add Existing Item from the cascading menus, as depicted in Illustration 20.

Click for larger image

Illustration 20: Select Add --> Add Existing Item in Solution Explorer (Partial View)

The Add Existing Items dialog appears.

10.  Navigate to the RS014 folder.

11.  Right-click the todays reports.rdl file, within the RS014 folder.

12.  Select Copy from the context menu that appears, as shown in Illustration 21.


Illustration 21: Copy the File in the Add Existing Item Dialog (Partial View) ...

13.  Right-click in the white space to the right of the files in the same dialog.

14.  Select Paste from the context menu that appears, as depicted in Illustration 22.


Illustration 22: Select Paste to Place the Report File Copy ...

A report titled Copy of todays reports.rdl appears in the folder.

15.  Right-click in the Copy of todays reports.rdl file.

16.  Select Rename from the context menu that appears, as shown in Illustration 23.


Illustration 23: Select Rename from the Context Menu ...

17.  Type the following into the write-enabled file title:

Custom_Todays_Reports.rdl

18.  Click outside the title to accept the modification / close the Edit capability.

The renamed report file clone, Custom_Todays_Reports.rdl, appears in the Add Existing Item Dialog, as depicted in Illustration 24.


Illustration 24: The Report Clone Appears (Circled) in the Dialog

19.  Double-click Custom_Todays_Reports.rdl to select and add it to the project.

The report file appears in the Solution Explorer, as shown in Illustration 25.


Illustration 25: The Report Clone Appears (Circled) in the Solution Explorer

20.  Double-click Custom_Todays_Reports.rdl in the Solution Explorer to open it in Layout view.

The report file opens, and appears on the Layout tab, as depicted in Illustration 26.


Illustration 26: The Report File Opens in Layout View

At this stage, we need to add the data fields Report Type and Path (the file system location for the report file) to the DataSet. To do so, we will modify the DataSet on the Data tab.

21.  Click the Data tab.

22.  Click the Graphical Query Designer button, as necessary, to shift to the graphic designer, which consists of a toolbar and four panes, as shown in Illustration 27.

Click for larger image

Illustration 27: Graphical Query Designer (Compact View) - with "Shift Button" Circled

23.  Right-click in the white space to the right of the tables, within the Diagram pane.

24.  Select Add Table ... from the context menu that appears, as depicted in Illustration 28.


Illustration 28: Select Add Table ...

The Add Table dialog appears, defaulted to the Tables tab.

25.  Select Report Types from the tables listed in the dialog, as shown in Illustration 29.


Illustration 29: Select the Report Types Table ...

26.  Click Add.

27.  Click Close, to dismiss the Add Table dialog.

The Report Types table appears, along with the other tables, in the Diagram pane.

28.  In the Report Types table, click the checkbox to the immediate left of Name, to select the field into the existing query.

29.  In the Grid pane (the pane containing rows and columns, just below the Diagram pane), type the following into the Alias column of the grid for the Name row (likely the bottom row in the Grid pane), to replace the default entry "Expr1:"

Type

30.  In the Reports table, click the checkbox to the immediate left of Path, to select the field into the existing query.

The Diagram and Grid panes appear, with our selections / additions, as depicted in Illustration 30.


Illustration 30: Our Selections and Additions in the Diagram and Grid Panes (Circled)

Now, let's go to the Layout tab and make the additions to the report layout.

31.  Click the Layout tab.

32.  Click the box containing the words "Total Executions."

The gray column and row headers for the table data region appear.

33.  Right-click the column header above the box containing the words "Total Executions."

34.  Select Insert Column to the Right from the context menu that appears, as shown in Illustration 31.


Illustration 31: Inserting a Column into the Table Data Region

The new column appears.

35.  Type the following into the top text box of the new column:

Type

36.  Click the Type field in the Fields List of the Fields window.

37.  Drag the field to the bottom row of the new column we created, and labeled Type above.

38.  Drop the field into the textbox.

The field appears in the bottom textbox of the new Type column, as depicted in Illustration 32.


Illustration 32: The Type Field Appears in the New Type Column

NOTE: If the Fields window is not initially visible in the Report Designer, select View --> Fields from the main menu to resurrect it.

39.  Right-click the column header for the column we just added above, now labeled Type. (Click anywhere in the column to make the header reappear, if necessary).

40.  Select Insert Column to the Right from the context menu that appears, once again.

The new column appears.

41.  Type the following into the top text box of the new column:

Location

42.  Click the Path field in the Fields List of the Fields window.

43.  Drag the field to the bottom row of the new column we created, and labeled Location above.

44.  Drop the field into the textbox.

The field appears in the bottom textbox of the new Location column as shown in Illustration 33.


Illustration 33: The Path Field Appears in the New Location Column



Test the New Custom Audit Report

Let's test the new report by moving to the Preview tab.

1.  Click the Preview tab.


2.  Select a date from the Select Date parameter selector atop the Preview tab.


NOTE: Keep in mind that the DTS update script we ran in our previous article to populate the Execution Log reporting database (also created in our last article) will need to be rerun to update the tables for any execution activity that has transpired since the last update. The DTS update script can, of course, be scheduled to do this at predetermined intervals (such as overnight, or far more frequently), but the updates will obviously determine the dates that appear in the parameter selector, as the picklist is populated via a query against a table in the database.



3.  Click the View Report button to execute the report.

The report executes, and the preview, similar to the one depicted in Illustration 34 appears.




Illustration 34: Inserting a Column into the Table Data Region

4.  Expand ("drill down") on a line item in the report by clicking the "+" sign to its left.

5.  Repeat the drilldown at the next level.

The report, with a selected line item expanded, appears similar to that shown in Illustration 35.


Illustration 35: Report Preview, with Expanded Line Item

Our new fields appear at the proper levels, and we see that we have met the requirements of the information consumers.

Far more customization can be accomplished with the sample report set, as most of us can appreciate, to add / rearrange various data fields that originate in the Execution Log. Moreover, numerous enhancements can be made to further refine the reports (an example might be to exclude the Execution Log audit and performance reports themselves from selection within our report structures, to allow for focus upon actual production reports, etc.).

From the perspective of data upon which we can report, the information housed in the Execution Log is only the beginning. Performance, auditing, and other data elements, from a host of other sources (including web servers, the Windows Event Log, trace files, and many others) can be added to a central "administration" database for a host of uses. We have only to create DTS tasks to move the data from the repositories that house them, and to bring them into our central reporting data source, to provide an integrated view of many aspects of our business intelligence operations. (I will focus on various administrative reporting scenarios in prospective articles, where I will share some of the administration / metadata reporting databases I have created for current and past clients.) The complementary power of the MSSQL Server components can be leveraged to produce virtually any data source we might require, with a little forethought and know-how. Reporting Services can then present that data in virtually any layout we can envision, and deliver it through numerous channels to the appropriate knowledge workers.

6.  Select File --> Save All to save all work to this point.

7.  Exit Visual Studio .NET when desired.

Summary and Conclusion ...

In this article, we continued the examination of Execution Log performance and audit reporting begun in the previous article of the series, Prepare the Execution Log for Reporting. Our focus here was an examination of some of the uses to which the Execution Log database, created in the previous session, might be put. We began this article with a review of the steps we took to prepare the data contained in the Execution Log for reporting, and then moved into a hands-on practice session with the sample reports provided with Reporting Services as a "starter" set.

After discussing multiple benefits that the sample reports can provide, and after reviewing the business requirements of a hypothetical group of information consumers (which dictated the steps we took in the practice session that followed), we prepared our environments for our practice exercises. Next, we uploaded copies of the sample reports, to see Reporting Services in action with the Execution Log data we had transformed in the previous article.

We then created a custom report, in accordance with specifications received from the information consumers based upon their initial interaction with the published reports. Once the required additions were made, we verified the effectiveness of our customized report by viewing it in Report Designer, ensuring that it returned data as expected. Throughout the session we commented on relevant considerations as they arose, noting the results we obtained for each step we took in the practice examples, as well as commenting on ways to extend the Execution Log data source for other uses.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Reporting Services Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved