• Article

Relational Query Designer User Interface (Report Builder 2.0)

Report Builder 2.0 provides both a graphical query designer and a text-based query designer to help you create a query that specifies the data to retrieve from Microsoft SQL Server relational databases for a report dataset. Use the graphical query designer to explore the metadata, interactively build a query, and view the results for your query. Use the text-based query designer to view the query that was built by the graphical query designer or to modify a query. You can also import an existing query from a file or report.

Note

In Report Builder 2.0, to specify a query for data source types Oracle, OLE DB, ODBC, and Teradata, you must use the text-based query designer. For more information, see see Text-based Query Designer User Interface (Report Builder 2.0).

For step by step instructions on how to open the query designer, see How to: Open a Query Designer for a Dataset Query (Report Builder 2.0).

Toolbar for the Relational Query Designer

The relational query designer toolbar provides the following buttons to help you specify or view the results of a query.

Button

Description

Edit As Text

Toggle to the text-based query designer to view the automatically generated query or to modify the query.

Import

Import an existing query from a file or report. File types .sql and .rdl are supported.

Run Query

Run the query. The Query results pane displays the result set.

Relational Query Designer Panes

The relational query designer helps you build a Transact-SQL SELECT statement that specifies the database tables and columns from which to retrieve data for a report dataset. This query designer has the following panes: the Database view pane, the Selected fields pane, the Relationships pane, the Applied filters pane, and the Query results pane.

  • Database view
    Displays a hierarchical view of tables, views, stored procedures, and table-valued functions that are organized by database schema.

  • Selected fields
    Displays the list of database field names from the selected items in the Database view pane. These fields become the field collection for the report dataset.

  • Function parameters
    Displays the list of input parameters for stored procedures or table-valued functions in the Database view pane.

  • Relationships
    Displays the automatically created list of default JOINs that are inferred from selected fields for tables or views in the Database view pane.

  • Applied filters
    Displays a list of fields and filter criteria for tables or views in the Database view.

  • Query results
    Displays sample data for the result set for the automatically generated query.

Database View Pane

The Database View pane displays the metadata for database objects that you have the permissions to view, which is determined by the data source connection and credentials. The hierarchical view displays database objects organized by database schema. Expand the node for each schema to view tables, views, stored procedures, and table-valued functions. Expand a table or view to display the columns.

Selected Fields Pane

The Selected Fields pane displays the database fields that you select for tables or views or the input parameters for stored procedures or table-valued functions. The fields that are displayed in this pane become the field collection for the report dataset. After you create a dataset and a query, use the Report Data pane to view the field collection for a report dataset. These fields represent the data you can display in tables, charts, and other report items when you view a report.

To add or remove fields to this pane, select or clear check boxes for the table or view fields in the Database view pane.

Function Parameters Pane

The Function Parameters pane displays the parameters for a stored procedure or table-valued function. The following columns are displayed:

  • **Parameter Name   **Displays the name of the parameter that is defined by the stored procedure or table-valued function.

  • Value   A value to use for the parameter when the query runs to retrieve data to display in the Query Results pane at design-time. This value is not used when the report runs at run-time.

Relationships Pane

The Relationships pane displays the join relationships that are automatically determined from the foreign key constraints that are retrieved from the database metadata. The following columns are displayed:

  • **Left Table   **Displays the name of the first table that is part of a join relationship.

  • **Relationship   **Displays the type of Transact-SQL JOIN statement that is used in the automatically generated query. By default, if a foreign key constraint is detected, INNER JOIN is used. Other relationships can be LEFT JOIN or RIGHT JOIN. Otherwise, the relationship displays Unrelated. Unrelated joins produce a CROSS JOIN in the query. For more information about types of JOINs, see "JOIN Fundamentals" in SQL Server Books Online.

  • **Right Table   **Displays the name of the second table in the join relationship.

To delete a relationship, you must unselect all the selected fields in the Database view pane on which this relationship depends.

Applied Filters Pane

The Applied Filters pane displays the criteria that is used to limit the number of rows of data that are retrieved at run-time. Criteria specified in this pane are used to generate a Transact-SQL WHERE clause. When you select the parameter option, a report parameter is automatically created. Report parameters that are based on query parameters enable a user to specify values for the query to control the data in the report.

The following columns are displayed:

  • **Field Name   **Displays the name of the field to apply the criteria to.

  • **Operator   **Displays the operation to use to in the filter expression.

  • **Value   **Displays the value to use in the filter expression.

  • **Parameter   **Displays the option to add a query parameter to the query. Use the Dataset properties to view the relationship between query parameter and report parameter. For more information, see How to: Associate a Query Parameter with a Report Parameter (Report Builder 2.0).

Query Results Pane

The Query results pane displays the results for the automatically generated query that is specified by selections in the other panes. The columns in the result set are the fields that you specify in the Selected Fields pane and the row data is limited by the filters that you specify in the Applied Filters pane.

This data represents values from the data source at the time that you run the query. The data is not saved in the report definition .The actual data in the report is retrieved when the report is processed.

Sort order in the result set is determined by the order the data is retrieved from the data source. Sort order can be changed by modifying the query or after the data is retrieved for the report. For more information, see Sorting Data in a Report (Report Builder 2.0).

Understanding Automatically Generated Queries

When you select tables and columns or stored procedures and views in the Database View pane, the query designer retrieves the underlying primary key and foreign key relationships from the database schema. By analyzing these relationships, the query designer determines the type of JOIN to perform between tables. When relationships exist between two tables, the query designer specifies an INNER JOIN. Otherwise, the query designer specifies a CROSS JOIN.

Generated queries always contain at least one column from each table. Automatically generating a query that does not contain a column from each table is not supported.

For the most control over your query, use the text-based query designer. To switch to the text-based query designer, in the toolbar, click Edit As Text. Once you edit a query in the text-based query designer, you can no longer use the relational query designer. The query will then always open in the text-based query designer.