Use Query Parameters

• Feb 10, 2023
• 9 minutes to read

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

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

Note

Various SQL extensions can require a special character as the variable’s first character. For instance, this can be the “at” sign (@) for Transact-SQL or the colon (:) for PL/SQL. Insert this character before the parameter name within the custom SQL query.

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

• To filter data at the data source level.

• To bind a dashboard to a stored procedure an SQL data source provides.

Use the Parameters property of the dashboard’s SqlDataSource query to access query parameters created within the Query Builder and generated for the data source’s stored procedure.

Configure Query Parameters

The Data Source Wizard dialog contains a list of available query parameters and its settings.

The following properties are available for each query parameter:

Name

Specifies the query parameter’s name.

Type

Specifies the parameter value’s data type. Set this property to Expression to generate parameter values dynamically.

Result Type

Specifies the data type of the expression’s result value. This property is enabled if the query parameter type is set to Expression.

Value

Determines the query parameter’s actual value.

You can specify a static value according to the selected value data type. Alternatively, construct an expression to generate parameter values dynamically. Click this property’s ellipsis button to invoke the Expression Editor and create an expression. This ellipsis button is enabled if you set the query parameter type to Expression.

Use the Add/Remove buttons to add or remove query parameters. Click Finish to complete the query modification.

Specify the Query Parameter Value

You can specify a static or a dynamic value for a query parameter in the Data Source Wizard dialog after you choose a query or stored procedure.

Specify a Static Value

Choose a query parameter value type and set a static value in the Value field according to the selected type.

Specify a Dynamic Value

To change the query parameter value dynamically, bind it to the corresponding dashboard parameter. To do this, in the Data Source Wizard window, change the parameter’s Type to Expression and specify the type of the return value in the Result Type field. Click the Value property’s ellipsis button to specify the expression.

Construct an expression in the invoked Expression Editor. You can find the list of dashboard parameters in the Fields section.

Click OK to apply the expression.

You can also work with query parameters in the Properties section of the Query Builder:

Bind a Dashboard Parameter to a Query Parameter

Pass a Dashboard Parameter Value to a Custom SQL Query

You can create a custom SQL query with a query parameter and bind it to a dashboard parameter to change the value dynamically.

To do this, follow the steps below:

1. In the Data Source Wizard, specify a custom SQL query in the SQL String editor.

   The following custom SQL query contains the WHERE clause that compares the OrderID field to the OrderIdParam query parameter:

   

2. On the next page of the Data Source Wizard, click Add to create a query parameter.

3. Specify the query parameter’s settings.

   In the Name field, specify the name used in the query. Set the parameter’s type to Expression and bind the query parameter to a dashboard parameter:

   

4. Click Finish to add the query.

For information on how to bind a query parameter to a multi-value dashboard parameter in a custom SQL query, refer to the following section: Pass a Multi-Value Dashboard Parameter Value to a Custom SQL Query.

Pass a Dashboard Parameter Value to a Stored Procedure

To bind a stored procedure parameter to a dashboard parameter, follow the steps below:

1. Select a stored procedure in the Data Source Wizard.

   

2. On the next page of the Data Source Wizard, select a query parameter and specify its settings.

   Change the parameter’s type to Expression and bind the query parameter to a dashboard parameter:

   

3. Click Finish to apply the specified settings.

For information on how to bind a query parameter to a multi-value dashboard parameter in a stored procedure, refer to the following section: Pass a Multi-Value Dashboard Parameter Value to a Stored Procedure.

Bind a Multi-Value Dashboard Parameter to a Query Parameter

The following sections show how to pass a multi-value dashboard parameter to a SELECT SQL query, custom SQL query, and stored procedure.

Pass a Multi-Value Dashboard Parameter Value to a Query

To map a multi-value dashboard parameter to a query parameter, follow the steps below:

1. Create a dashboard parameter. In parameter settings, select the Allow Multiselect checkbox and specify the Look-Up settings.

   

   Note

   The query that supplies a list of values to the dashboard parameter (the dynamic look-up list) should be different from the query that supplies data to the dashboard.

2. In the Data Source Wizard window, run the Query Builder and specify the query. To specify a filter criteria, click the ellipsis button next to the Filter field in the Query Builder.

   

3. In the invoked Filter Editor, specify the condition with the Is any of or Is none of operator:

   

4. Bind the query parameter to the corresponding dashboard parameter.

The following query selects the orders that IDs can be found within the @OrderID query parameter values:

For more information on how to filter SQL queries, refer to the following article: Filter Queries.

Pass a Multi-Value Dashboard Parameter Value to a Custom SQL Query

You cannot pass a multi-value parameter to a custom SQL query directly. To pass a multi-value parameter to a custom SQL query, convert the array of parameter values to a string with the Join() function and then split the resulting string in the query.

Note

Do not pass dynamic lookup dashboard parameters to a custom query or a stored procedure with the Select All option enabled.

To map a multi-value dashboard parameter to a custom SQL query parameter, follow the steps below:

1. Create a dashboard parameter. In its settings, select the Allow Multiselect checkbox and specify the Look-Up settings.

   

   Note

   The query that supplies a list of values to the dashboard parameter (the dynamic lookup list) should be different from the query that supplies data to the dashboard.

2. In the Data Source Wizard, enter or edit your query in the SQL String editor. The image below displays a custom query for a Microsoft SQL Server database.

   

   The STRING_SPLIT function is used to split a string into rows of substrings. For information about techniques for other databases, refer to the following sections:

   • Oracle
   • MySQL
   • IBM DB2

3. On the next page, bind the query parameter to the dashboard parameter you created earlier. In the Expression Editor, pass the name of the dashboard parameter to the Join() function.

   

   The resulting query parameter’s settings may appear as follows:

   

For more information about custom SQL queries, refer to the following article: Custom SQL Queries.

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

You cannot pass a multi-value parameter value to a stored procedure directly. To pass a multi-value parameter to a stored procedure, convert the array of parameter values to a string with the Join() function if you use the Microsoft SQL Server, MySQL, or Oracle database system. The stored procedure code should parse the resulting string to get the dashboard parameter values.

Note

Do not pass dynamic lookup dashboard parameters to a custom query or a stored procedure with the Select All option enabled.

To map a multi-value dashboard parameter to a stored procedure parameter, follow the steps below:

1. Create a dashboard parameter. In its settings, select the Allow Multiselect checkbox and specify the Look-Up settings.

   

   Note

   The query that supplies a list of values to the dashboard parameter (the dynamic lookup list) should be different from the query that supplies data to the dashboard.

2. In the Data Source Wizard, select a stored procedure.

   

3. On the next page, bind the query parameter to the dashboard parameter you created earlier. In the Expression Editor, pass the name of the dashboard parameter to the Join() function.

   

   The resulting query parameter’s settings may appear as follows:

   

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 function in Microsoft SQL Server 2016 and later.

You can also implement a stored procedure with a User Defined Table Type parameter. In this case, use the CreateTable() expression function to create 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;

IBM DB2

The following code is the stored procedure for an IBM DB2 database that takes a string of parameters:

CREATE OR REPLACE PROCEDURE "GetOrdersByID" (data varchar(100)) READS SQL DATA DYNAMIC RESULT SETS 1  
    BEGIN
        DECLARE cursor_1 CURSOR WITH RETURN FOR
        SELECT *
        FROM "Orders"
        WHERE "OrderID" in (SELECT * from XMLTABLE('$d/r/i' PASSING XMLPARSE(DOCUMENT CAST('<r><i>' || replace(data , ',' , '</i><i>') || '</i></r>' as CLOB) ) as "d" COLUMNS ITEM VARCHAR(255) PATH '.'));
        OPEN cursor_1;
    END