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