Concepts → Filter Expression

About Filter Expressions

Basically, filters help you narrow down your results based on a specified set of attributes and values you choose. A filter expression is a string that defines one or more logical expressions to be used in filtering data records. A filter expression specifies the conditions that data must meet to be included in the query result set. It resembles the WHERE clause in SQL statements. Incorta Analytics supports defining filters on different levels.

A filter expression is a boolean expression that returns either True or False.

The system evaluates a filter expression at the runtime of the related object. For example, dashboard filters are evaluated during the rendering of each dashboard, while load filters are applied while loading the physical schema table data.

Filter expression context

The filter expression context determines the validation rules for the expression itself and the limitations of the expression.

For example, the expression context of a physical schema table load filter allows only for referenceable objects within the scope of the same physical schema table and the Loader Service. For this reason, when creating a load filter for a physical schema table, you can use only plain column names, and not the fully qualified column names. Thus, it is not possible to reference a column in another table even in the same physical schema. In addition, you cannot reference a session variable, as a session variable is a runtime reference to an in-memory object in the Analytics Service.

A filter expression can consist of built-in functions, built-in system variables, and other referenceable objects valid for the expression context. For example, you can reference internal session variables when creating a runtime security filter for a physical schema table, while you cannot reference the same when creating a load filter expression.

You can create filter expressions with the Formula Builder when creating any of the following as allowed by the expression context:

• Filter Expression Session Variable
• Formula Runtime Security Filter for a Physical Schema Table or Materialized View
• Formula Individual Filter for a Physical Schema Incorta Table
• Load Filter for a Physical Schema Table
• Formula Individual Filter for an Incorta View
• Formula Filter Option for a Dashboard
• Formula Applied Filter for a Dashboard
• Formula Individual Filter for an Insight
• Formula Filter for an Insight Measure (not available for all insight types)

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 (schema or business schema) or the variable.

Examples of a filter expression

Example #1

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.

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

Example #2

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%"
	)
)