A query parameter is an expression in curly brackets ({}) that is embedded in a SQL query within the SQL editor of a particular report. These parameters are substituted for inputs from UI controls or even entire SQL fragments when the query is executed. In this sense, all query parameters are substitutions. There are three types:
- Basic Parameters: Basic parameters are substitutions that merely reference a value provided by some UI control (e.g. user input). The general syntax is {<name>}, where <name> is a predefined parameter name. Basic parameters are provided by UI controls associated with transformations and filters.
- Transformations: A transformation is a substitution that produces an entire SQL snippet for transforming a value expression (e.g. column value), usually via the use of SQL functions and taking into account user input from an associated UI control. The general syntax for a transformation is {<expr>:<transform>}, where <expr> is an expression that selects the values to transform (e.g. via a column name) and <transform> is one of Cluvio's built-in transformation operators. The benefit of built-in transformations comes not just from shorter SQL but from the integration with predefined UI controls. Transformations are typically used repeatedly in SELECT, GROUP BY, ORDER BY and HAVING clauses of a SQL query.
- Filters: A filter is a substitution that produces a SQL snippet for a query condition whose values are provided by an associated UI control. Filters are used in WHERE or HAVING clauses of a SQL query and have the general syntax {<expr>=<filter>} where <expr> is an expression that defines the values to filter on and <filter> is the name for a predefined or custom filter. If <filter> is not a predefined filter, it is by definition a custom filter which can be further configured, e.g. with different types of UI controls.
In simple cases an <expr> in a transformation or filter is just a column name. However, if the expression involves the use of SQL functions, subqueries or similar, the expression must additionally be enclosed in square brackets, e.g. {[MIN(<col>)]:<transform>} or {[MIN(<col>)]:<filter>]}.
UI Controls
As mentioned, most query parameters are associated with UI controls on the dashboard, allowing viewers of the dashboard to influence the results shown in the reports on the dashboard without needing to see or change the underlying SQL (see Interactive Dashboards). In this sense, query parameters used in reports make the dashboard in which the reports are included configurable. The UI controls on a dashboard are the union of all the controls associated with the reports within the dashboard. Of course, if a report does not need to be (or should not be) configurable, query parameters need not be used.