• Article

How to: Open a Query Designer for a Dataset Query (Report Builder 2.0)

Query designers help you build a query to specify the data to retrieve from a data source for a report dataset. Report Builder 2.0 provides a query designer for each data source type, for example Microsoft SQL Server or Microsoft SQL Server Analysis Services. When you create a dataset query for a data source, whether it is for an embedded data source or a shared data source reference, the query designer associated with the data source type opens.

Some data source types support both a graphical query designer and a text-based query designer. When more than one designer is supported, you can click Edit As Text on the query designer toolbar to toggle the designer. In most designers, editing the query text and then toggling to the graphical query designer is not supported. For more information, see Query Designers and Data Sources (Report Builder 2.0).

After you create a dataset, use the Report Data pane to view the field collection and automatically generated report parameters that are specified by the query and query parameters.

To open a query designer

  1. In the Report Data pane, right-click the data source, and then click Add Dataset.

  2. In Name, type a name for the dataset in the Name text box or accept the default name.

    Note

    The dataset name is used internally within the report. For clarity, we recommend that the name of the dataset describe the data that the query returns.

  3. In Data source, verify the name of the data source you selected in step 1, or click New to create a new data source.

  4. In Query Type, select from the options that are enabled. Options depend on the data source type.

    • Text   Type or enter a query in the query pane, or click the Query Designer button. The query must be written in query language for the data source type. For example, for data source type Microsoft SQL Server, use Transact-SQL.

    • Table   Supported for OLE DB and ODBC data source types only. From the list, select a table. This returns all columns in the table. For example, for a table named Sales.Customer in a SQL Server database, this is the same as running the Transact-SQL statement SELECT * FROM Sales.Customer.

    • **StoredProcedure   **From the list, select a stored procedure or user defined function.

  5. If you clicked Query Designer in step 4, a query designer opens. The query designer that opens is the default query designer that is associated with the type of data source that you chose in step 1. If this data source type supports multiple query designers or multiple modes within a query designer, you can change from graphical to text-based or from text-based to graphical. Your choice is used the next time the query designer opens for this data source type. For more information about using query designers, see Query Designers (Report Builder 2.0).

  6. After you finish designing a query, Click OK.

  7. (Optional) To view the field collection created by the query, in the Dataset Properties dialog box, click Fields.

    To view query parameters specified in the query, click Parameters.

    To view report parameters that are automatically generated for the query parameters, close the Dataset Properties dialog box, and expand the Parameters node in the Report Data pane.