Share via

  • Article

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.