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.