When you create multiple filters, they are, by default, connected with an implicit AND operator; that is, the data displayed in your table, chart, or crosstab is what remains after all your filters are applied.

However, with the custom filter functionality, you can exercise greater control over the displayed data by applying a custom expression that includes more complex, nested AND, OR, and NOT operators, as well as by applying multiple filters to a single field.

Custom filters are useful in a number of situations, including:

When using the AND operator isn’t sufficient. Consider an international company that wants to view data for stores located on the Pacific Rim; they may create a custom expression with the following criteria:

  • Country is USA
  • AND
  • State is California OR Washington OR Oregon OR Hawaii OR Alaska.
  • OR
  • Country is Japan OR Indonesia

Using the AND operator for all of these criteria returns an empty view, as no store is located in all of those areas.

When you need to eliminate some results in a field. For example, if your food and beverage distribution company wants to view sales for all drinks except for high-price items, you might include the following criteria in a custom expression:

  • Product Group is Beverages
  • NOT
  • Price is greater than 39.99

This filter displays all items in the Beverage Product Group, but filters out those with prices over $39.99

These are only two scenarios where custom filters can hone your results and make your view more precise. There are, of course, many other situations where they can be applied.

In this section, we take you through these tasks:

  • Creating a custom expression
  • Editing a custom expression
  • Removing a custom expression
  • Applying multiple filters to a single field

Note: Custom filters are applied to views, but filter details don’t appear on previews, or on the report generated from that view.

To create and apply a custom filter

  1. Create two or more filters for your data, as described in Using Filters. These can be standard field-based filters, or Keep Only and Exclude filters. Note that, as you create the filters for use in a custom expression, you may find that the data in your view disappears, since most (if not all) of the data won’t meet all of the filter criteria. When you create your custom expression and change some of the ANDs to ORs and NOTs, data reappears in the panel.
  2. At the bottom of the Filters panel, expand the Custom Filter Expression section.
  3. In the text entry box, enter a filter expression using the letter designations, and including the following operators:
    • AND narrows your results and includes only fields that meet the criteria of both filters before and after the operator.
    • OR broadens your results and includes fields that meet the criteria of either filter before or after the operator.
    • NOT excludes results that match the criteria.
    • Parentheses combines multiple filters into a single item in the expression.
  4. Click Apply. Your view is updated to reflect the newly-applied filter criteria.

After creating a custom filter, you may want to add another filter to the expression, or remove one already included in the expression.

Note: If the simple filter you want to delete is part of a custom filter, you must first remove it from the custom filter expression; otherwise, deleting the filter deletes the custom filter expression.

To add a new filter to an existing custom expression

  1. If necessary, create the new filter in the Filter panel.
  2. In the Custom Filter Expression section, click inside the text entry box to edit the expression.
  3. Add the new filter to the expression.
  4. Click Apply to apply the new filter criteria.

To remove a filter from a custom expression

  1. Expand the Custom Filter Expression section.
  2. In the text entry box, remove the unwanted filter from the expression, and adjust the expression as needed.
  3. Click Apply to apply the new filter criteria.

When working with custom expressions, you may decide to delete an expression and create a new one.

To remove a custom expression from a view:

  1. Expand the Custom Filter Expression section.
  2. Clear the expression from the text entry box.
  3. Click Apply. The expression is removed, leaving the remaining filters intact.

When you refine your custom expression, you may also want to delete unused filters from the Filters panel.

  • If the filter you want to remove isn’t part of the custom filter, hover your mouse over in the filter’s title bar and select Remove Filter.
  • If you want to remove all existing filters, including the custom expression, hover your mouse over the icon in the upper right corner of the Filters panhandle selectRemove All Filters.

You can apply multiple simple filters to a single field, if needed, to further refine your custom filter results. For example, a user may want to view the data in the Shipping Cost field, but only when it meets certain criteria combinations:

  • When shipping costs to French cities with postal codes that begin with the number 5 are under five Euros

  • When shipping costs to German cities with postal codes that begin with the number 1 are under five Euros

You can recreate the scenario below using the example of how to use the Report Builder.

Example

In the following example a user has a table including the following columns:

  • Country
  • Postal Code
  • Shipping Charge

To analyze the specific shipping costs described above, the user creates the following (simple) filters – including two filters each for the Country and Postal code fields:

A. Country equals FranceB. Postal code starts with 5C. Country equals GermanyD. Postal code starts with 1E. Shipping Charge is less than 5

Then, to display only the information she needs, she creates the following custom expression:

((A and B) or (C and D)) and E

This translates to:

((FRANCE and POSTAL CODES THAT START WITH 5) or (GERMANY and POSTAL CODES THAT START WITH 1)) and SHIPPING CHARGES LESS THAN 5 EUROS.

Using Input Controls

In the Report Builder, you can display the input controls defined in the Topic as visible to users. You can accept the controls’ default values or enter other values. The Report Builder indicates that the view has input controls by displaying Alt as active on the toolbar. Click this icon to select new values or to save values as the new defaults for this view.

There are two types of input controls: Single select and multi-select. The input control type is determined by which operator you are using. In turn, the available operators are determined by what type of field (date, text, numeric, or boolean) you are using as a filter.

Single select controls present a calendar or drop-down list of values, from which you can choose a single value. Operators associated with this type of input control include:

  • equals
  • is not equal to
  • is greater than
  • is less than
  • is greater or equal to
  • is less or equal to
  • contains
  • does not contain
  • starts with
  • does not start with
  • ends with
  • does not end with
  • is before
  • is after
  • is on or before
  • is on or after

Multi-select controls display a calendar or drop-down list of values, from which you can choose multiple values. You can click individual values to select them, or use shift-click to select multiple sequential values.

Operators associated with this type of input control include:

  • is one of
  • is not one of
  • is between
  • is not between

To add an input control to the view using a filter:

  1. Create a new filter, or use an existing one in the Filters panel.
  2. In the Filters panel, click the operator dropdown menu in the filter’s title bar.
  3. Select an operator from the dropdown menu.
  4. Click Apply. The filter appears as an input control when the view is used to run the report.
  5. Place your cursor over the Save icon, select Save Ad Hoc View as.
  6. Name the view, select a location, and click Save.
  7. On the tool bar, click Alt.

Only the input controls defined in the topic appear here. Again, if no input controls were defined in the topic, the button appears inactive. You can create a report and open it in the report viewer to see a filter listed as an input control.

To edit the values for a view’s input controls:

  1. On the toolbar, click Alt. A window listing the input controls defined in the Topic appears. Note: The Parametrized Report Topic already includes three input controls, created when the report was uploaded: Country, RequestDate, and OrderId.
  2. Select new values. For example, select USA from the Country drop-down.
  3. To change default values of input controls, select the check box, Set these values as defaults when saving your view.
  4. The selected values become the default values when you save the view.
  5. Click OK.
  6. The Report view shows USA data.

Did this article help?

Please provide us your feedback below.

* How helpful was this article?

If you have any additional questions and would like to be contacted by a member of our support team, please provide your email.