SQL Server Query Designer User Interface

New: 14 April 2006

Reporting Services provides both a graphical query designer and a generic query designer for creating queries to retrieve data from a relational database for a report dataset in Report Designer. The graphical query designer provides visual representations of the underlying data from the data source. Use the graphical query designer to browse and interactively build a query and view the results. The generic query designer provides a way to enter query text directly. Use the generic query designer to create, paste, or edit query text, run the command, and view the results.

To switch between the generic query designer and the graphical query designer, click the Generic Query Designer () toggle button on the toolbar. Some complex queries and expression-based queries cannot be represented in the graphical query designer.

For more information about working with a SQL Server data source, see Defining Report Datasets for a SQL Server Relational Database and Report Data How-to Topics.

Command Type

There are three different types of query commands: Text, StoredProcedure, or TableDirect. Before you create your query, you must select a command type from the Command type drop-down list. The default command type is Text. The Text type supports standard Transact-SQL query text for relational database data sources, including data processing extensions for Microsoft SQL Server and Oracle. Select StoredProcedure to use a stored procedure for your query. Select TableDirect to perform the equivalent of a SELECT * command to retrieve all the fields in a table.

Note

Not all data processing extensions support all types. The underlying data provider must support a Command type before all features are available. The Microsoft SQL Server data processing extensions support only Text and StoredProcedure. The OLE DB data processing extension with the Microsoft OLE DB Provider for SQL Server supports Text, StoredProcedure, and TableDirect.

To switch types, on the toolbar, click the Edit Selected Dataset button () to open the Dataset dialog box. From the Command type drop-down list, select the type, and then click OK. The query designer opens in the selected type.

Graphical Query Designer

There are four panes in the graphical query designer: Diagram, Grid, SQL, and Result. You can work with visual representations of database tables and a metadata grid populated with the columns you select. You can specify columns, aliases, sort values, and filter values for a Transact-SQL query. You can view the query text generated from your selections, run the query, and view the result set. For command type StoredProcedure, you can select a stored procedure from a drop-down list of stored procedures retrieved from the data source, run it, and see the results in the Result pane. Similarly, for command type TableDirect, you can select a table from a drop-down list of tables retrieved from the data source, run a default query that selects all columns from the table, and see the results in the Result pane.

Command Type Text

In Text type, the graphical query designer presents four areas, or panes. The following figure labels each pane.

The following table describes the function of each pane.

Pane	Function
Diagram	Displays graphic representations of the tables in the query. Use this pane to select fields and define relationships between tables.
Grid	Displays a list of fields returned by the query. Use this pane to define aliases, sorting, filtering, grouping, and parameters.
SQL	Displays the Transact-SQL query represented by the diagram and Grid panes. Use this pane to write or update a query using Transact-SQL.
Result	Displays the results of the query. To run the query, right-click in any pane, and then click Run, or click the Run button on the toolbar.

When you change information in any one of the first three panes, those changes will appear in the other panes. For example, if you add a table in the Diagram pane, it will automatically be added to the Transact-SQL query in the SQL pane. Adding a field to the query in the SQL pane will automatically add the field to the list in the Grid pane and update the table in the Diagram pane.

To perform actions in a certain pane, such as adding a table to the Diagram pane, right-click in the pane and then click the menu item.

Important

If you create or update a query using the query designer, you must save the changes to the report. You can do this by clicking the Refresh fields () button, or switch to Layout view, or on the File menu, click Save All.

Toolbar for the Graphical Query Designer

The graphical query designer toolbar provides buttons to help you design Transact-SQL queries using the graphical interface. The following figure shows the toolbar with a table that shows each button and its function.

Button	Description
	Edit the selected dataset.
	Delete the selected dataset.
	Refresh the dataset fields from the data source. If you have the Datasets window visible, any field changes you have made are updated in the list of fields for the selected dataset. To view the datasets window, on the View menu, click Datasets.
	Toggle between the generic query designer and graphical query designer.
	Show or hide the Diagram pane.
	Show or hide the Grid pane.
	Show or hide the SQL pane.
	Show or hide the Result pane.
	Run the query.
	Check that the syntax of the query text is correct.
	Set the sort order to Sort Ascending for the selected column in the Diagram pane,
	Set the sort order to Sort Descending for the selected column in the Diagram pane,
	Remove the filter for the selected column in the Diagram pane that is marked as having a filter ().
	Show or hide the Group By column in the Grid pane. When the Group By toggle is on, an extra column named Group By appears in the Grid pane, and each value for the selected columns in the query defaults to Group By, which causes the selected column to be included in a Group By clause in the SQL text. Use the Group By button to automatically add a GROUP BY clause that includes all columns in the SELECT clause. When your SELECT clause includes aggregate function calls (for example, SUM(ColumnName)), include each non-aggregate column in the GROUP BY clause if you want it to appear in the result set. To appear in the Result pane, each column in the query must have an aggregate function defined for use in calculating the value to display in the Result pane, or the column in the query has to be specified in the GROUP BY clause of the SQL query.
	Add a new table from the data source to the Diagram pane. Note   When you add a new table, the query designer tries to match up foreign key relationships from the data source. After you add a table, confirm that the foreign key relationships represented by linkages between the tables are correct.

Example

The following query returns the list of last names from the AdventureWorks database Contact table:

SELECT LastName FROM Person.Contact

You can also run stored procedures from the SQL pane. The following query runs the stored procedure uspGetEmployeeManagers in the AdventureWorks database:

EXEC uspGetEmployeeManagers '1'

Command Type StoredProcedure

In StoredProcedure type, the graphical query designer displays a drop-down list of the available stored procedures from the data source and a Result pane. The following figure labels each pane.

The following table describes the function of each pane.

Pane	Function
Stored procedure drop-down list	Lists all the available stored procedures from the data source. Select one from the list to make it active.
Result	Displays the result of running the stored procedure. To run the selected stored procedure, click the Run button on the toolbar.

Toolbar Buttons for Command Type StoredProcedure

The graphical query designer toolbar provides a drop-down list of stored procedures on the data source. The following figure shows each button and its function.

Button	Description
	Edit the selected dataset.
	Delete the selected dataset.
	Refresh the dataset fields from the data source.
	Toggle between generic query designer and graphical query designer, preserving the query text or stored procedure view.
	Run the selected stored procedure.
Stored procedure drop-down list	Click the down arrow to display a list of available stored procedures from the data source. Click any stored procedure from the list to select it.

Example

The following stored procedure calls a chain-of-command list of managers from the AdventureWorks database. This stored procedure accepts EmployeeID as parameter. You can enter any small integer.

uspGetEmployeeManagers '1'

Command Type TableDirect

In TableDirect type, the graphical query designer displays a drop-down list of the available tables from the data source and a Result pane. If you select a table and click the Run button, all the columns for that table are returned.

Note

Not all data providers support this type. One data provider that does support TableDirect type is OLEDB using the Microsoft OLE DB Provider for SQL Server.

The following figure labels each pane.

The following table describes the function of each pane.

Pane	Function
Table drop-down list	Lists all the available tables from the data source. Select one from the list to make it active.
Result	Displays all columns from the selected table. To run the table query, click the Run button on the toolbar.

Toolbar Buttons for the Command Type TableDirect

The graphical query designer toolbar provides a drop-down list of tables on the data source. The following figure shows each button and its function.

Button	Description
	Edit the selected dataset.
	Delete the selected dataset.
	Refresh the dataset fields from the data source.
	Toggle between generic query designer and graphical query designer, preserving the query text or stored procedure view.
	Select all columns from the selected table.
Table drop-down list	Click the down arrow to display a list of available tables from the data source. Click any table from the list to select it.

Generic Query Designer

Use the generic query designer for complex queries, to paste queries from other tools, or to use for expression-based queries. The generic query designer supports the same three command types as the graphical query designer: Text, StoredProcedure, and TableDirect. There are two panes: the Query pane, which shows the query text, stored procedure, or table name, and the Result pane, which shows the results of running the query. Text type is the default.

To change types, on the toolbar, click the Command type drop-down list, and select a type.

Generic Query Designer Toolbar

The generic query designer provides a single toolbar for all the command types. The following figure shows the toolbar and a table that shows each button and its function.

Button

Description

Edit the selected dataset.

Delete the selected dataset.

Refresh the dataset fields from the data source.

Run the query text or the stored procedure and display the result set in the Result pane.

Command Type

Select Text, StoredProcedure, or TableDirect. If a stored procedure has parameters, the Define Query Parameters dialog box appears when you click Run on the toolbar, and you can fill in values as needed.

Note:

If a stored procedure returns more than one result set, the first result set is used to populate the dataset.

Command Type Text

When you create a SQL Server dataset, Report Designer displays the graphical query designer by default. To switch to the generic query designer, click the generic query designer () toggle button on the toolbar. The generic query designer presents two panes: the Query pane and the Result pane. The following figure labels each pane.

The following table describes the function of each pane.

Pane	Function
Query	Displays the Transact-SQL query text. Use this pane to write or edit a Transact-SQL query.
Result	Displays the results of the query. To run the query, right-click in any pane and click Run, or click the Run button on the toolbar.

Example

The following query returns the list of last names from the AdventureWorks database Contact table.

SELECT LastName FROM Person.Contact

The following query calls the AdventureWorks stored procedure uspgetEmployeeManagers and returns the chain-of-command for the employee with identification number 1.

EXEC uspgetEmployeeManagers '1'

When you click Run on the toolbar, the command in the Query pane runs and the results are displayed in the Result pane.

Command Type StoredProcedure

When you select Command typeStoredProcedure, the generic query designer presents two panes: the Query pane and the Result pane. Select a stored procedure from the drop-down list and it is pasted into the Query pane. From here, you can type in parameter values.

Example

The following query calls the AdventureWorks stored procedure uspgetEmployeeManagers and returns the chain-of-command for the employee with identification number 1.

uspgetEmployeeManagers '1'

Command Type TableDirect

When you select Command typeTableDirect, the generic query designer presents two panes: the Query pane and the Result pane. If you select a table and click the Run button, all the columns for that table are returned.

Example

The following query returns a result set for all customers in the AdventureWorks database. When you select the Customers table name from the drop-down list, it is the equivalent of creating the Transact-SQL query SELECT * FROM Customers. The following text is pasted into the Query pane.

Customers

See Also

Other Resources

Query Design Tools in Reporting Services
Defining Report Datasets
Defining Report Datasets for a SQL Server Relational Database
Defining Report Datasets for Other Data Sources
Installing AdventureWorks Sample Databases and Samples

Help and Information

Getting SQL Server 2005 Assistance