Text Input

Usage

A text input filter is a UI control that accepts text input.

The filter is used as a condition on a SQL text expression (e.g. a text column).

Syntax

{<text_expr>=<variable_name>}

For example, a text input filter applied on a name column may look as follows:

... WHERE {name=name_filter} ...

The generated SQL expression for the text filter condition will be

... WHERE ... name = '<text input>'

where <text input> is the value entered by the user, subject to database-specific escaping of special characters.

Configuration

To configure a text filter, select the Text Input UI control type in the filter definition dialog.

If you enable Multiple values (comma-separated), the user input values are split by comma and each resulting text item is treated as a separate input value. The SQL condition generated by the filter treats separate values as alternatives, e.g. the input string a,b for a multi-value text filter used as {name=name_filter} would result in the following SQL:

... WHERE ... name IN ('a', 'b')

Select the Configuration tab to specify a default value and additional configuration options.

Value Selectors

A value selector allows to tap into SQL value expressions associated with the input text.

Count

SELECT {my_text_filter.count}

The .count selector produces the number of selected values:

• 0 or 1 for a single-select filter
• 0, 1 or more for a multi-select filter

Selected

SELECT {my_text_filter.selected}

The .selected selector produces a boolean expression: true if the filter has at least one value selected, false otherwise.

Min / Max

SELECT {my_text_filter.min}, {my_text_filter.max}

The .min and .max selectors produce a SQL string literal for the minimum/maximum of the selected values (numeric or lexicographic sort order) or NULL if there is no selection.

Value

SELECT {my_text_filter.value}

The .value selector produces a SQL string literal with the input value, or NULL when the filter has no selection.

Single-select filters

This selector is only applicable to single-select filters. It always produces NULL for a multi-select text filter.

Raw Value

SELECT * FROM {my_text_filter.value_raw}

The .value_raw selector results in direct substitution of the input value into the SQL. It can be used to dynamically alter the SQL query executed, e.g. to allow dynamically selecting a schema or table to query:

caution

The ability to use the filter input unquoted in SQL can allow SQL injections. Reports using such inputs should be shared only with trusted users and constrained via fixed parameters when embedding or via the use of drop-down filters with strict input validation.

Values

SELECT {my_text_filter.values}

The .values selector produces the input values as a comma-separated list in parenthesis (e.g. ('one', 'two', 'three', ...)), such as can be used with an WHERE IN clause, or a database- and value-type-specific empty set expression.

Values Concatenated

SELECT {my_text_filter.values_concat}

The .values_concat selector produces a single SQL string literal with all input values separated by comma (e.g. 'one,two,three'), or an empty string when the filter has no selection.

Values Array

SELECT {my_text_filter.values_array}

The .values_array selector produces the selected values as a SQL array (e.g. ['one','two','three']) or an empty array when the filter has no selection.

Raw Values

SELECT {my_text_filter.values_raw} FROM my_table

The .values_raw selector results in direct substitution of the input values into the SQL query, separated by comma. This may be useful to dynamically select columns in the query. The same caution applies as with value_raw for single-select text filters.