Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Apr 29, 2004

MSSQL Server Reporting Services: Managing Reporting Services: Data Connections and Uploads - Page 3

By William Pearson

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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM