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:
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 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:
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.)
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.
» 
Comment and Contribute
