Like what you see? Have a play with our trial version.

Admins, if you'd like to hide this setting, see our Advanced Configurations page for more details.

Overview

The Filter Formatting button contains a number of options to customize your filters. This gives you full control over restrictive each filter is, whether they're dependent on other filters, and how they should be displayed for user interaction.

When you click on the Filter Formatting button, the general settings will be displayed. These can be updated at any time.

General Filter Formatting Settings

The general formatting options for report filters are split three sections — Format, Actions and Filter Refresh. Note that Filter Refresh is only displayed when at least one filter in the list on the left is set to be a cached filter. 

Option

Description

Filter Display

Select where visible filters should appear on the Report page — Top, Left, or Left Side Nav.

Apply Link Location

Select where the Apply button or Apply link should appear. This is positioned within the Filter Display (described above), and clicking on it triggers the results of the filter values selected by the user.

Apply Style

Choose whether the Apply trigger is shown as a button or as a link.

Filter Width

Select the length for User Prompt filter values display, allowing you to accommodate longer values.

Display Filter Values

Select whether you'd like users to see a legend of filter values applied to the report.

Option

Description

Autorun Report on Load

Choose whether or not to run the report on first load. You might use this option for daily reports so that they display the most up-to-date data upon loading.

Remember Filter Values

Choose whether or not to remember previously applied filter values for this report for when it is next loaded. For example, a daily report might include a dynamic date of 'yesterday' in its filters. However, if the daily report contains a filter with a date to be manually selected, you might opt out of using this feature for that report.

Drill Filters

Choose whether the filter panel and filter breadcrumbs of the child report will appear after a drill through takes place. This only needs to be set on the parent report. There are three options:

  • Open: The filter panel is maximized, displaying the filter list, and the applied filter breadcrumbs appear at the top of the child report.
  • Closed: The filter panel is minimized, with the option to open the panel to display the filter list. Applied filter breadcrumbs still appear at the top of the child report.
  • Hidden: The filter panel is minimized, with the option to open the panel, but the filter list remains hidden. Applied filter breadcrumbs are not displayed.

Regardless of which option you choose, a breadcrumb list of applied filters is always displayed at the top of a report, which cannot be manipulated.

Minimize Filter Section

Choose whether to show or minimize the display of filters upon the first load of the report.

Filter Date Reference

Choose how the report should apply filter date periods — either relative to the current date, or relative to the date when the view was last updated.


Option

Description

Refresh

Choose whether users can manually refresh cached filter values, or set them to automatically refresh on a schedule.

Refresh NowWhen the Refresh toggle is set to Manual, the Refresh Now link will be displayed so you can manually refresh cached filter values at any time from this dialog box.
ScheduleWhen the Refresh toggle is set to Scheduled, additional scheduling options will appear for full control over how often scheduling should occur and when it should start. 

The schedule will apply to all cached filters on this report.

When refreshing cached filters in a multi-tenancy set up, be aware of the following:

  • If this is initiated from the default organization or through a scheduled task,  the filters in all the organizations will be refreshed. 
  • If this is initiated from a specific client organization,  only the filters of that organization will be refreshed.

Filter Settings by Type

The Filter Formatting panel for each type of filter is split into two sections — Display and Entry style. The Display section provides the bounding box settings, such as a label description, and whether the filter is mandatory. The Entry style section includes options to change the appearance of filters and what values they display. This latter section varies the most between filter types, so we've included screen shots of that section for each filter type, below.

Date filter settings

The screen below shows the Entry style options for date filters. See the tables below for descriptions of both the Display section and the Entry style section.



Option

Description

Description

Define the title of the filter.

Filter Requirement

There are two available options:

  • Allow Omit allows the user to leave this filter blank.
  • Mandatory forces the user to provide a value for this filter in order to run the report.

Filter Hierarchy

Create a hierarchy of filters (eg, filter by year, then by quarter, then by month). To use this option, the parent filter must be set to a selection field (this can be done in the Entry Style section of this dialog box, where Value Entry Method should be set to Value List Selection.

Click on the Specify Parent Filter link to display the Set Parent dialog box, where you can select the parent filter. You can also set two display options:

  • Dependent Values displays only the values related to one parent (eg, only display Jan, Feb, Mar when the parent filter for quarter is set to Q1). 
  • Dependent Display hides the current filter from the user until the parent filter value has been selected (eg, display the month filter only when a quarter has been selected).

Remember User Selection

Remember values applied to this filter for future use.

Option

Description

Value Entry Method

Define the way the user should provide values for the filter. There are two available options:

  • Manual User Entry allows the user to either type in dates into the entry fields, or use the calendar picker.
  • Value List Selection allows you to cache values or provide a list of pre-defined date ranges for the user to select from.

Allow Prompt

This option is only available for the Manual User Entry option and allows users to prompt the database for a list of values to select from at the time of viewing the report.

Display All Filter Values

This option is displayed if the Allow Prompt option above is enabled. 

By default (when this option is not active), your user prompt filters will only display the values available based on any selections already made. Say, For example, you had two user prompt filers — one for Date Range and one for Camp Country. If you select a date range of one month in one year,  the filter value list display for Camp Country would be limited to only those countries that hosted a camp during that month of that year. Enabling this option would ignore the date range and display all values for Camp Country. 

  • Toggled off (default): Display a restricted list of value prompts for this filter that automatically updates according to any other filter value selections already made.
  • Toggled on: Display the full, unrestricted list of value prompts for this filter, regardless of other filter value selections already made.

Value List Setup

Define which method of providing a list of values you will use. There are three options:

  • Pre-defined Periods: Use a list of pre-defined date ranges for the user to select from. See Date Filter Periods for more information.
  • Cached Values: Create a list of values by caching the current contents of the field in the database (and remember to check the Filter Refresh section of the General Settings to set the cache refreshing options).
  • Cached values On Demand: Create a list of values by loading the contents of the field each time the report is loaded.
  • Custom Query: Create a list of values by caching the current contents of the field in the database using a custom SQL query (and remember to check the Filter Refresh section of the General Settings to set the cache refreshing options).
  • Custom Query Cached On Demand: Create a list of values by caching the current contents of the field using the custom SQL query each time the report is loaded.
List Display Type

Choose if cached values should be displayed in a dropdown or a popup.

Pre-filter by Aggregated Metrics

This setting filters the cached values with the result of any filtered metric fields in the report, provided the metric field filter is not user prompted.

For example, you can use this setting to further filter the unique values of this field to be cached, by adding an age (metric) field with a defined filter of greater than 50. The values cached for this dimension field will only include unique content where the age field is > 50.

Sort

Define the sort order for the list of values as ascending or descending. For Custom Query, there is an additional “Sort” option, which is “No Sort” if the user prefers not to have any specific sorting applied to the query results.

Use Min/Max ValuesChoose whether to hide or display the minimum value and the maximum value for this filter.

Default Value

(Optional). Define the default value(s) to be used for the filter when the report is initially loaded.

If the Value List Setup option is set to Custom Query or Custom Query Cached on Demand, the following options will become available. Use this to write a custom SQL query.


Dimension filter settings

When using a dimension as a filter, dimension-specific options are available in the Display and Entry style sections. These options let you define various settings, such as filter caching, custom descriptions and list length (if using an In List operator). Some of these options therefore differ from the ones available when using a metric or a date. See the tables below for descriptions of both the Display section and the Entry style section.



Option

Description

Description

Define the title of the filter.

Filter Requirement

There are two available options:

  • Allow Omit allows the user to leave this filter blank.
  • Mandatory forces the user to provide a value for this filter in order to run the report.

Filter Hierarchy

Create a hierarchy of filters (eg, filter by region, then by country). To use this option, the parent filter must be set to a selection field (this can be done in the Entry Style section of this dialog box, where Value Entry Method should be set to Value List Selection.

Click on the Specify Parent Filter link to display the Set Parent dialog box, where you can select the parent filter. You can also set two display options:

  • Dependent Values displays only the values related to one parent (eg, only display European countries when the parent filter for region is set to Europe). 
  • Dependent Display hides the current filter from the user until the parent filter value has been selected (eg, display the country filter only when a region has been selected).

Remember User Selection

Remember values applied to this filter for future use.

List Size

Set the number of values displayed in the list before scrolling is required. If a list contains fewer values than this number, the list size will shrink to fit accordingly.

Option

Description

Value Entry Method

Define the way the user should provide values for the filter. There are two available options:

  • Manual User Entry allows the user to type in values the entry fields.
  • Value List Selection provides a list of pre-defined dimension values for the user to select from.

Allow Prompt

This option is only available for the Manual User Entry option and allows users to prompt the database for a list of values to select from at the time of viewing the report.
Note: we recommend that you disable the user prompt option if your data set has too many variables, as it may take some time for the values to be returned.

Display All Filter Values

This option is displayed if the Allow Prompt option above is enabled. 

By default (when this option is not active), your user prompt filters will only display the values available based on any selections already made. Say, For example, you had two user prompt filers — one for Date Range and one for Camp Country. If you select a date range of one month in one year,  the filter value list display for Camp Country would be limited to only those countries that hosted a camp during that month of that year. Enabling this option would ignore the date range and display all values for Camp Country. 

  • Toggled off (default): Display a restricted list of value prompts for this filter that automatically updates according to any other filter value selections already made.
  • Toggled on: Display the full, unrestricted list of value prompts for this filter, regardless of other filter value selections already made.

CSV Entry

This option is only displayed when Manual User Entry is set as the entry method. It allows users to manually provide a list of comma-separated values for the filter.

Value List Setup

Define which method of providing a list of values you will use. There are three options:

  • Reference Codes: Create a list of values for the user to select from based on the reference code applied to the filter. See Reference Code Use for more information.
  • Cached Values: Create a list of values by caching the current contents of the field in the database (and remember to check the Filter Refresh section of the General Settings to set the cache refreshing options).
  • Cached Values On Demand: Create a list of values by loading the contents of the field each time the report is loaded.
  • Custom Query: Create a list of values by caching the current contents of the field in the database using a custom SQL query (and remember to check the Filter Refresh section of the General Settings to set the cache refreshing options).
  • Custom Query Cached On Demand: Create a list of values by caching the current contents of the field using the custom SQL query each time the report is loaded.
List Display Type

Choose if cached values should be displayed in a list, checkboxes, or a popup.

Pre-filter by Aggregated Metrics

This setting filters the cached values with the result of any filtered metric fields in the report, provided the metric field filter is not user prompted.

For example, you can use this setting to further filter the unique values of this field to be cached, by adding an age (metric) field with a defined filter of greater than 50. The values cached for this dimension field will only include unique content where the age field is > 50.

Ignore Report Filters when Loading Cached Value

This setting was introduced in Yellowfin 9.5 and is off by default. This setting only appears when cached values are used and for reports based on drag and drop views, and not reports based on view level filters.

When this setting is enabled, other report filters are not applied to the value list. This means that more cached filter value options will be displayed because there is no dependency on any other filters. For example, if a filter for 'fruit' and 'veg' is set to filter out fruit, 'apple' would still be displayed as a filter value even though the resulting data would have those results filtered out.

When this setting is disabled, the cached filter value list is reduced according to the other filter values already selected. For example, if a filter for 'fruit' and 'veg' is set to filter out fruit, 'apple' would not be displayed in the cached filter value list.

For a detailed explanation and example, see our knowledge base article

Admins, if you'd like to hide this setting, see our Advanced Configurations page for more details.

Restrict SQL Context to Filter Columns OnlyThis setting was introduced in Yellowfin 9.5 and is off by default. This setting only appears when cached values are used and for reports based on drag and drop views, and not  reports based on view level filters.

When this setting is enabled, the SQL context is restricted to filter columns only, allowing cached filter values to come from a single table, rather than joining to the rest of the view tables, which can lead to more cached filter values being displayed. This is because there is no join logic to restrict the results.

When this setting is disabled, cached filter values rely on join logic to be loaded, and are therefore restricted according to the standard table references for the report.

For a detailed explanation and example, see our knowledge base article.

Admins, if you'd like to hide this setting, see our Advanced Configurations page for more details.

Sort

Define the sort order for the list of values as ascending or descending. For Custom Query, there is an additional “Sort” option, which is “No Sort” if the user prefers not to have any specific sorting applied to the query results.

Use Min/Max ValuesChoose whether to hide or display the minimum value and the maximum value for this filter.

Default Value

Define the default value(s) to be used for the filter when the report is initially loaded. (Optional).

Dynamic Filter Value

Enable this to automatically select the first filter value, when the default value is not available to the user.
A user may not have the default value available to them (for example, as a result of access filter restrictions), in which case the system will select the first value to be used as the filter.
Note: This only applies to dimension values where the filter values are selected from a list of options (i.e. ‘Value List Selection’ is the chosen entry method), such as checkboxes, or dropdowns. 

If a default filter has multiple values, and at least one is available to the user, then dynamic filtering will not be applied.

If the Value List Setup option is set to Custom Query or the following options will become available.

The following tokens can be used within SQL to filter queries under certain circumstances:

When using a Cached Query, the SQL can include the following tokens:

%CLIENTREFID% - This will insert the client reference Id into the SQL. This should only be used when client source substitution is used, where data for multiple clients may exist in a single database.

When using a Cached Query on Demand and a source filter is enabled, the SQL can include these tokens:

%CLIENTREFID% - This will insert the client reference Id into the SQL. 

%REFERENCE_TYPE% - This will insert the Access Filter values for the user who is running a report. For example, an Access Filter reference code named REGION, will have a token named %REGION%. Access Filter values will be inserted as text tokens and will be quoted in the generated SQL. Multiple tokens will be inserted in comma-separated form, so that they can be included in an SQL IN statement.

%REFERENCE_TYPE_NUMERIC% - This will insert the Access Filter values for the user who is running a report. For example, an Access Filter reference code named USERID, will have a token named %USERID_NUMERIC%. Access Filter values will be inserted as numeric tokens. Multiple tokens will be inserted in comma-separated form, so that they can be included in an SQL IN statement.


Metric filter settings

Metric filter options are more limited by their nature, but there are still some configuration options. See the tables below for descriptions of both the Display section and the Entry style section.


Option

Description

Description

Define the title of the filter.

Filter Requirement

There are two available options:

  • Allow Omit allows the user to leave this filter blank.
  • Mandatory forces the user to provide a value for this filter in order to run the report.

Filter Hierarchy

Create a hierarchy of filters (eg, filter by country, then by invoiced amount). To use this option, the parent filter must be set to a selection field (this can be done in the Entry Style section of this dialog box, where Value Entry Method should be set to Value List Selection.

Click on the Specify Parent Filter link to display the Set Parent dialog box, where you can select the parent filter. You can also set two display options:

  • Dependent Values displays only the values related to one parent (in our example of filtering by country then invoiced amount, this field would not be required). 
  • Dependent Display hides the current filter from the user until the parent filter value has been selected (eg, display the invoiced amount filter only when a country has been selected).

Remember User Selection

Remember values applied to this filter for future use.

Option

Description

Value Entry Method

Define the way the user should provide values for the filter. There are three options:

  • Manual User Entry allows the user to type in the metric values.
  • Slider allows the user to select values using a numeric slider.
  • Range allows the user to provide a low and a high value.

Value List Setup

Define which method of providing a list of values you will use. There are two options:

  • Manual: The user will manually type values for this filter.
  • Custom Query: Create a list of values by caching the current contents of the field in the database using a custom SQL query.

Min

If Value Entry Method is set to Slider or Range, use this field to set the minimum value the user can select for this filter.

Interval

If Value Entry Method is set to Range, use this field to set the increments to display between the Min and Max fields

Max

If Value Entry Method is set to Slider or Range, use this field to define the maximum value the user can select for this value.

Default Value

(Optional.) Define the default value(s) to be used for the filter when the report is initially loaded. 

If the Value List Setup option is set to Custom Query the following options will become available.