Concepts → Filter Expression

About a filter expression

A filter expression allows you to narrow down results based on specific criteria. A filter expression evaluates which rows return true.

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.

About a filterable column

A filterable column is a data-backed column or a 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:

• Business_Schema.Incorta_View.Column
• Business_Schema.Incorta_View.Formula_Column
• Business_Schema.View.Column
• Business_Schema.View.Formula_Column
• Physical_Schema.Table.Column
• Physical_Schema.Table.Formula_Column
• Physical_Schema.Incorta_Analyzer_Table.Column
• Physical_Schema.Incorta_Analyzer_Table.Formula_Column
• Physical_Schema.Materialized_View.Column
• Physical_Schema.Materialized_View.Formula_Column

About a filter operator

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.

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 lower(), inList(), and daysBetween().

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

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 string or 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 lower() or upper() to compare the values in a filterable column with a string literal.

Boolean functions return true or false values. A filter expression with a boolean functions exposes the filterable column within the expression itself.

About a filter value

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 (1001),
• 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

Filter expression context

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 complex filter expression with a formula

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 Analyzer 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 Analyzer 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

View access rights

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.

Examples of a complex filter expression

Example A

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 admin.

Copy
or(
    and(
        SALES.PRODUCTS.PROD_CATEGORY = "Electronics",
        in(
            $user,
            "user"
        )
    ),
    and(
        SALES.PRODUCTS.PROD_CATEGORY = "Hardware",
        in(
            $user,
            "admin"
        )
    )
)

Example B

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.

Copy
and(
    like(
        first_name,
        "J%"
    ),
    like(
        last_name,
        "E%"
    )
)