dcsimg

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

February 22, 2005



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.

MSSQL Server Reporting Services
Introducing the Tablix Data Region: Basic Grouping Concepts
Introducing the Tablix Data Region in Reporting Services 2008
100% Stacked Column Chart for Analysis Services Data
XY (Scatter) Chart for Analysis Services Data
Simple Doughnut Chart for Analysis Services Data
Exploded Pie Chart for Analysis Services Data
Stacked Bar Chart for Analysis Services Data
Line Chart for Analysis Services Data
Stacked Column Chart for Analysis Services Data
A More Advanced Pie Chart for Analysis Services Data
Simple Pie Chart for Analysis Services Data
Simple Bar Chart for Analysis Services Data
Simple Column Chart for Analysis Services Data
Introducing Reporting Services Charts for Analysis Services
Mastering OLAP Reports: Parameterized Grouping
Mastering OLAP Reports: Parameterizing Number of "Top" Items with the MDX TopCount() Function, Part II
Mastering OLAP Reports: Parameterizing Number of "Top" Items with the MDX TopCount() Function, Part I
Mastering OLAP Reports: Parameterizing Number of "Look Back" Periods with the MDX LastPeriods() Function, Part II
Mastering OLAP Reports: Parameterizing Number of "Look Back" Periods with the MDX LastPeriods() Function, Part I
Support Parameterization from Analysis Services - Parameter Defaults
Parameterization from Analysis Services - Cascading Picklists
Support Parameterization from Analysis Services
Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets
Reporting Services: Customize Automatically Created Parameter Support Objects
Snapshot Reports II: SQL Server Management Studio Perspective
Snapshot Reports I: Report Manager Perspective
Report Execution Caching II: Report Manager Perspective
Report Execution Caching I: SQL Server Management Studio Perspective
Report Session Caching in Reporting Services 2005
Black Belt Administration: Reporting Services Configuration Manager
Intelligent Layering: Leverage Conditional Formatting Logic from Analysis Services
Black Belt Administration: Performance Dashboard for Microsoft SQL Server, Part II
Black Belt Administration: Performance Dashboard for Microsoft SQL Server, Part I
Mastering OLAP Reports: Extend Reporting Services with Custom Code
Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. II
Black Belt Components: Support Simple Navigation with a Document Map
Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I
Black Belt Components: Interactive Sorts within a Matrix Data Region
BlackBelt Authoring: Conditional Drillthrough to Multiple Reports
Mastering OLAP Reporting: Prototype KPIs in Reporting Services
BlackBelt Administration: Linked Reports in SQL Server Management Studio
BlackBelt Administration: Linked Reports in Report Manager
Mastering OLAP Reporting: Reporting with Analysis Services KPIs
Report Builder: Creating a Report Model
Mastering OLAP Reporting: Meet Business Needs with Matrix Dynamics, Part II
Mastering OLAP Reporting: Meet Business Needs with Matrix Dynamics, Part 1
Mastering OLAP Reporting: Display a Dataset Field in a Report Page Header
Interactive Sorting Within Reporting Services
MSSQL Server Reporting Services: Mastering OLAP Reporting: Multiple Value Selection in a Parameter Picklist
MSSQL Server Reporting Services : Mastering OLAP Reporting: Drilling Through Using MDX
MSSQL Server Reporting Services: Mastering OLAP Reporting: Relationally-Based Picklists for OLAP Reporting
MSSQL Server Reporting Services: Mastering OLAP Reporting: Extending Conditional Formatting: SWITCH and Drilldown Defaults
MSSQL Server Reporting Services: Mastering OLAP Reporting: Percent of Total - Chart Presentation Nuances
MSSQL Server Reporting Services: Mastering OLAP Reporting: Percent of Total - Two Perspectives
MSSQL Server Reporting Services : Mastering OLAP Reporting: Ad Hoc TopCount and BottomCount Parameters
MSSQL Server Reporting Services: Black Belt Components: Ad Hoc Sorting with Parameters
MSSQL Server Reporting Services: Black Belt Administration: "Governor" Capabilities: Report Execution Timeout
MSSQL Server Reporting Services : Black Belt Administration: Execution Log Performance and Audit Reports
MSSQL Server Reporting Services: Black Belt Administration: Prepare the Execution Log for Reporting
MSSQL Server Reporting Services: Black Belt Components: Ad Hoc Conditional Formatting for OLAP Reports
MSSQL Server Reporting Services : Black Belt Components: Manage Nulls in OLAP Reports
MSSQL Server Reporting Services: Reporting Services Basics: Create a Reusable Template Report
MSSQL Server Reporting Services: Master Chart Reports: Track Exchange Rates in a Line Chart
MSSQL Server Reporting Services: Master Chart Reports: Pie Charts in Reporting Services
MSSQL Server Reporting Services: Mastering OLAP Reporting: Cascading Prompts
MSSQL Server Reporting Services: Managing Reporting Services: Data-driven Subscriptions, and External Data Sources for Subscriber Data
MSSQL Server Reporting Services: Managing Reporting Services: Report Execution and Standard Subscriptions
MSSQL Server Reporting Services: Managing Reporting Services: Data Connections and Uploads
MSSQL Server Reporting Services: The Authoring Phase: Overview Part II
MSSQL Server Reporting Services: The Authoring Phase: Overview Part I
MSSQL Server Reporting Services: A New Paradigm for Enterprise Reporting








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers