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