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