Query Designers and Data Processing Extensions (Reporting Services)
A query designer helps you create the query command that specifies the data that you want in a report dataset. The data processing extension passes the query you create to the data source and retrieves a result set. Reporting Services provides a query designer and data processing extension when you select a data source type for your dataset. For example, when you create a dataset for a Microsoft SQL Server data source type, the SQL Server query designer opens when you create the query.
A query designer and data processing extension can differ in support for a data source that uses a particular version of a language. For example, a query languages such as Transact-SQL can differ in syntax depending on the data source type. The Microsoft Transact-SQL language and the Oracle SQL language have some variation in syntax for a query command.
Query Designers and Data Source Types
You can use a query designer to create a new query that retrieves data from a data source. You can also import an existing query from a report definition file (.rdl) or, for SQL Server queries, from a .sql file.
Reporting Services provides both graphical and text-based query designers. By using a text-based query designer, you can type command text into a query pane. By using a graphical query designer, you can drag metadata items that represent the underlying data on a data source to the query design surface. Most multidimensional data sources provide a graphical query designer that displays metadata representing the data on the data source. You can change from a graphical query designer to a text-based query designer by clicking the text-based query designer icon on the toolbar.
You can only change from a text-based query designer to a graphical query designer if there is one registered for that data source. Because not all valid queries can be represented in a graphical query designer, when you switch query designer modes, your query text is not necessarily preserved. To have the most control over the query text, use the text-based query designer.
The following examples illustrate query command text that can be affected by the data provider:
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].[Contact].
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.
For more information about specific query designers, see Reporting Services Query Designers.