Lesson 3: Defining a Query for the Report

After the data source is defined, Report Designer creates a dataset and displays the generic query designer, which you can use to design the query. For this tutorial, you will create a query that retrieves sales order information from the database.

The generic query designer is the default query design tool because it accommodates complex Transact-SQL statements and it does not format or validate the statements until you run the report. However, if you are more familiar with the graphical query designer, you can use it instead of the generic query designer. A toggle button on the query designer toolbar is provided so that you can switch tools. For more information about query designers, see Query Design Tools in Reporting Services.

To define a Transact-SQL query for report data

  1. Type, or copy and paste, the following query into the SQL pane of the generic query designer. The SQL pane is the top pane in the design tool. The diagram located below these steps show you where the query should be specified.

    SELECT  S.OrderDate, S.SalesOrderNumber, S.TotalDue, C.FirstName, C.LastName
    FROM    HumanResources.Employee E INNER JOIN
            Person.Contact C ON E.ContactID = C.ContactID INNER JOIN
            Sales.SalesOrderHeader S ON E.EmployeeID = S.SalesPersonID
  2. To view the results of the query, click the Run (!) button on the query designer toolbar.

The following diagram shows the generic query designer with the query copied into the SQL pane. Notice that the toggle button for enabling the generic query designer is selected. You can click this button if you want to use the graphical query designer instead.

Report query in generic query designer

Next Task

You have successfully specified a query that retrieves data for your report. Next, you will create the report layout. See Lesson 4: Adding a Table Data Region.

See Also


Tutorial: Creating a Basic Report

Other Resources

Defining Report Datasets for a SQL Server Relational Database

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

5 December 2005

New content:
  • Toggle to graphical query designer.