Concepts → Filter Expression
A filter expression allows you to narrow down results based on specific criteria. A filter expression evaluates which rows return
A filter expression has a context which references not only the parent object of the filter, but also the results and criteria. Examples of expression contexts include an individual filter for an insight, an applied filter for a dashboard, a runtime security filter for a physical schema table, and a dashboard runtime filter.
Depending on the tool and the context, there are various ways to create a filter expression such as using the Filter bar of a dashboard or the Filter panel in the Analyzer. Typically, you use the Formula Builder to create a complex filter expression.
An example of a filter expression is to return only the rows from a physical schema table where the “PROD_CATEGORY” column has “Electronics” as a row value. In this regard, a filter expression consists of:
- a filterable column ( “PROD_CATEGORY” )
- a filter operator ( Equals )
- a filter value ( “Electronics” )
When using the Formula Builder, a filter expression can be much more complex as the tool supports using built-in functions and variables. Regardless, the elements of the filter expression — filterable column, filter operator, and filter value — are essentially the same.
With the Formula Builder, it is possible in certain contexts to specify a formula expression such as
1=0. A formula expression will evaluate as a filter expression. A filter expression evaluates which rows return
true. In the case of
1=0, the result is always
false for all rows.
A filterable column is a data-backed column or formula column.
A filterable column shares the filter expression context such as an individual filter for an insight or a dashboard runtime filter for a dashboard.
A formula expression can encapsulate the filterable column or filterable columns within the filter expression.
Not all filter expression contexts support all filterable columns types. The following are types of possible filterable columns:
For the purposes of a filterable column, an alias table is the same as a physical schema table.
A filter operator defines the operation for the filter expression. The available operators depends on the data type of the filterable column and the filter expression context. The following table does not account for all possible contexts for a filter expression.
|Does Not Equal||✔||✔||✔||✔||✔||✔||✔|
|Greater Than or Equals||✔||✔||✔||✔||✔||✔||✔|
|Less Than or Equals||✔||✔||✔||✔||✔||✔||✔|
|Does Not Start With||✔||✔|
|Does Not Contain||✔||✔|
Depending on the context, a filter expression can consists of one or more filterable columns. Using the Formula Builder, it is also possible to specific in certain contexts a complex filter expression using mathematical operations (
*,\,+,-) or certain types of built-in functions such
A filter expression supports equality and relational operators as well built-in functions such as:
- Boolean functions
- Conditional statements
- Conversion functions
- Arithmetic functions
- Date functions
- String functions
- Query functions
Not all built-in functions are available for all contexts.
For a filterable column of the type
double, the equality and relational operators may not function as desired. A double represents a floating point. Consider using various built-in arithmetic functions to best approximate equality comparisons.
For a filterable column of the type
text, equality and relational operators are case sensitive. You must use the same case and the full string in order to create a valid comparison. Consider using various built-in String functions such as
upper() to compare the values in a filterable column with a string literal.
Boolean functions return
false values. A filter expression with a boolean functions exposes the filterable column within the expression itself.
A filter value depends on the filter expression context and the filter operator. A filter value can be one of the following:
- a scalar value (literal) such as string (
"Electronics") or integer (
- a referenceable column
- an array of scalar values
- date system varaibles
- external session variable
- filter expression session variable
- global variable
- internal session variable
- miscellaneous system variable
- presentation variable
Not all values types are available for all contexts.
The filter expression context determines the validation rules for the expression itself and the limitations of the expression.
For example, it is valid to reference an internal session variable when creating a runtime security filter for a physical schema table, but it is invalid to reference an internal session variable in the context of a load filter. The expression context of a physical schema table load filter is the scope of the same physical schema table.
A filter expression explicitly defined within the context of a specific object cannot be referenced in another context. A filter expression session variable, however, is a referenceable object that may be repurposed in numerous contexts.
You can use a formula to create a complex filter expression that contains conditional logic, built-in functions, and other referenceable objects such as variables that are valid within the given expression context.
To create a complex filter expression, use the Formula Builder. The following are expression contexts for complex filter expressions:
- Filter expression session variable
- Formula for a runtime security filter for a physical schema table or materialized view
- Formula for a load filter for a physical schema table or materialized view
- Formula for an aggregate filter for an Incorta Table
- Formula for an aggregate filter for an Incorta View
- Formula for an aggregate filter for an insight
- Formula for an individual filter for an Incorta Table
- Formula for an individual filter for an Incorta View
- Formula for an individual filter for an insight
- Formula for an insight measure (not available for all visualizations)
- Formula for filter option for a dashboard filter
- Formula for an applied filter for a dashboard filter
- Formula for an prompt for a dashboard filter
In order to reference a column or variable in a filter expression, you must have View access rights to the grandparent object (physical schema or business schema) or the variable.
The following is an example of a filter expression for a dashboard applied filter formula. This filter expression filters the dashboard data according to the logged-in user. Only data related to the
Electronics category is displayed if the logged-in user is
user, and only data related to the
Hardware category is displayed if the logged-in user is
or( and( SALES.PRODUCTS.PROD_CATEGORY = "Electronics", in( $user, "user" ) ), and( SALES.PRODUCTS.PROD_CATEGORY = "Hardware", in( $user, "admin" ) ) )
This following example is for a load filter expression created for a physical schema table to control which data to load from the data source. When loading the table data, only records that fulfill the two conditions (the first name starts with
J and the last name starts with
E) are retrieved. Notice the use of plain column names rather than the fully qualified ones.
and( like( first_name, "J%" ), like( last_name, "E%" ) )