Create a Shared Data Source with Report Wizard

By: Ray Barley

Overview

The Data Source contains the information necessary to retrieve the data we want to render on our report.  Reporting Services can access data in relational databases, OLAP databases, and just about other any data source for which you have an ODBC or OLE DB driver. 

When we create a Data Source we can specify it as shared which means it can be used by any report in the same project.  As a general rule you probably want to create Shared Data Sources.  If a Data Source isn't shared it just means that its definition is stored inside the report and it cannot be shared with another report.

In this section we will walk through the details of creating a Shared Data Source.

Explanation
After launching the Report Wizard you will be presented with the Select the Data Source dialog as shown below:

Our project does not have any Shared Data Sources yet so we do not have the option of selecting an existing Shared Data Source for our new report.  Instead we will have to define a Data Source.  We will have to supply the following:

• Name - choose a descriptive name for this Data Source; we'll use AdventureWorksLT.  Do not put any spaces in the name; if you do you will get an error message when you complete the wizard and your Data Source will not be created
• Type - select from the available options in the dropdown list; the default value of Microsoft SQL Server is the correct one for the AdventureWorksLT database that we are using
• Connection String - enter the connection string for your Data Source; usually you will want to click the Edit button to enter the details and have the connection string created for you
• Edit button - rather than entering the connection string, click this button to display the Connection Properties dialog where you can enter some information and have the connection string created for you
• Credentials button - click this button to display the specify Data Source Credentials dialog where you can specify the credentials to use when connecting to your Data Source
• Make this a shared data source checkbox - click this checkbox to create a Shared Data Source; any report in the same project can the use this Data Source

Click the Edit button to display the Connection Properties dialog; fill in your Server name and select the AdventureWorksLT database as shown below:

The Server name is the one where your SQL Server database is deployed.  If you are running a named instance of SQL Server, you will need to specify the Server name as SERVERNAME\INSTANCENAME.  If you are running SQL Server locally you can specify localhost in place of SERVERNAME.  You should click the Test Connection button to verify that you are able to connect to the database then click OK to close the dialog.

Click the Credentials button to display the Data Source Credentials dialog as shown below:

The default selection Use Windows Authentication (Integrated Security) is okay for our purposes.  This means that Reporting Services will connect to the Data Source using the Windows credentials of the person running the report.  Note that when your report is complete and you deploy the report and Data Source for others to use, you can select a different option if necessary.  For now we'll stick with the default.

After completing the above steps, the Select the Data Source dialog will look as shown below:

Click Next to move on to the Design the Query dialog which we will discuss in the next section.