Manage data connections and upload
new reports, as Architect Bill Pearson introduces the Management phase of reporting.
About the Series ...
This is the fourth article 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 giving a preview of its features, as well as sharing my conviction
in its role as a new paradigm in enterprise reporting. As I advise clients on a
more and more frequent basis these days, this is the future in a big way. I hope
you will consider my input valuable, and that you will investigate closely the
savings and advanced functionality that will soon be available to anyone with
an MSSQL Server 2000 (and beyond) license.
Important: For information concerning the applications to which you will
require access to benefit the most from our series, please see our initial Database
Journal article, A New
Paradigm for Enterprise Reporting.
last article, The
Authoring Phase: Overview Part II, we resumed where we left off in
I of our Reporting Services Authoring phase overview. We
undertook the remaining steps of our initial walkthrough of the Authoring
phase within the context of an example tabular report. We completed many steps
within our continuing practice example, including the performance of numerous
modifications and enhancements to the layout of the report. We then filtered
the dataset, and performed grouping and sorting within the table. We added
subtotals and a total, using the group and report
footers in their respective placement. Finally, we set formatting and other
properties within our example report, to introduce the substantial population
of options for increasing usefulness and appearance of a report within Reporting
this article, we will introduce the Managing phase of the reporting
lifecycle within the context of Reporting Services. Here, as well as in
subsequent articles, we will examine our options for central management of the
reporting function, from the perspective of the objects that we can manage and
the actions that we can perform with, and upon, those objects. Our examination
will surround managing components that include:
- Data Source Connections
- Report Processing and Maintenance
- Report Scheduling
- Report Server Content
- Control of User Access
- Report Distribution
objective of this article is to begin our overview of Managing. We will
return to most of the activities we touch upon here, just as we will to many of
the topics we explore within our other life cycle overviews, as we get involved
in creating reports to accomplish illustrative business needs. As I stated in
the Authoring overview, I intend to make this a series on enterprise
reporting in the widest sense. Before the release of Reporting Services,
when the proprietary "Big Sisters" offered the only real options for
enterprise reporting, we would have had to introduce multiple tools to
accomplish sophisticated solutions in many cases. At last, I have a unified, common
platform from which I can show the techniques and methods I have accumulated
over the years to support robust and creative business intelligence.
Introduction to the Managing Phase
learned in the previous two-part article, The
Authoring Phase: Overview,
that a report definition is created by a developer via the Report
Designer (or, optionally, another authoring tool that leverages the Reporting
Services API). We overviewed connection, query, layout and other aspects
of the design process, within the creation of a tabular report as a practice
exercise. After Authoring, the next phase in the report development
life cycle, at least for reports that are managed in the first place, is
Managing, where the report is processed and rendered.
As we might imagine,
there are many variations and options in the steps involved, but, in general, publication
of the new report definition to a Report Server designates it as a managed
report. In addition to being saved on a Report Server, managed
reports are associated with meta data, and have properties, that
allow actions to be taken with them, including:
linking to other reports
- application of security
movement to various locations
renaming, deletion and other
We can think of
processing as taking place in the following general sequence:
Extraction of the data as
specified in the definition (rdl)
The marriage of the data to the
report layout we have defined
The rendering of the output, as
an information product in a desired presentation format.
Once the report is
generated (and this can be arranged to happen on a schedule we establish, or
upon demand by an information consumer, or even both), the report can be delivered
to, or accessed by, consumers in a number of ways. We will discuss these
options, and some of the processes involved in their operation, in a subsequent
article that focuses on report access and delivery.
article, we are going to get a good look at the centralized management of
reporting that becomes a reality with Reporting Services. In the middle of it
all is the Report Manager, with which we will become familiar over this
and subsequent articles. We already mentioned, in the introductory article to
this series, that reports can be uploaded to the report server easily
from either the Report Designer or the Report Manager; we also
noted that the reports can be viewed thereafter via a web browser. Multiple
types of security can be assigned during the upload process, as well as
elsewhere, for welcome (and rare) flexibility. We will explore these and other
features, as well as the degrees of management and administration
considerations that make the overhead involved with Reporting Services widely
customizable to an organization's needs. We will even examine some aspects of report
server administration within the context of management, providing a preview of
more detailed examinations that we will undertake with this component in
started with our examination of the Managing phase and get a taste of
the future that is today with Reporting Services. Because this article
focuses on management of reports that are already designed (and in
keeping with our objective to make articles "free-standing" with
regard to readers being able to participate in each without having joined us in
previous articles), we will work with the set of report samples that accompany Reporting
Accessing the Sample Reports
To use the
sample reports, we must first install them as a part of Reporting Services
Setup. When we installed Reporting Services, we were given this
opportunity, with the default installation point for the files being the Samples
folder within the Reporting Services program folder. A common example of this
default path is as follows:
C:\Microsoft SQL Server\MSSQL\Reporting Services\Samples\Reports
documentation for the sample reports (search on "Samples" in the Reporting
Services Books Online) informs us that, although the reports are installed,
they are not deployed automatically to the Report Server. Since our Managing
phase articles concern themselves with this process, in part, and with managing
reports that are already created in general, the sample reports present an
excellent vehicle by which we can focus on managing, and avoid the diversion of
designing considerations. The MSSQL Server 2000 Reporting Services series will
afford us, as it progresses, many opportunities to design and write reports
that are targeted to specific business needs. Check back frequently to see how
I meet real world requirements, within illustrative contexts that have a
particularly practical flavor.
If you have already begun
to explore managing reports, and have deployed some or all of the samples by
manually uploading, using scripts, or publishing via Report Designer, we
can still use the sample files in our practice exercises. Simply rename
copies, locate them differently, or whatever other action fits your local
constraints, and perform the steps along with the rest of us, with your
variations in mind as you publish, save, and so forth.
Note: If you did not choose to
install the samples during Setup, it might be a good idea to do so at
this point, keeping in mind, as you run Setup again, the location to
which you choose to direct them. The Samples shortcut that is normally
installed in the Reporting Services program group of the Start menu,
along with the shortcut to the Report Manager URL in the browser, can be
useful, and we will refer to it occasionally within the series. This makes the
location of the files a little less of a memory chore, but we can certainly
customize our manner of arriving at these destinations in any way we choose.
upload the samples as a part of achieving the objectives of the practice exercises
in this lesson, as well as in preparation for exercises we will share in future
Managing articles. We will use Report Manager versus the Report
Designer interface, but rest assured we will get experience from loading
with the latter of the two options many times, as the series evolves.
a Data Source Connection
The purpose of the Report
Server is to serve, or to act as a presentation platform for, our
reports. An important element of its function is that 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, and with which most of us are
familiar. Data sources contain the following, where applicable:
of the data processing extension we use to process queries of the type for
which we intend to use the connection [ex's]
string that allows us to locate the source
credentials involved in allowing us to read the data in the source
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. When the description of the data source
is embedded in the report, the data source is referred to as report-specific.
The connection information is internal to the report, and can therefore be used
only by the report that houses it. As we might expect, we modify
report-specific connections at the report level.
Frequently used data
sources can be represented by shared data source items, which we can use
as a data source connection in any report for which we need to access
the underlying data source. Shared data sources are self-contained,
and can be referenced by many reports with the identical data source. Shared
data sources can be maintained from the central location of the item itself,
independently of the reports, which, along with their obvious reusability, make
shared data source items a popular approach.
will create a shared data source for the reports before we upload them,
else there will be no mechanism to link them to the data they are intended to present.
In the following steps, we will establish a data source connection independent
of the reports themselves. Our set of sample reports will share data housed in
a single source, the AdventureWorks2000 sample database (which comes
along in the typical installation of Reporting Services), so this provides an
excellent opportunity to illustrate the appropriate use of a shared data source.
first step is to start Report Manager, then to create a shared data
source in lockstep with uploading our reports.
the Reporting Services program group that installs within a
typical setup. The equivalent on my PC appears as shown in Illustration 1.
Illustration 1: Navigate
to Report Manager ...
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:
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 depicted in Illustration 2.
Illustration 2: Navigate
to Report Manager ...
arrive at the Report Manager Folder View, as shown in Illustration
Illustration 3: Report
Manager Folder View
Data Source atop the Folder View.
Data Source page appears.
following into the Name box:
sample reports are based upon data in the sample MSSQL Server 2000 database, AdventureWorks2000,
which is created within the typical Reporting Services installation.)
following into the Description box:
AdventureWorks2000 shared data source for sample reports
the checkbox to the left of Enable this data source is checked (the
SQL Server in the Connection Type selector.
following into the Connection String text box:
data source="(local)";persist security info=False;
NOTE: Adapt the above to your own environment if necessary (for
example, if the AdventureWorks2000 database is on another server and
access is being attempted across a network, etc.)
Using, select Windows NT Integrated Security.
our input, the New Data Source page appears as depicted in Illustration
Illustration 4: The
New Data Source Page, with Input
to accept the shared data source.
returned to the Folder View, and see our new data source appear on the Contents
tab, as shown in Illustration 5.
Illustration 5: The
Data Source Appears on the Folder View, Contents Tab
created a data source, we will now get some practice with uploading reports
from the Report Manager. We will then marry the reports to the common
data source we have created.
will perform the upload of the sample reports set that accompanies Reporting Services to gain hands-on practice with the process, as well
as to provide reports upon which to base other managing activities in this and
File atop the Folder View, Content tab.
File page appears.
to the location of the sample reports (again, the Samples folder is installed
within the Reporting Services program folder by default. A common
example of this default path appeared in the "Accessing the Sample
Reports" section above.)
folder appears in the Choose File dialog, once located, as depicted in Illustration 6.
Click the Samples
folder to open it.
Click the Reports
folder that appears, to open it.
sample report definition files (.rdl's) and other object files
appear. At this point, we will select the Company Sales report
Click the Company
Sales.rdl file, as shown in Illustration 7.
Illustration 7: Selecting
the Company Sales.rdl File from among the Sample Set
Click the Open
button on the dialog.
Sales file, along with default title (which we can change), appear in the
respective boxes of the Upload File page, as depicted in Illustration
Illustration 8: The
Company Sales Report - Selected for Upload
NOTE: If you have already uploaded one
or more of the sample reports, you can choose to overwrite report(s) of the
same name that have been uploaded, or simply give the new report(s) new name(s),
or place it / them within a new folder you can create on the fly.
report file uploads. We can see it appear listed on the Folder view, Content
tab, where we saw the new data source appear, as depicted in Illustration
Illustration 9: The
Uploaded Company Sales Report Appears in the Folder View - Content Tab
Repeat steps 1
through 7 above for the following files in the Sample Reports
folder, from which we uploaded Company Sales.rdl:
- Employee Sales Summary.rdl
- Foodmart Sales.rdl
- Product Catalog.rdl
- Product Line Sales.rdl
- Sales Order Detail.rdl
- Territory Sales Drilldown.rdl
Testing the Reports - and Resolving Data Source Issues
Having uploaded the seven reports listed above, let's perform a test of the first, Company Sales, to ascertain that it is working properly. This offers us an opportunity to experience a data connection problem, and then to resolve it. The same process will need to be accomplished for each of the uploaded reports experiencing the issue.
9. From the Home page Folder view, Contents tab, click the Company Sales report we uploaded first in the previous section.
We arrive at the Company Sales page, View tab, but are confronted with a Reporting Services Error message, as seen in Illustration 10.
NOTE: If no error was received, and if data appears, close the report and perform the connectivity test on the remaining reports, returning to the steps that follow if, and when, an error occurs.
10. Click the Properties tab of the page to see properties for the Company Sales report.
The Properties tab assumes the focus.
11. Click the Data Sources link at the left side of the page.
The Data Sources page appears, indicating the cause of the error, as shown in Illustration 11.
Illustration 11: Data Source Page, Indicating Problem
The next page that appears gives us an opportunity to remedy the missing data source issue. A box exists within which we can name an alternative source; we also see a tree from which we can select a data source with which to marry the report under consideration, as shown in Illustration 12.
Illustration 12: Options for Associating the Report with the Data Source
Let's correct the problem by naming the replacement data source. Regardless of the number of data sources you may see here (what you see on your local PC will vary if you have set up other data sources, before, etc.), we will select the AdventureWorks2000 data source we created earlier, located in the Home folder, as depicted in Illustration 13.
Illustration 13: Select the AdventureWorks2000 Data Source
13. Click AdventureWorks2000 in the tree, under the Home folder.
We see AdventureWorks2000 populate the Location box, as shown in Illustration 14.
Illustration 14: AdventureWorks2000 Populates the Location Box
14. Click OK to accept our specifications.
We arrive at the initial Data Source property page, where we see that the shared data source is designated.
15. Click Apply at the bottom of the page to apply the new data source designation.
16. Click the View tab at the top left of the Properties page.
NOTE: Be sure to always apply before navigating to the View tab - there is no "are you sure?" message if we skip this step.
The Company Sales report appears, as shown in Illustration 15.
Illustration 15: The Company Sales Report - Connection Established
We see that we have established a data connection to the report.
17. Perform steps 9 through 16 above for each newly uploaded report, as required to establish connectivity.
The Folder view of the Report Manager will appear similar to that shown in Illustration 16, once we have uploaded the reports we listed earlier.
Illustration 16: Report Manager - Folder View, Displaying Uploads
Keep in mind that uploading can happen in other ways: the most common method, outside the confines of the Report Manager, is via the Report Designer, which we visited from a fairly high level in our initial articles, and to which we will no doubt return in upcoming articles of the series.
18. Close the browser, after experimenting with the various reports, as desired.
Summary and Conclusion ...
In this article, we introduced Managing as a phase of the Reporting Services development life cycle. We discussed the focus of this and subsequent Managing articles, and then took a look at data connections within Reporting Services. We set up a shared data source item for the sample reports set. We then practiced the upload process with each of the sample reports, to gain familiarity with the steps involved.
Next, we associated the sample reports with the new shared data source item, with the tandem benefit of returning the reports to a working status, and of practicing a method of re-establishing connections to a data source, in cases where a previous connection has been deleted or altered. Finally, we practiced the verification of data connections through initial report viewing, ensuring that each brought back data as expected.
» See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Reporting Services Forum.