MSSQL Server Reporting Services: Managing Reporting Services: Data Connections and Uploads - Page 3
April 29, 2004
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:
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.
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.
We arrive at the Report Manager Folder View, as shown in Illustration 3.
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.
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.
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.