Optimizing Microsoft SQL Server 2000 Reporting Services: Performance and Access Reports from the Execution Log
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 Figure 30.
Figure 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 textbox 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 Figure 31.
Figure 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:
36. Click the Type field in the Fields List of the Fields window.
NOTE: If the Fields window is not initially visible in the Report Designer, select View —> Fields from the main menu to resurrect it.
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 Figure 32.
Figure 32: The Type Field Appears in the New Type Column
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:
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 Figure 33.
Figure 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 (overnight or 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.