Query Designers and Data Sources (Report Builder 2.0)
A query designer helps you create a query that specifies the data that you want to include in a report dataset. A query designer can be text-based or graphical. A text-based query designer enables you to work with the query command directly. A graphical query designer automatically builds the query command as you select or drag items from a view of the underlying data source to the query design surface.
Report Builder 2.0 provides a query designer for each supported data source type. The correct query designer opens automatically when you add or modify a dataset. Some data source types support only a text-based query designer, others support only a graphical query designer, and some support both.
If a data source type supports both graphical and text-based query designers, you can toggle from graphical to a text-based view, but not necessarily from text-based view to graphical view. Not every query command can be represented graphically. To have the most control over a query, use the text-based query designer.
From a query designer, you can create a query or import an existing query from a report. For more information, see Creating a Report Dataset (Report Builder 2.0).
Query Designers for Shared Data Sources
When you include a shared data source in your report, you are including a reference to a data source that has been published to a report server. The data source type can be any type that has been installed and configured on the report server by the report server administrator. This can include data source types that are not installed with Report Builder 2.0. By default, dataset queries for these data source types can be created in the text-based query designer.
For the list of supported data source types, see Example Connections (Report Builder 2.0).
Query Designers and Command Syntax
Support for the syntax of a query command syntax is determined by the software that is associated with the data source type. On the report server, this software can be a SQL Server 2008 Reporting Services data processing extension or a .NET Framework data provider.
The following examples illustrate query command text that can be affected by data source type:
Support for the schema part of a database object name. When a data source uses schemas as part of the database object identifier, the schema name must be supplied as part of the query for any names that do not use the default schema. For example, SELECT FirstName, LastName FROM [Person].[Person].
Support for query parameters. Data providers differ in support for parameters. Some data providers support named parameters; for example, SELECT Col1, Col2 FROM Table WHERE <parameter identifier><parameter name> = <value>. Some data providers support unnamed parameters; for example, SELECT Col1, Col2 FROM Table WHERE <column name> = ?. The parameter identifier might differ by data provider; for example, SQL Server uses the "at" (@) symbol, Oracle uses the colon (:). Some data providers do not support parameters. For more information, see "Data Sources Supported by Reporting Services" in the Reporting Services documentation in SQL Server Books Online.
For more information about specific query designers, see Query Designers (Report Builder 2.0).