MSSQL Server Reporting Services: Managing Reporting Services: Data Connections and Uploads

Thursday Apr 29th 2004 by William Pearson

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.


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.

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.

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.

1.  Click Start.

2.  Navigate to 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 ...

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:


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

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

Illustration 3: Report Manager Folder View

4.  Click New Data Source atop the Folder View.

The New Data Source page appears.

5.  Type the following into the Name box:


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

6.  Type the following into the Description box:

AdventureWorks2000 shared data source for sample reports

7.  Ensure that the checkbox to the left of Enable this data source is checked (the default).

8.  Select Microsoft SQL Server in the Connection Type selector.

9.  Type the following into the Connection String text box:

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

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

10.  Under Connect Using, select Windows NT Integrated Security.

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

Illustration 4: The New Data Source Page, with Input

11.  Click OK to accept the shared data source.

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

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.

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

Click for larger image

Illustration 6: The Samples Folder Appears in the Choose File Dialog

3.  Click the Samples folder to open it.

4.  Click the Reports folder that appears, to open it.

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

5.  Click the Company Sales.rdl file, as shown in Illustration 7.

Illustration 7: Selecting the Company Sales.rdl File from among the Sample Set

6.  Click the Open button on the dialog.

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.

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.

7.  Click OK.

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.

Illustration 9: The Uploaded Company Sales Report Appears in the Folder View - Content Tab

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

Click for larger image

Illustration 10: Reporting Error Appears for Most of Us

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

12.  Click Browse.

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.

Mobile Site | Full Site