specify-query-parameters.md

title	author
Specify Query Parameters	Anna Vekhina

Specify Query Parameters

This document provides general information on query parameters and describes common ways of utilizing parametrized SQL queries to filter data at the data source level.

Query Parameters Overview

A query parameter holds an external value that is inserted into an SQL statement before query execution. This value can be either static or dynamically generated by an associated expression.

The query parameter value is inserted into the resulting SQL query string in the "@QueryParameterName" placeholder's position.

Query parameters are used in the following scenarios:

• When filtering report data at the data source level using the Query Builder.

  The Query Builder helps you construct SQL queries when creating a new data-bound report or binding an existing report to an SQL data source,

  

  You can add queries to an existing SQL data source or edit existing queries:

  

  You can filter the constructed queries using query parameters. Expand the Parameters section in the Query Builder to add a new query parameter.

  

  Expand the Query Properties section and click the Filter property's ellipsis button to invoke the Filter Editor and filter data using the created query parameters.

  

  The criteria based on the specified query parameters are added as an SQL statement's WHERE part.

• When binding a report to a stored procedure provided by an SQL data source.

  The Data Source Wizard include the following page.

  

  If you select a stored procedure, the wizard creates a query parameter for each procedure parameter and allows you to configure the query parameters in the next Configure query parameters page.

  

Configure Query Parameters

The following properties are available for each query parameter:

• Name - specifies the parameter's name.
• Type - specifies the parameter value's data type.
• Expression - determines whether the actual parameter value is static or generated dynamically.
• Value - determines the query parameter's actual value. If the Expression option is enabled, the actual parameter value is produced dynamically by calculating an associated expression. This is useful when you map the query parameter value to the report parameter value. Refer to the next document section for more information.

Provide the Query Parameter Value

Below, you can see how a value is specified for a query parameter within the Data Source Wizard's page. You can also specify query parameter values in the Report Wizard or the Query Parameters dialog in the same way.

• Specifying a static value

  Choose a query parameter's value type and set a static value to the Value property according to the selected type.

  

• Providing a dynamic value

  Create a complex expression by expanding the Type property's drop-down list and selecting Expression.

  

  Click the Value property's ellipsis button and construct an expression in the invoked Expression Editor. You can map a report parameter that already exists in a report to a query parameter.

  

Pass a Multi-Value Parameter Value to a Query

You can map multi-value parameters to query parameters. For instance, the following query selects the orders whose IDs can be found within the values the @OrderID query parameter provides.

Pass a Multi-Value Report Parameter Value to a Stored Procedure

You cannot pass a multi-value parameter value to a stored procedure directly. Use one of the following expression functions:

• Use the Join() expression function to convert the array of parameter values to a string if you use MS SQL Server, MySQL or Oracle database systems.

  

• Use the CreateTable() expression function to prepare a table using values of several multi-value parameters.