Set up a Data Connection and Create a Dataset
Our next step is to set up a Data Connection. 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 eccentric, scenario on your hands.
Let's set up a Connection, and create a Dataset within our practice example.
1. Select New Dataset in the Dataset selector at the top of the Data tab, as depicted in Illustration 8.
Click for larger image
Illustration 8: Select New Dataset in the Dataset Selector Data Tab
As soon as we click the New Dataset selection, the Data Link Properties dialog box appears, defaulted to the Connection tab.
2. Type the name of the computer housing the targeted OLTP database, AdventureWorks2000.
(My server name, MOTHER1, appears in this article.)
3. Select the radio button to the left of the authentication option that is appropriate for your environment.
(Mine is Windows NT Integrated security.)
4. Select AdventureWorks2000 within the Select the database on the server selector.
The settings on the Connection tab of the Data Link Properties dialog should resemble those shown in Illustration 9.
Illustration 9: Data Link Properties Dialog Connection Tab
5. Click the Test Connection button to verify connectivity to the data source.
We receive a message box, indicating a successful test connection, as shown in Illustration 10.
Illustration 10: We Test Positive for Connectivity
6. Click OK to accept the settings we have made, and to close the Data Link Properties dialog.
Report Designer next presents us with the dataset design tool, based upon our newly connected source. We are immediately positioned to design our query, which brings us to the next step.
7. Click the ellipses ("...") button to the right of the default dataset name of AdventureWorks2000, which appears in the Dataset selector, as shown in Illustration 11.
Illustration 11: Editing the New Dataset
8. Enter VendorStateAllocation for the name of the Dataset dialog box, replacing the default name of AdventureWorks2000.
(AdventureWorks2000 remains selected for the data source by default.) The Dataset dialog appears as shown in Illustration 12.
Illustration 12: Completed Dataset Dialog
9. Click OK to accept the settings and return to the Data tab, which displays in the Generic Query Designer.
10. Type the following into the SQL pane:
SELECT Vendor.VendorID AS VendorID, StateProvince.StateProvinceCode AS StateCode FROM Address INNER JOIN VendorAddress ON Address.AddressID = VendorAddress.AddressID INNER JOIN Vendor ON VendorAddress.VendorID = Vendor.VendorID INNER JOIN StateProvince ON Address.StateProvinceID = StateProvince.StateProvinceID WHERE (StateProvince.StateProvinceCode IN( 'WA', 'CA', 'OR'))
As we have noted in earlier articles in the MSSQL Server 2000 Reporting Services series, we might have constructed the above query using the Query Builder. Query Builder's graphical tools are very helpful when we are unfamiliar with the database we are querying, or if we are learning the syntax of SELECT queries. Because it is more efficient to simply type the query into the SQL pane of the Dataset dialog box, or in the Generic Query Designer, we will take this route in many articles to conserve time and space. (Some queries cannot be created through the Query Builder, due to complexity or other complications, but the tool will serve many of us well, in most cases).
Click the Run button (shown in Illustration 13, atop the query we have constructed) to ascertain that the syntax is correct.
Illustration 13: Running the Query (Compact View, Run Button Circled)
As we can see, the resulting dataset contains a simple list of Vendor IDs, together with the respective States in which they are located. We will rely upon this dataset to populate our pie chart in the next section. The VendorIDs will be counted to generate number of vendors in each State (or Category, in chart property parlance), as we shall see.