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.
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
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.
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.