Specify Query Parameters

  • 4 minutes to read

Each query in an SqlDataSource contains a collection of query parameters. A query parameter holds an external value that is inserted into an SQL statement before the query is executed. This value can be static, if specified explicitly, or dynamic, when an expression is evaluated to obtain a value.

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

Use query parameters to:

  • Filter report data at the data source level

    The Query Builder helps you construct SQL queries to create a data-bound report or bind a report to an SQL data source,

    QueryParameters_CreateQuery

    You can also add or edit queries in an SQL data source.

    report-explorer-field-list-manage-queries

    Click Edit Parameters. Configure query parameters in the invoked Query Parameters dialog.

    QueryParameters_QueryBuilder

    Click Filter to invoke the Filter Editor where you can specify filter conditions that include the created query parameters.

    QueryParameters_FilterBuilder

    The specified filter conditions are added as an SQL statement's WHERE part.

    The Filter button is not available for custom SQL queries. Specify the query parameter in the query instead.

    Specify a Parameter in a Custom Query

    Refer to the Filter Data at the Data Source Level topic for more information.

  • Specify parameters for a stored procedure

    The Report Wizard and Data Source Wizard include the Create a query or select a stored procedure page. If you select a stored procedure, the wizard creates a query parameter for each procedure parameter and allows you to configure the created parameters on the Configure query parameters and preview the result page.

    QueryParameters_StoredProcedure

    See Bind a Report to a Stored Procedure for more information.

Use the query's Parameters property to access the query parameters collection. These include parameters created in the Query Builder and generated for the data source's stored procedure. Use the SelectQuery.FilterString property to access the query's filter string.

QueryParameters_FilteringScenario

You can add query parameters in the Query Parameters dialog and modify the filter in the FilterString Editor.

Configure Query Parameters

The following properties are available for a query parameter:

  • Name

    The parameter name.

  • Type

    The parameter value's data type.

  • Expression

    Determines whether the parameter value is static or dynamic.

  • Value

    Specifies the parameter value. If the Expression option is enabled, this property contains an expression that is parsed and processed to obtain the parameter value. An expression can include data source fields or report parameters.

Pass a Multi-Value Parameter Value to a Query

You can bind query parameters to multi-value report parameters.

parameters-multi-value-result

For instance, the following query selects orders whose IDs are listed in the @OrderIDs query parameter.

  1. Create a multi-value report parameter.

    parameters-multi-value-report-parameter

  2. Invoke the Query Builder. Set the query parameter value to the expression that contains the report’s multi-value parameter.

    MultiValueParameterInQuery

  3. In the Query Builder, invoke the Filter Editor and specify a filter string that includes the newly created query parameter.

    multi-value-param

If the multi-value report parameter accepts null values and its value is not pre-specified, set the following filter string:

multi-value-param-is-null

In this case, all data is initially loaded to the report data source. When a user selects report parameter values, the report data source is filtered.

Pass a Multi-Value 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.

    JoinFunction

    • MS SQL Server

      Split the string that the Join() function returns into an array within the stored procedure.

      create procedure [dbo].[GetOrdersByID]
      @data varchar(100)
      as
      select * from Orders
      where OrderID in (select value from STRING_SPLIT(@data,','));
      
      NOTE

      You can use the STRING_SPLIT function in MSSQL server 2016 or later.

    • Oracle

      The following code demonstrates how to implement an Oracle-specific stored procedure that takes a string of parameters:

      create or replace procedure get_movies(titles in varchar, rc out sys_refcursor) as
      begin
      open rc for
      select * from movies
      where title in (
          select regexp_substr(titles, '[^,]+', 1, level) from dual
          connect by regexp_substr(titles, '[^,]+', 1, level) is not null
      );
      end;
      
    • MySQL

      The following code demonstrates how to implement a MySQL-specific stored procedure that takes a string of parameters:

      create procedure `get_movies`(in titles varchar(100))
      begin
      select *
      from movies
      where find_in_set(title, titles);
      end;
      
  • Use the CreateTable() expression function to prepare a table using values of several multi-value parameters.

    CreateTable

    The following code demonstrates how to get values from a passed table within a stored procedure with a User Defined Table Type parameter:

    create type OrderParameters as table (value int, dt datetime);
    
    create procedure [dbo].[get_orders]
    @args OrderParameters readonly
    as
    select * from Orders
    where OrderId in (select value from @args)
    or OrderDate in (select dt from @args)