Parameters

Usage of parameters

Parameters (filters) are used to get capture user input into variables, that can be used in the query.

The following parameter types are supported:

Parameter Type	Multiple values?	How to use in the report SQL query
String	No	SELECT * FROM employees WHERE username = $filter OR full_name LIKE ‘%’+$FILTER+’%’;
Number	No	SELECT * FROM employees WHERE age > $age;
DatePicker	No	SELECT * FROM employees WHERE startDate BETWEEN $dateFrom AND DATEADD(DAY,1,$dateTo);
DateTimePicker	No	SELECT * FROM timeLog WHERE entry_time > $timeFrom;
Checkbox	No	SELECT * FROM employees WHERE is_active = $isActive;
Radiobutton	No	SELECT * FROM employees WHERE employee_type = $type;
Textarea	Yes	SELECT * FROM employees WHERE ID IN ($ListOfIds);
CheckboxList	Yes	SELECT * FROM employees WHERE office_id IN ($office);
Lookup	Yes	SELECT * FROM employees WHERE office_id IN ($office);
Dropdown	Yes	SELECT * FROM employees WHERE office_id IN ($office);

Parameters can be used in the query in two ways, either as variables (which is the most common use), or as macros.

Variables

Variables are prefixed by $ symbol in your SQL query (you do not need specify $ sign in the parameter name when you create a parameter). When using a variable, SQLwallet will automatically converts its type, for example DatePicker parameter variable can be used as a regular DATETIME variable.

SELECT * FROM Orders
WHERE orderDate BETWEEN $DateFrom AND DATEADD(DAY,1,$DateTo);

Macros

You also can use the parameters as macros by prefixing a parameter name with $$. Macros give you an ability to dynamically construct virtually any SQL query. All macros will be substituted in the query text “as is”. The advantage of macros is that you can use them as any part of SQL, even if the native SQL does not allow use of variables.

Consider a case when you need to switch to the different schema or database dynamically, depending on user input. All you need to do is to create a drop-down parameter with name “database”, which contains names of the databases as the options, and use it as a macro:

USE $$database;

Optional parameters

If you specify a parameter as optional, you will need to add the corresponding logic into your SQL query.

Example 1. If the user does not enter the customer name (optional parameter $CustName), the report will return all customers, otherwise only the customers with this name.

SELECT * FROM Customers
WHERE ($CustName IS NULL OR Customers.Name = $CustName);

Example 2. If the user does not enter the start date (optional parameter $DateFrom), the report will return all invoices for the last 1 month by default.

SELECT * FROM Invoices
WHERE InvoiceDate >= ISNULL($DateFrom, DATE_ADD(MONTH, -1, GETDATE());

Multi-value parameters

There is a special case for parameters that allow selection of multiple values, they are supposed to be used with IN clause:

Please note that in order to use the syntax above, your query must have type “Query” and not “Stored procedure”. If you query type is “Stored procedure”, then multiple valued parameters will be passed as comma-delimited string. This is required if you want to pass multiple values into a stored procedure, as it is the only way in SQL dialects that do not support arrays.

Query usage:

SELECT * FROM employees WHERE office_id IN ($office);

Stored procedure usage:

EXEC spGetEmployessFromOffices($office);

Specifying possible values

When you create a multivalue parameter, you need to specify list of possible values (except of Textarea parameter, which we’ll disuss later). You can do it in two ways: either specifying static list of values, or providing an SQL query, that will populate the list of values from database.

If you are using an SQL query, make sure it returns two columns named “Text” and “Value”. The Value will be passed into the parameter variable, and the Text will be shown to the user.

The parameter query can have a different data source than the main report output query.

You can use macros in the parameter query, referring the other parameters. This is useful in scenarios, where the parameter’s list of possible values depends on user selection of another parameter:

SELECT  Name AS Text, Id AS Value
FROM Genres
WHERE CategoryId = $Category
ORDER BY Name