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

Custom SQL in DB input block

Omniscope database input block allows the users to extract and query the data by picking the DB table and setting the 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 to live query it 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 take the value from: 

- 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 - the difference between the Parameters (project-specific or app-level) and the Report Variables is that the parameters are accessible from the workflow area and are controlled by the project Editors (image on the left), while the Variables are set on the Report level and are exposed to the report Viewers (image on the right), to empower them to influence the data exploration (e.g. to test 'what if' scenarios, or switch fields on the X/Y axis).