Optimizing Microsoft SQL Server 2000 Reporting Services: Performance and Access Reports from the Execution Log
3. Click the View Report button to execute the report. The report executes, and the preview, similar to the one depicted (but wholly dependent upon the activity of your local reports, of course) in Figure 34 appears.
Figure 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 that shown in Figure 35.
Figure 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. The report is now ready for publication, via deployment in the Report Designer, or via upload in Report Manager (the way we published the sample reports in the earlier part of our practice session).
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.
12. Select File —> Save All to save all work to this point.
13. Exit Visual Studio .NET when desired.
In this article, we continued the examination of Execution Log performance and access reporting begun in the previous article of the series, Execution Log Reporting: Preparation as a Data Source. 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.