Using Query Parameters with Specific Data Sources

Artykuł
12/03/2008

New: 17 July 2006

When you define a query for a dataset, you choose the data source type. The data source type specifies the data processing extension or registered .NET data provider used to connect to the data source. The data provider is the software layer that interacts with the data source and determines what support there is for using variables in a query. This topic shows how, for a given result set, a query changes depending on the data source to which you are connected, and whether it contains a variable in the query.

Query Variables and Data Providers

The following table shows the results of data from the AdventureWorks sample database. You can create this result set by running a Transact-SQL query against the AdventureWorks sample database or an MDX query against the Adventure Works cube deployed as part of the AdventureWorks Sample Reports. The result set shows two columns: the names of product models that contain the word "Mountain" and the corresponding number of Internet sales.

Name	InternetSales
Fender Set - Mountain	2121
HL Mountain Tire	1396
LL Mountain Tire	862
ML Mountain Tire	1161
Mountain Bottle Cage	2025
Mountain Tire Tube	3095
Mountain-100	396
Mountain-200	3552
Mountain-400-W	543
Mountain-500	479
Women's Mountain Shorts	1019

In the next sections, you will see the query to use to create this result set, first from a relational database and then from a multidimensional database.

Relational Database Data Sources

For a relational database like Microsoft SQL Server, you can create queries using Transact-SQL. Although this query limits the results by including a WHERE clause that is set to a hard-coded value, the query does not yet contain a variable.

Transact-SQL Query

Transact-SQL Query
`SELECT PM.Name, Count(*) as InternetSales` `FROM Sales.SalesOrderHeader SOH INNER JOIN Sales.SalesOrderDetail SOD` `ON SOH.SalesOrderID = SOD.SalesOrderID INNER JOIN` `Production.Product PP` `ON SOD.ProductID = PP.ProductID INNER JOIN` `Production.ProductModel PM ON PP.ProductModelId = PM.ProductModelID` `WHERE (SOH.OnLineOrderFlag = 1 AND PM.Name LIKE ('%Mountain%'))` `GROUP BY PM.Name` `ORDER BY PM.Name`

SELECT PM.Name, Count(*) as InternetSales

FROM Sales.SalesOrderHeader SOH INNER JOIN Sales.SalesOrderDetail SOD

ON SOH.SalesOrderID = SOD.SalesOrderID INNER JOIN

Production.Product PP

ON SOD.ProductID = PP.ProductID INNER JOIN

Production.ProductModel PM ON PP.ProductModelId = PM.ProductModelID

WHERE (SOH.OnLineOrderFlag = 1 AND PM.Name LIKE ('%Mountain%'))

GROUP BY PM.Name

ORDER BY PM.Name

To add a variable for the Product Model name, PM.Name, you need to specify a query parameter in the WHERE clause. The following table shows how to specify query parameters for different data providers. The data provider is automatically selected for you when you choose the data source type. The connection string for this data provider is shown in the second column. The query in the table is identical to the query above but has added a parameter in the form supported by the data provider.

Data Source Type	Data Provider	Query
Microsoft SQL Server	Microsoft SQL Server (SqlClient) Connection String: `Data Source=.;Initial Catalog=AdventureWorks`	`SELECT PM.Name, Count(*)` `FROM Sales.SalesOrderHeader SOH INNER JOIN Sales.SalesOrderDetail SOD` `ON SOH.SalesOrderID = SOD.SalesOrderID INNER JOIN` `Production.Product PP` `ON SOD.ProductID = PP.ProductID INNER JOIN` `Production.ProductModel PM ON PP.ProductModelId = PM.ProductModelID` `WHERE (SOH.OnLineOrderFlag = 1 AND (PM.Name` `LIKE '%' + @Mountain + '%'))` `GROUP BY PM.Name` `ORDER BY PM.Name`
OLE DB	Microsoft OLE DB Provider for SQL Server Connection String: `Provider=SQLOLEDB.1;Data Source=.;Initial Catalog=AdventureWorks`	`SELECT PM.Name, Count(*)` `FROM Sales.SalesOrderHeader SOH INNER JOIN Sales.SalesOrderDetail SOD` `ON SOH.SalesOrderID = SOD.SalesOrderID INNER JOIN` `Production.Product PP` `ON SOD.ProductID = PP.ProductID INNER JOIN` `Production.ProductModel PM ON PP.ProductModelId =` `PM.ProductModelID` `WHERE ((SOH.OnLineOrderFlag = 1) AND PM.Name` `LIKE` `('%' + ? + '%'))` `GROUP BY PM.Name` `ORDER BY PM.Name`
Oracle	Microsoft SQL Server (OracleClient)	`SELECT PM.Name, Count(*)` `FROM Sales.SalesOrderHeader SOH INNER JOIN Sales.SalesOrderDetail SOD` `ON SOH.SalesOrderID = SOD.SalesOrderID INNER JOIN` `Production.Product PP` `ON SOD.ProductID = PP.ProductID INNER JOIN` `Production.ProductModel PM ON PP.ProductModelId =` `PM.ProductModelID` `WHERE ((SOH.OnLineOrderFlag = 1) AND` `PM.Name` `LIKE '%' + :1 + '%')` `GROUP BY PM.Name` `ORDER BY PM.Name`
OLE DB	Any registered .NET Data Provider	Provider-specific.

In these examples, a variable was added to the WHERE clause of the query, changing the test for the word "Mountain" to a test for the variable value.

The .NET data provider for SQL Server supports named variables that begin with the at (@) character. For example, PM.Name ``LIKE '%' + @Mountain + '%').
The OLE DB provider for SQL Server does not support named variables. Use the question mark (?) character to specify a variable. Parameters passed to the OLE DB provider must be passed in the order they occur in the WHERE clause. For example, PM.Name ``LIKE ('%' + ? + '%').
The .NET data provider for Oracle supports numbered parameters that begin with the colon (:) character. For example, PM.Name ``LIKE '%' + :1 + '%').
Support for variables by other OLE DB data providers are specific to the data provider. Check the data provider's documentation for more information.

The query designer associated with a data source type creates a query parameter for each variable identified. These query parameters are listed on the Parameter tab of the Dataset property pages. For every query parameter, a report parameter of type String is created. The query parameter values defined for the dataset are set to the corresponding report parameter. You can edit the report parameter data type and other properties in the Report Properties dialog box, which is accessible from the Report menu when you are in Data view. You can also create, edit, or delete these query parameters manually. For more information, see Dataset (Parameters Tab, Report Designer).

Multidimensional Database Data Sources

For a multidimensional database like Microsoft Analysis Services, you can create queries using MDX. Although this query limits the results by including a Filter clause set to a hard-coded value, the query does not yet contain a variable.

MDX Query

MDX Query
`SELECT NON EMPTY` `{ [Measures].[Internet Order Count] } ON COLUMNS,` `NON EMPTY {` `([Product].[Model Name].[Model Name].ALLMEMBERS ) }` `DIMENSION PROPERTIES` `MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS` `FROM (` `SELECT (` `Filter(` `[Product].[Model Name].[Model Name].ALLMEMBERS,` `Instr([Product].[Model Name].currentmember.Properties(` `'Member_Caption' ),` `'Mountain' ) > 0 )` `) ON COLUMNS` `FROM [Adventure Works])` `CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR,` `FORMATTED_VALUE, FORMAT_STRING,` `FONT_NAME, FONT_SIZE, FONT_FLAGS`

SELECT NON EMPTY

{ [Measures].[Internet Order Count] } ON COLUMNS,

NON EMPTY {

([Product].[Model Name].[Model Name].ALLMEMBERS ) }

DIMENSION PROPERTIES

MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM (

SELECT (

Filter(

[Product].[Model Name].[Model Name].ALLMEMBERS,

Instr([Product].[Model Name].currentmember.Properties(

'Member_Caption' ),

'Mountain' ) > 0 )

) ON COLUMNS

FROM [Adventure Works])

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR,

FORMATTED_VALUE, FORMAT_STRING,

FONT_NAME, FONT_SIZE, FONT_FLAGS

To add a variable for the Product Model name, @ProductModelName, you need to add a FILTER clause. The following table shows that, for multidimensional databases, variables are part of a FILTER clause. The data source type is the value you choose from the drop-down list when you create a new data source for Reporting Services. The data provider is automatically selected for you when you choose the data source type. The connection string for this data provider is also shown in the second column. The query in the table is identical to the query above but has added a parameter in the form supported by the data provider.

Data Source Type Data Provider Query

Data Source Type	Data Provider	Query
Microsoft SQL Server Analysis Services	SQL Server Analysis Services (AdomdClient) Connection String: `Data Source=.;Initial Catalog="Adventure Works DW"`	`SELECT NON EMPTY { [Measures].[Internet Order Count] } ON COLUMNS,` `NON EMPTY { ([Product].[Model Name].[Model Name].ALLMEMBERS ) }` `DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS` `FROM` `( SELECT (` `Filter( [Product].[Model Name].[Model Name].ALLMEMBERS,` `Instr( [Product].[Model Name].currentmember.Properties(` `'Member_Caption' ), @ProductModelName ) > 0 ) )` `ON COLUMNS` `FROM [Adventure Works])` `CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR,` `FORMATTED_VALUE, FORMAT_STRING,` `FONT_NAME, FONT_SIZE, FONT_FLAGS`
OLE DB	Microsoft OLE DB Provider for Analysis Services 9.0 Connection String: `Provider=MSOLAP.3;Data Source=.;Initial Catalog="Adventure Works DW"`	OLE DB for OLAP does not support parameterized queries. Workarounds are described in this whitepaper: "Integrating Analysis Services with Reporting Services".

Microsoft SQL Server Analysis Services

SQL Server Analysis Services (AdomdClient)

Connection String:

Data Source=.;Initial Catalog="Adventure Works DW"

SELECT NON EMPTY { [Measures].[Internet Order Count] } ON COLUMNS,

NON EMPTY { ([Product].[Model Name].[Model Name].ALLMEMBERS ) }

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM ( SELECT (

Filter( [Product].[Model Name].[Model Name].ALLMEMBERS,

Instr( [Product].[Model Name].currentmember.Properties(

'Member_Caption' ), @ProductModelName ) > 0 ) )

ON COLUMNS

FROM [Adventure Works])

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR,

FORMATTED_VALUE, FORMAT_STRING,

FONT_NAME, FONT_SIZE, FONT_FLAGS

OLE DB

Microsoft OLE DB Provider for Analysis Services 9.0

Connection String:

Provider=MSOLAP.3;Data Source=.;Initial Catalog="Adventure Works DW"

OLE DB for OLAP does not support parameterized queries.

Workarounds are described in this whitepaper: "Integrating Analysis Services with Reporting Services".

In these examples, a variable was added to the WHERE clause of the query, changing the test for the word Mountain to a test for the variable value.

The Microsoft SQL Server Analysis Services supports named parameters defined in the Filter section of the MDX query designer. Named variables begin with the at (@) character. For example, @ProductModelName.
The Microsoft OLE DB Provider for Analysis Services 9.0 does not support parameterized queries. For more information, search for "Integrating Analysis Services with Reporting Services" on msdn.microsoft.com.
Support for variables by other multidimensional data providers are specific to the data provider. Check the data provider's documentation for more information.

The query designer associated with a data source type creates a query parameter for each variable identified. A special dataset is automatically created to provide valid values for each parameter. These query parameters are listed on the Parameter tab of the Dataset property pages. For every query parameter, a report parameter of type String is created. The query parameter values defined for the dataset are set to the corresponding report parameter. You can also create, edit, or delete these dataset parameters manually. For more information, see How to: Define Parameters in MDX and DMX Query Designer for Analysis Services (Report Designer).

The datasets to provide valid values for each query are created whenever the query is processed.

Udostępnij za pośrednictwem

Using Query Parameters with Specific Data Sources

Query Variables and Data Providers

Relational Database Data Sources

Multidimensional Database Data Sources

See Also

Tasks

Concepts

Other Resources

Help and Information

Dodatkowe zasoby