Lesson 2: Creating the Employees Dataset 

  • Article

 

In Reporting Services, a dataset specifies a data source connection and a query that retrieves data.

In this tutorial, you will create two datasets to use in the report. The first dataset provides a list of employees, and it is created in this lesson. The second dataset provides department data and is created in the following lesson. Both datasets retrieve data from the AdventureWorks database.

When there are no other data sources in the report or the project, the first dataset that you create is named after the database you selected. Because there will be two datasets in this report, the first data set should be renamed to something more descriptive.

To create a data source and dataset

  1. In Data view, in Dataset at the top of the view, select New Dataset. The Data Source dialog box appears.

  2. In Name, type AdventureWorks.

  3. In Type, select Microsoft SQL Server.

  4. In Connection string, type the following:

    Data source=(local); initial catalog=AdventureWorks

    Note

    This connection string assumes that Business Intelligence Development Studio, the report server, and the AdventureWorks database are all installed on the local computer and that you have permission to log onto the AdventureWorks database. For more information about connection strings, see Connecting to a Data Source and Data Source (General Tab, Report Designer).

     

     

  5. Click OK. AdventureWorks is added to the DataSets pane.

To rename a dataset

  1. Click the Edit Selected Dataset (...) button on the toolbar.

  2. In Name, type Employees.

  3. Click OK.

To add a query to a dataset

  1. In Data view, type, or copy and paste, the following query in the top pane of the generic query designer:

    Select c.firstname, c.lastname, e.title, d.departmentID
From HumanResources.EmployeeDepartmentHistory D INNER JOIN
HumanResources.Employee E ON D.EmployeeID = E.EmployeeID INNER JOIN
Person.Contact C ON E.ContactID = C.ContactID
Where D.DepartmentID=1
Order By c.lastname

    This query will be changed later to an expression; creating a basic query first will allow Report Designer to automatically create a fields list. If you begin by writing an expression, you have to manually update the fields list.

  2. To view the results of the query, click the Run (!) button on the query designer toolbar.

    Note   The AdventureWorks database schema has been updated since it was first released. If you get an error rather than the query results you expect, verify that you are using the most recent version. For more information about how to install the database, see Installing AdventureWorks Sample Databases and Samples.

Next Steps

After you create dataset, you can continue to the next step: Lesson 3: Creating the Departments Dataset.

See Also

Concepts

Tutorial: Using a Dynamic Query in a Report

Other Resources

Using Dynamic Queries
Connecting to a Data Source
Retrieving Data from a Data Source

Help and Information

Getting SQL Server 2005 Assistance