Concepts → Filter Option
About a filter option
A filter option is a type of dashboard filter. A filter option represents a filter expression that affects all applicable insights on all tabs of the given dashboard.
A filter option behaves the same way as an applied filter. The difference between a filter option and an applied filter is that a dashboard consumer is aware of the filter option. A padlock icon next to the dashboard title in the action bar of a dashboard indicates one or more filter options.
The tooltip for the padlock icon next to the dashboard title incorrectly refers to security options. A filter option is not by itself inherently related to security such as Discretionary Access Control (DAC), Role Based Access Control (RBAC), or Row Level Security (RLS).
A dashboard consumer with View access rights to the dashboard can select a filter option in the Filter Options menu. When the dashboard consumer selects the padlock icon next to the dashboard title, the dashboard Filter Options menu opens. The dashboard consumer can select the available filter option to apply the dashboard runtime filter or filters.
A dashboard developer creates zero or more filter options. A filter option consist of a filter set. A filter set is one or more filter option filter pills. At least one filter option is the default option. If a dashboard developer defines more than one filter option, a dashboard consumer can select between the various filter options.
A dashboard developer with Edit access rights to a dashboard can create and edit one or more filter options. Here's how:
- In the Action bar of a dashboard, select More Options (⋮ vertical ellipsis icon).
- In the More Options menu, select Dashboard Filters Manager.
- In Dashboard Filters Manager, in the Filters and Prompts panel, in the Filter Options tray, select + Add.
- In the Properties panel for the filter set, configure the properties.
- Drag & drop a column or formula from the Data panel to the Filters section of the filter set.
- Configure the properties of the filter option filter pill.
About a filter option filter set
A filter set consists of one or more filter option filter pills. A filter option filter pill defines the filter expression. A filter set with two or more filter option filter pills evaluates all the filter pills as one combined filter expressions using an AND built-in boolean operator.
About a filter option filter pill
A filter option filter pill defines the filter expression. A filter pill in this regard can be column or a formula.
Valid references in a filter expression for a filter option filter pill
As a filter expression, a filter option filter pill has at least one filterable column, a filter operator, and at least one filter value. A filterable column for a filter option filter pill can be a data-backed column or a formula column from:
- an alias
- a physical schema table
- an Incorta Analyzer table
- a materialized View
- a business Schema View
- an Incorta View
The join relationships between physical schema tables affect a filter expression. For example, when the filterable column is from a parent table, the filter expression automatically applies to all the dashboard insights related to this table’s child tables. However, if the filterable column is from a child table, the filter expression does not affect the insights related to the parent tables.
A filterable column from an Incorta View will only affect an in insight on a dashboard that queries that specific Incorta View.
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 values to apply as a filter. Also, the In and Not In operators allow for the selection of none, one or more, or all filter values.
A filter value for a filter option filter pill can be a scalar value, array of scalar values, or a reference to a variable as follows:
- a filter expression session variable
- an internal session variable
- an external session variable
- a global variable
- a defined presentation variable for the dashboard
- a date system variable
- a miscellaneous system variable
You can also define a filter option as a filter expression using the Formula Builder.
A global variable is unsupported. It is possible to reference a global variable in a formula expression. It is not possible to make an explicit reference to a global variable as a filter value.
View access rights
In order to reference a column or variable in a filter expression, you must have View access privileges to the grandparent object (schema or business schema) or the variable. A global variable is available to all users.
About an applicable insight for a filter option
In the context of a filter expression, an applicable insight is one that the filter expression applies to. In this regard, there is a relationship between the applicable insight and the filterable column in the filter expression.
The joins between the physical schema tables and the query plan for an insight measure determine whether or not the filterable column affects the insight. For example, a physical schema column in a parent table serves as a filterable column for a measure in a child table. However, a physical schema column from a child table is not a filterable column for a parent table, even when it is the foreign key reference.
You can use the In Query operator in a filter expression to filter the parent table values using a query or queryDistinct built-in function. The query or queryDistinct built-in functions can reference a child table column (or any other table with no relation to the parent table) to generate the filter values.
For a filterable column, an applicable insight meets one of the following conditions:
- The filterable column is a dimension or measure column in the insight.
- The filterable column has a sibling column from the same physical schema table, and that sibling is a dimension or measure for the insight.
- The filterable column is a dimension or key that shares a measure on the insight from a common child table with other dashboard insights.
If an applicable insight already contains an insight filter, an applied filter will complement the insight filter based on a common filterable column as described above.
An insight that queries an Incorta View is an applicable insight for a dashboard filter or dashboard runtime filter only for filterable columns from the Incorta View.
Properties for a filter option
There are two groups of properties for a filter option: the properties for a filter set and the properties of the filter option filter pill. The properties of filter option filter pill differ for a column and formula.
Filter option filter set properties
Here are the properties of a filter set:
| Property | Control | Description |
|---|---|---|
| Column Label | text box | Enter the display name for the filter option. A dashboard consumer will see this value in the Filter Options menu. |
| Default | toggle | Enable this option to set the current filter option as the default. If you create only one filter option, it will serve as the default. |
| Filters | section | Drag and drop a column or formula from the Data panel to the filters section of the filter set. The column or formula will appear as a filter option filter pill. You can then edit the filter option filter pill properties. |
Properties for a filter option filter pill that is a column
The following are the properties of a filter option filter pill that is a column:
| Property | Control | Description |
|---|---|---|
| Column Name | label | Displays the fully-qualified name. Select the information icon to view details and sample data from the column. |
| Column Label | text box | Enter a name for the column. A dashboard consumer will not see this value. |
| Operator | drop down list | Specify the default operator for the filter expression. Depending on the data type of the filter pill, operators will differ. The selected operator also affects the available controls for specifying the default filter values. |
| Values | text box | This option is available when the select operator is In Query. Enter an expression using the Formula Builder. In the Formula Builder, you can specify a reference for a filter expression session variable, internal session variable, external session variable, presentation variable, date system variable, miscellaneous system variable. |
| Values | target box | This option is available when the selected operator is First Version or Last Version. Drag and drop from the Data panel a column to the target box. |
| Values → Search values | text box | Enter a search term to search for specific values. |
| Values → Select All | checkbox | This property is available only when the selected operator allows for multiple values. Select to enable. Deselect to clear the selected list. |
| Values → Edit in Bulk | link | Select to enter multiple values in bulk by entering or copying and pasting an array of delimited values, either Comma or Line Break delimited. Select Done. This property is available only when the selected operator allows for multiple values. You can manually enter a reference for a filter expression session variable, internal session variable, external session variable, presentation variable, date system variable, miscellaneous system variable. |
| Values → Case Sensitive | button toggle | Enable to match both the value text and letter case. This property is available only when you select a filter operator that requires string input, such as Starts With, Ends With, and Contains. |
| Values → Add | text box, checkbox, radial, calendar, calendar with time picker, or level | Depending on the selected filter operator and the related data type of the column, you can select the default filter values for the filter expression. For calendar and calendar with time picker, you can select a date system variable. This property is available only when the selected operator requires one or more values. |
You can specify a variable for the filter value of the filter pill. The variable data type needs to match the data type of the filterable column. You can enter the reference by manually entering the reference using the Values → Edit in Bulk option or by using the In Query operator and Formula Builder
Properties for a filter option filter pill that is a formula
In order for a filter option filter pill to actually function as a dashboard filter, it must be a complete filter expression. As such, the formula expression encapsulates the filterable column or filterable columns within the filter expression.
Here is an example of formula for a filter option that represents a calculated value as a partial filter expression.
SALES.SALES.AMOUNT_SOLD - SALES.SALES.COST_OF_GOODS
In this example, the two columns in the calculation serve as the filterable columns in the filter expression. The data type for the formula result determines the available filter operators. The formula result determines the available filter values.
For a formula that represent a partial expression, a dashboard developer must complete the filter expression in the properties panel by specifying the filter operator and the filter values.
The following are the properties of a filter option filter pill that is a formula:
| Property | Control | Description |
|---|---|---|
| Column Name | label | Displays the column label for the formula. |
| Column Label | text box | Required. Enter the display name for the column. A dashboard user will not see this value. |
| Dynamic | toggle | Enable this option to select an external session variable or a filter expression session variable. |
| Dynamic → Values → Search values | text box | Enter a search term to search for the name of an external session variable or a filter expression session variable. |
| Dynamic → Values | radial | Select an external session variable or a filter expression session variable. Visible only when Dynamic is enabled. To view the variable value, select the Inspector (magnifying glass icon) in the right gutter. |
| Formula | text box | Select to open the Formula Builder to create the filter expression. Visible only when Dynamic is disabled. |
| Operator | drop down list | Specify the default operator for the filter expression. In the case where the formula represents a complete filter expression cases, select either True or False. In the case where the formula represents a partial filter expression, then select the relevant operator. |
| Values | text box | This option is available when the select operator is In Query. Enter an expression using the Formula Builder. In the Formula Builder, you can specify a reference for a filter expression session variable, internal session variable, external session variable, presentation variable, date system variable, miscellaneous system variable. |
| Values | target box | This option is available when the selected operator is First Version or Last Version. Drag and drop from the Data panel a column to the target box. |
| Values → Search values | text box | Enter a search term to search for specific values. |
| Values → Select All | checkbox | This property is available only when the selected operator allows for multiple values. Select to enable. Deselect to clear the selected list. |
| Values → Edit in Bulk | link | Select to enter multiple values in bulk by entering or copying and pasting an array of delimited values, either Comma or Line Break delimited. Select Done. This property is available only when the selected operator allows for multiple values. |
| Values → Case Sensitive | button toggle | Enable to match both the value text and letter case. This property is available only when you select a filter operator that requires string input, such as Starts With, Ends With, and Contains. |
| Values → Add | text box, checkbox, radial, calendar, calendar with time picker, or level | Depending on the selected filter operator and the related data type of the prompt column, you can select the default filter values for the filter expression. You can manually reference a filter expression session variable, internal session variable, external session variable, presentation variable, date system variable, miscellaneous system variable. This property is available only when the selected operator requires one or more values. |
Often, you specify a filter expression session variable when you enable the Dynamic property. However, if using an external session variable, the external session variable must return a string that represents a complete or partial filter expression. If the filter value is a variable, the variable should be of the same data type of the expression. A global variable is not supported as a filter value.