How to: Open a Query Designer for a Dataset Query

Query designers help you build a query to specify the data to retrieve from a data source for a report dataset. Reporting Services provides a query designer for each data source type, for example Microsoft SQL Server or Microsoft SQL Server Analysis Services.

Some data source types support both a graphical query designer and a text-based query designer and you can click Edit As Text on the query designer toolbar to toggle between them.

After you finish designing a query and finish creating 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.

Query designers are registered and installed when you install Reporting Services. Whether a specific query designer opens by default to Design view or Query view depends on the data source type and the query. Use the RSReportDesigner.config file to view the query designer configured to open for each data source type. For more information, see RSReportDesigner Configuration File.

To open a query designer

  1. In the Report Data pane, on the toolbar, click New, and then click Dataset.

  2. In the Dataset Properties dialog box, 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 the Data source list box, select the name of an existing data source. The data source type for this data source determines which query designers are available to help you build a query.

  4. Select a Query Type option. Options depend on the data source type.

    • Text   Type or enter a query in the query pane. 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. Click Query Designer to open the graphical or text-based query designer tool. The query designer associated with the data source type opens in the mode you selected in step 4.

    Click Edit As Text on the toolbar, if enabled, to toggle between the text-based query designer and the graphical query designer.

    For more information about using query designers, see Reporting Services Query Designers.

  6. Click OK.

    To view the field collection created by the query, click Fields. In the Dataset Properties dialog box**, y**ou can add your own calculated 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 view the Parameters node in the Report Data pane.