Using Query Parameters with Specific Data Sources (Report Builder 2.0)
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 Framework 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 used 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 |
|---|
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 Framework 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 Properties Dialog Box, Parameters (Report Builder 2.0).
Multidimensional Database Data Sources
For a multidimensional database like 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 |
|---|
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 |
|---|---|---|
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 the MDX Query Designer for Analysis Services (Report Builder 2.0).
The datasets to provide valid values for each query are created whenever the query is processed.
See Also