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.

Introduction

In our last article, The Authoring Phase: Overview Part II, we resumed where we left off in Part 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 Services.

In 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

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

Reporting Services has arrived.

Introduction to the Managing Phase

We 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:

• scheduling
• linking to other reports
• application of security
• movement to various locations
• renaming, deletion and other file maintenance

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.

In this 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 subsequent articles.

Let's get 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 Services.

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

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

Let's 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.

Creating 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:

• Specification of the data processing extension we use to process queries of the type for which we intend to use the connection [ex's]
• Connection string that allows us to locate the source
• Access 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.

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

Our first step is to start Report Manager, then to create a shared data source in lockstep with uploading our reports.

 http://<webservername>/reports

We arrive at the Report Manager Folder View, as shown in Illustration 3.

The New Data Source page appears.

AdventureWorks2000

(The sample reports are based upon data in the sample MSSQL Server 2000 database, AdventureWorks2000, which is created within the typical Reporting Services installation.)

AdventureWorks2000 shared data source for sample reports

data source="(local)";persist security info=False;

  initial catalog=AdventureWorks2000

With our input, the New Data Source page appears as depicted in Illustration 4 below.

We are returned to the Folder View, and see our new data source appear on the Contents tab, as shown in Illustration 5.

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

Uploading the Reports

We 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 prospective articles.

The Upload File page appears.

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

The Samples folder appears in the Choose File dialog, once located, as depicted in Illustration 6.

The sample report definition files (.rdl's) and other object files appear. At this point, we will select the Company Sales report definition files.

The Company Sales file, along with default title (which we can change), appear in the respective boxes of the Upload File page, as depicted in Illustration 8.

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.

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

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.

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.

The Properties tab assumes the focus.

The Data Sources page appears, indicating the cause of the error, as shown in Illustration 11.

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.

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.

We see AdventureWorks2000 populate the Location box, as shown in Illustration 14.

We arrive at the initial Data Source property page, where we see that the shared data source is designated.

The Company Sales report appears, as shown in Illustration 15.

We see that we have established a data connection to the report.

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.

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.

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.