Sorting and filtering in Designer

  1. To create a new filter, follow one of these steps:
    1. Select the Filters icon from the toolbar, select the +Add Filter icon. Select the Filter or the Advanced Filter option. Select the column to filter on. The filter is added to the filter section. Select the filter to edit and apply to the report.
      Note: One-click filter creation with Expression columns is still in development. We recommend that you create the filters directly from the filter panel.
    2. From the Report Content menu, select the Entity Actions icon and then Create Filter.
    3. From the report display area, click on the entity, select the Entity Actions icon, and select Create Filter.
  2. Select the Filter Type:
    • Data: The filter operation is performed at the database level and only results that meet the criteria are returned. This is the default value.
    • Display: The filter operation is applied after the database returns the result set for the report query. Only the selected values are displayed in the report.
    • Set-based: The result set of another report is used to provide filter values. This is useful for exception reporting.
      Caution: 
      When this filter is based on another report, the column in the subreport must not contain any BQL functions or positional calculations. BQL expressions in set-based filter subqueries are not supported. The set-based filter is an IN or NOT IN subquery that can use the basic operators, such as equal to (=), less than (<).
  3. Select the filter operator from the list
  4. Select the filter style. The styles that are available are based on the data type. This list shows the filter styles:
    • Check list: For multi-select text-based, or numeric data types.
    • Slider: or date, datetime, and integer data types.
    • Value: For text-based data.
    • Date picker: For date data types.
    • Variable: For multi-select, text-based, numeric data, or date types.
  5. Select the options to filter by. This is displayed differently depending on the selected filter style.
  6. Select the More Options icon and specify this information:
    Apply to Measures
    Select whether to apply the filter to measures in the report.
    Prompted
    When this option is enabled, a filter is created that can be used by a dashboard prompt. A prompt is automatically created for you when the report is added to a dashlet.
  7. Select Apply.
  8. Advanced Filters are used to create complex expressions. Parenthetical precedence is supported, operators such as IN and NOT IN, and nesting using AND/OR syntax between filters.
    1. Advanced Filters are created using the Expression Builder.
    2. Specify the name of your filter. This name is displayed at the top of the report to indicate the filters that are applied.
    3. Create the BQL expression.
    4. By default, Advanced Filters are data filters. The filter operation is performed at the database level and only results that meet the criteria are displayed. To change this to a display filter, enable Use as a display filter.
    5. Select Done.

Applied filters on the report are displayed at the top of the report. Hover over the filter to view more information.

To remove filters from a report, click the X icon or disable it from the Filters menu.