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 19, 2005

MSSQL Server Reporting Services: Black Belt Components: Ad Hoc Sorting with Parameters - Page 3

By William Pearson

Set up a Data Connection for the Territory Sales Drilldown Report

Our first step is to set up a Data Connection. As we have noted numerous times throughout our series, Reporting Services can connect with, and create the DataSets it needs from virtually any ODBC or OLE DB-compliant data source, (in addition to the obvious MSSQL Server and MSAS data stores). .NET-based API's add the potential for other data sources, assuming that you have a legacy, or otherwise "alternative," scenario on your hands.

Let's set up a Connection to support the DataSet within our practice example.

1.  Right-click the Shared Data Sources folder underneath the new RS016 project tree in the Solution Explorer.

2.  Select Add New Data Source from the context menu that appears, as depicted in Illustration 6.

Illustration 6: Select Add New Data Source from the Context Menu

The Data Link Properties dialog appears, defaulted to the Connection tab.

3.  Click the Provider tab, to select it.

4.  Select Microsoft OLE DB Provider for SQL Server from the Provider list box, as shown in Illustration 7.

Illustration 7: Selecting Microsoft OLE DB Provider for SQL Server - Providers Tab

5.  Click Next, to move to the Connection tab.

6.  In the Data Source box, select the name of the server upon which the AdventureWorks2000 database resides (mine is MOTHER1 in the illustration that follows).

7.  Provide the appropriate authentication information to access the AdventureWorks2000 database on the server you have selected.

I am using Windows NT Integrated Security, as this is not a production environment. For a production environment, selections here require careful consideration.

8.  Select AdventureWorks2000 in the list of data sources that appear when we click the down-arrow selector button at the next box (and thus receive confirmation that our server / authentication information is adequate to display the sources) at the top of the Data tab).

The completed Data Link Properties - Connection tab appears as depicted in Illustration 8.

Illustration 8: Data Link Properties - Connection Tab

9.  Click the Test Connection button to confirm connectivity.

The Microsoft Data Link message box appears, indicating a successful test, as shown in Illustration 9.

Illustration 9: Testing Positive for Connectivity ...

10.  Click OK to close the message box.

11.  Click OK to accept the settings we have made, and to close the Data Link Properties dialog.

The new shared data source appears in the Solution Explorer pane. Let's change the name of the source to make it work with the previously created sample report we are about to clone.

12.  Right-click the new AdventureWorks2000 shared data source.

13.  Select Rename from the context menu that appears, as depicted in Illustration 10.

Illustration 10: Select Rename from the Context Menu ...

The file name becomes "editable" in Windows Explorer.

14.  Take the "2000" out of the name of the .rds file, modifying it to simply "AdventureWorks.rds"

15.  Click outside the editor, in the white space within the Solution Explorer pane, to "set" the changes to the file name.

The shared data source appears as shown in Illustration 11.

Illustration 11: The Renamed Shared Data Source in the Solution Explorer

Having established the data source, we are now ready to create a copy of the Territory Sales Drilldown sample report, which we will then modify to meet the expressed requirements of the information consumers.

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