Lesson 3: Creating the Departments Dataset 

  • Article

 

In this step, you will create a dataset that provides values for a Department parameter defined later in this tutorial. In the finished report, the Department parameter will be used to filter the Employees dataset by a chosen department.

To create a dataset

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

    Note

    If a data source is already available to the report, Report Designer displays the Dataset dialog instead of the Data Source dialog. A data source was defined when you created the Employees dataset.

     

     

  2. In Name, type Departments.

  3. Click OK.

To add a query to the dataset

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

    SELECT   0 AS DepartmentID, 'All' AS Name
UNION
SELECT   DepartmentID, Name
FROM     HumanResources.Department
ORDER BY Name

    This UNION query creates a list of departments that includes the word "All" at the top of the list.

    Note

    The generic query designer is displayed in the Data view by default. You can also use the graphical query designer to edit queries; however, UNION queries should always be edited using the generic query designer.

     

     

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

Next Steps

After you create the Department data set, you can continue to the next step: Lesson 4: Defining a Report Layout.

See Also

Concepts

Tutorial: Using a Dynamic Query in a Report

Other Resources

Using Dynamic Queries
Retrieving Data from a Data Source

Help and Information

Getting SQL Server 2005 Assistance