Use Parameter or Variable-driven Custom SQL in DB Live Query Mode

Custom SQL in DB input block

The Omniscope database input block allows users to extract and query data by selecting the DB table and setting filters, without any coding.

The Custom SQL query is an advanced option that allows the users to enter the SQL statement to import the desired data or execute live queries directly from a Report.

Note: The syntax of the statement is vendor specific, so you should refer to your database documentation when constructing.

Input parameters in custom SQL

The Database block allows you to include a list of values from your input data inside your SQL statement. Connect the block containing the values to your Database source block. Once connected simply insert [[FIELD NAME]] into your SQL statement, where FIELD NAME corresponds to the field in the input data.

For example the following statement:

SELECT * FROM `Customers` WHERE `Customer Name` IN ([[Customer Name]])

when executed will replace [[Customer Name]] with all the values in the Customer Name field in the input data as a comma separated list.

If you have multiple input parameters, these use all unique non-null values from each referenced field from the input data. The relationship between values across different input fields is irrelevant.

App parameters and Report variables in custom SQL

If using either project-specific or app-wide Parameters, you can specify these using {{paramName}} syntax, e.g.:

SELECT * FROM `Customers` WHERE `Customer Name`={{Customer_Param}}

However you should configure a "Data change detection" behaviour to ensure parameter changes are propagated in a timely fashion, as they will not otherwise be auto-detected.

You can also link Report variables* in your custom SQL using the same syntax. This will expand the area of variable influence to the workflow area and enable the report viewer to influence / execute the DB query.

Note. The database block must be in live query mode.

For example, let's say you had a text choice report variable "Type" with options "Consumer" and "Trade". You should also use the optional syntax shown below to provide default values:

SELECT * FROM `Customers` WHERE `Customer Type`={{Type='Consumer'}}

Now, when the user of the report changes the "Type" variable between "Trade" and "Consumer", the report will update to show the dynamically changed SQL query. In cases where the SQL query is executed outside of a report (e.g. in the workflow app), the default 'Consumer' will be used.

An app parameter in SQL, enclosed in {{ and }}, will resolve its value based on the following hierarchy:

- a report variable of the same name (if present),

- a project parameter of the same name (if found), 

- a server-wide parameter of the same name (if found), 

- using the default value e.g. {{param='default'}}.

*For clarity: Parameters can be project-specific or app-level, and they are controlled by project Editors within the workflow. These are accessible throughout the workflow area (see image on the left). On the other hand, Report Variables are set at the report level and are exposed to report Viewers (see image on the right). This allows Viewers to dynamically influence data exploration, such as testing 'what if' scenarios or choosing X/Y axis fields.