Concepts → Individual Filter
About an individual filter
An individual filter is a pill in the Individual Filter tray in the Insight panel of the Analyzer. In the Filter panel for the pill, you can specify the filter operator and one or more filter values. The result is a filter expression that removes the unwanted rows from the insight query. In this regard, an individual filter is synonymous with a SQL WHERE clause. A SQL WHERE clause filters out unwanted rows from a query.
In the Analyzer for an insight, you can specify one or more individual filters for a supported visualization.
The Rich Text visualization does not support individual filters.
For both an Incorta Analyzer Table and an Incorta View, the Analyzer only supports the Listing Table and Aggregated Table visualizations.
In order to create or edit the following with the Analyzer, you must belong to a group assigned with necessary security role(s) AND be the owner of the object or have Edit access rights to the object:
- an insight on a dashboard tab
- an Incorta Analyzer Table in a physical schema
- an Incorta View in a business schema
The objects are a dashboard, physical schema, and business schema, respectively. An individual filter is not visible to a user with View or Share access rights to a dashboard, physical schema, or business schema.
Filter expression context for an individual filter
The parent object of an individual filter pill is either an insight, an Incorta Analyzer Table, or an Incorta View. The parent object governs the filter expression context and, in doing so, determines the evaluation and application of the filter expression.
The filter expression context governs the referenceable and filterable columns. When a column from a physical schema table or runtime business view is an individual filter pill, you specify the filter operator and one or more filter values within the Filter panel in the Analyzer to complete the filter expression. Alternatively, you can add a formula from the Data panel to the Individual Filter tray and use the Formula Builder to create a complex filter expression.
An individual filter for an Incorta Analyzer Table
An Incorta Analyzer Table is a derived table, meaning that it represents a query of one physical schema table or two or more related physical schema tables. Physical schema tables are not derived. Incorta evaluates and applies an individual filter when processing the Incorta Analyzer Table query for the load job that is either for the entire physical schema or for the Incorta Analyzer Table.
An individual filter for an Incorta View
An Incorta View is a type runtime business view. Unlike a business schema view, an Incorta View allows for one or more individual filters. As a runtime business view, an Incorta View is only available in the runtime processing of the Analytics Service.
An individual filter for an insight
The Analytics Service applies an individual filter in the query plan predicate, the WHERE clause, of an insight query. A query plan relates to one or more measures for the given insight. The Analytics Service evaluates the query at runtime when the insight renders on the dashboard tab.
Visualizations such as the KPI visualization allow for multiple measures. These measures may come from disparate schemas or tables without relationships. In these cases, an individual filter may not reference a filterable column for the specific aggregated measure.
Supported filterable columns based on the filter expression context of the individual filter
The following table details the filterable columns that you can use or reference for each context. These columns can be data-backed columns or formula columns:
| Filterable columns | Context: Incorta Analyzer Table | Context: Incorta View | Context: Insight |
|---|---|---|---|
| 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.Incorta_SQL_Table.Column | ✔ | ✔ | |
| Physical_Schema.Materialized_View.Column | ✔ | ✔ | ✔ |
| Physical_Schema.Materialized_View.Formula_Column | ✔ | ✔ | ✔ |
The joins between physical schema tables and the query plan for the insight measure work together to determine if an individual filter uses a filterable column. For example, you can specify a physical schema column in a parent table as an individual filter for a measure in a child table. In contrast, a physical schema column from a child table is not a filterable column for a parent table as an individual filter, even when it is the foreign key reference. The behavior is the same for a formula as an individual filter. The following are the filterable columns that you can use or reference in a formula column when creating an individual filter pill:
- The filterable column is a dimension or measure column, that is, it exists as pill in either the Grouping Dimension or Measure trays in the Analyzer.
- The filterable column has a sibling column from the same physical schema table, and that sibling is a dimension or measure.
You can use the In Query filter operator in an individual filter expression. The In Query operator allows you to specify a formula expression where you query the parent table values using a query or queryDistinct function that references the child table column (or any other table with no relation to the parent table) to generate the filter values.
When a measure and/or dimension pills are columns or formula columns from an Incorta View, all filterable columns must be from the same Incorta View. Similarly, if the measures and/or dimensions pills are not columns or formula columns from an Incorta View, all filterable columns must not be from an Incorta View.
Types of individual filters in the Analyzer
When creating an individual filter pill, you can use either a formula or a column of any data type. When a column from a physical schema or a runtime business schema is an individual filter pill, you specify the filter operator and one or more filter values within the Filter panel in the Analyzer to complete the filter expression. Selected columns can be data-backed columns that reference data that persists to shared storage or formula columns.
Alternatively, you can add a formula from the Data panel to the Individual Filter tray and use the Formula Builder to create the filter expression. For a formula, you specify a filter expression or reference a filter expression session variable. The context of the individual filter determines the types of columns that you can use or reference. For more information, see Supported filterable columns based on the filter expression context of the individual filter.
Individual filter pill as a Column
An individual filter pill represents a filter expression. The filterable column in the filter expression can be a key, dimension, or measure column. The filterable column can be a data-backed column or formula column.
A filter expression for the individual filter pill consists of the filterable column, a filter operator, and one or more filter values. To configure the Filter properties, select the pill in the Individual Filter tray in the Insight panel. In the Filter panel, configure the following properties to specify the filter expression.
| Property | Control | Description | Comments |
|---|---|---|---|
| Column name | label | Displays the fully-qualified name of the selected column pill. | You can select the information icon to view more details about the column |
| Column label | text box | Displays the editable label for the column pill. | |
| Operator | drop down list | Select the filter operator to use in the filter criteria | |
| Values | radial or check box | Select the filter value(s). Some filter operators do not require a filter value such as Null and True. | In addition to specific values, you can reference variables according to the individual filter context. For more information, see Valid referenceable variables in an individual filter. |
| Values | text box | Enter filter values. | Use the + control to add the filter values. You can add more than one filter value, but only select one for the filter expression. |
| Disable filter | toggle | Enable to disable the filter expression. | This property overrides the toggle in the Filter Values panel. |
Individual filter pill as a Formula
You use the Formula Builder to create the filter expression for an individual filter pill that is a formula. This means that you can evaluate a formula expression for an individual filter. Depending on the filter operator, a formula expression can itself evaluate specific conditions and values. A filter value can be a literal value, an array of values, or a referenceable object.
As such, you can also specify the dynamic evaluation of an individual filter using a filter expression session variable.
For more details, see Valid referenceable variables in an individual filter.
The following are the properties of an individual filter that uses a formula.
| Property | Control | Description | Comments |
|---|---|---|---|
| Column label | text box | Displays the editable label for the formula pill. | |
| Dynamic | toggle | Enable this option to select a filter expression session variable as a value | |
| Formula | text box | When selected, it invokes the Formula Builder to create the filter expression. | This property is visible only when you disable Dynamic |
| Operator | drop down list | Select the filter operator to use in the filter expression | This property is visible only when Dynamic is disabled. |
| Values | radial or check box | Select the filter value(s). Some filter operators do not require a filter value such as Null and True. With the Dynamic property enabled, search for and then select a date system variable or filter expression session variable. | In addition to specific values, you can reference variables according to the individual filter context. For more information, see Valid referenceable variables in an individual filter. |
| Disable filter | toggle | Enable to disable the filter expression. | This property overrides the toggle in the Filter Values panel. |
Certain filter operators afford various options for selecting one or more filter values. For example, when using the In Query operator, you can use a query expression to generate the list of filter values. Also, the In and Not In operators allow for the selection of none, one or more, or all filter values.
Valid referenceable variables in an individual filter
When creating the filter expression for an individual filter pill, whether using a column or a formula, you can reference different variables as filter values. There are multiple factors that govern which types of variables you can reference. These factors are as follows:
- The type of the filter: column, filter expression formula, or dynamic formula
- The context of the individual filter: insight, Incorta Analyzer Table, or Incorta View
- The filter expression you create for a formula individual filter
- The operator you select
Here are the types of variables that you can reference as a filter value (scalar or array) in an individual filter:
- date system varaible
- external session variable
- filter expression session variable
- global variable
- internal session variable
- miscellaneous system variable
- presentation variable
The following table shows the matrix of referenceable variables and the context of the individual filters.
| Referenceable Variables | Context: Incorta Analyzer Table | Context: Incorta View | Context: Insight | Type: Column (data-backed or formula) | Type: Formula (using filter expression) | Type: Dynamic Formula |
|---|---|---|---|---|---|---|
| date system varaible | ✔ | ✔ | ✔ | ✔ | ✔ | ✔ |
| external session variable | ✔ | ✔ | ✔ | ✔ | ✔ | |
| filter expression session variable | ✔ | ✔ | ✔ | ✔ | ||
| global variable | ✔ | ✔ | ✔ | ✔ | ||
| internal session variable | ✔ | ✔ | ✔ | ✔ | ✔ | |
| miscellaneous system variable | ✔ | ✔ | ✔ | ✔ | ✔ | |
| presentation variable | ✔ | ✔ | ✔ |
View access rights
In order to reference a column or variable in an individual filter, you must have View access rights to the grandparent object (physical schema or business schema) or the variable.
A global variable is available to all users in a given tenant. It is not possible to assign access rights to a global variable.