Pass Query Parameters

• May 29, 2019
• 4 minutes to read

A query parameter holds an external value that is inserted into an SQL statement before query execution. This value can be 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.

In dashboards, query parameters are used in the following scenarios:

• When filtering dashboard data on the data source level using the Query Builder.

• When binding a dashboard to a stored procedure an SQL data source provides.

You can access query parameters using the Parameters property of the query the dashboard’s SqlDataSource component exposes. These parameters include the ones you created within the Query Builder or that were generated for the data source’s stored procedure.

• Configure Query Parameters
• Provide the Query Parameter Value
• Pass a Multi-Value Parameter Value to a Query
• Pass a Multi-Value Dashboard Parameter Value to a Stored Procedure

Configure Query Parameters

The Query Editor dialog contains the added query parameters’ settings.

The following properties are available for each query parameter:

Name	Specifies a parameter’s name.
Type	Specifies the parameter’s type.
Expression	Specifies whether an expression is used to specify a parameter’s value.
Value	Specifies the parameter’s value. If the Expression check box is enabled, you can invoke the Expression Editor dialog to specify the required expression or select an existing dashboard parameter to pass to the SQL query.

Use the Add / Remove buttons to add or remove query parameters.

Click the Preview… button if you need to preview the query result. Click Finish to complete query modification.

Provide the Query Parameter Value

Below, you can see how to specify a value for a query parameter.

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

Enable the Expression checkbox for a parameter.

The following options are used to calculate the parameter’s actual value dynamically:

• Create a complex expression by expanding the Value property’s drop-down list and selecting Expression Editor.

  

  Construct an expression in the invoked Expression Editor.

  

• Map a new dashboard parameter to a query parameter by expanding the Value property’s drop-down list and selecting Add Dashboard Parameter.

  

  Specify dashboard parameter settings in the invoked Dashboard Parameter dialog. Specify the dashboard parameter type according to the type of the corresponding query parameter. Click OK to exit the dialog.

  

• Map a dashboard parameter that already exists in a dashboard to a query parameter by expanding the Value property’s drop-down list and selecting the parameter you want to use. An appropriate expression string is generated automatically.

  

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.

The parameter values are 10248, 10249, and 10250. The image below show the preview result:

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

You cannot pass a multi-value parameter value to a stored procedure directly. Use the Join() expression function in the Expression Editor to convert the array of parameter values to a string if you use MS SQL Server, MySQL or Oracle database systems.

Below are examples of how to get parameter values from the passed string within a stored procedure.

MS SQL Server

Split the string 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 that you can use the (STRING_SPLIT)[https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql] function in MSSQL server 2016 and later.

You can also implement a stored procedure with a (User Defined Table Type)[https://docs.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine] parameter. In this instance, use the CreateTable() expression function to prepare a table from values of several multi-value parameters.

The following code demonstrates how to implement a stored procedure that gets values from the table the CreateTable() function produces:

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)

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;