Using Filters to Customize the Display of Data


Filters enable you to customize the display of data in your report, chart, document, or visualization. This gives you the advantage of viewing only the data that you want to see and use.

When creating a report, you refer to fields in several parts of the request. For example, in display commands (PRINT, SUM), in sort phrases (BY, ACROSS), and in selection criteria (WHERE, WHERE TOTAL, IF).

Note: When using ESSBASE hierarchical data sources, creating a filter on a sort field is not supported.

The WHERE phrase selects records from the data source to be included in a report. The data is evaluated according to the selection criteria before it is retrieved from the data source. You can use as many WHERE phrases as necessary to define your selection criteria.

In WHERE TOTAL tests, however, data is selected after all the data has been retrieved and processed.

You can group conditions and expressions within filter criteria. In addition, you can apply functions and calculations within criteria. For more information on filtering options, see Field Tab.

You can create Where and Where Total filters in the Filter dialog box by clicking WHERE.

Double-clicking the Double-click or press F2 to edit! text opens drop-down menus for Fields and Subqueries, Operators, and Values.

You can retrieve fields and values from the Master File and data source and Subqueries from a HOLD file.

The Field drop-down menu provides a field list from the Master File. The Field list can be viewed as follows:

The Operator (default) drop-down menu provides various operators for your filter. For example, Equal to.

The Value drop-down menu opens a dialog box with multiple options.

Note: If you are creating a filter on a full date field, the Value field will have a calendar icon adjacent to it. You can use this icon to select a date using a calendar control.

The Type drop-down menu contains the following options:

The value area generally contains a text input box that you can use to manually insert values. However, if you are working with a date field, the Value field is set to Today. You can optionally select Beginning of Month, End of Month, Beginning of Quarter, End of Quarter, Beginning of Year, End of Year, or you can specify a Custom date using the calendar that displays when you make that selection.

Note: The default date of Today applies to Report, Chart, and Document modes only.

The value area also contains a Get Values drop-down menu, which supplies the following options:

Note: The Get Values drop-down menu is only accessible if you have already selected a field.

After selecting the values that you want, you can move them into and out of the Multiple Values area with the left and right arrows. You can also change the value order and delete values with the up and down arrows and the Delete icon.

After creating a condition, you can insert additional conditions before and after the selected condition by using the Insert Before and Insert After buttons at the top of the Filter dialog box. You can use either the And or the Or conjunction to link conditions and the Group and Ungroup buttons to nest and organize conditions.

You can create additional filters by clicking the New Filter button at the top of the Filter dialog box.

After creating the filters that you want, click OK to save and apply the filters. You can access them from the Filter pane of the Resources panel.

Note: Dates shown in the live preview reflect the format specified in the data source. However, when Filters are applied to a date formatted field, the format of dates shown in the Filter interface is derived from the locale. When the report, chart or visualization is executed, the output as well as any prompt will reflect the format specified in the data source.

Including or Excluding a Filter

Once you have created a filter, you can make decisions about when and where you want to include it in your report. For example, you might want to include one filter and exclude another. Using the Include and Exclude options in the Filter group on the Home or Field tabs, you can set filters accordingly.

You can use the following information to make decisions about your filters:

  • Exclude. Removes, but does not delete, the filter from the report.
  • Include. Restores a filter that was previously excluded from a report.

Sorting the Values in an Autoprompt Parameter

Using the prompt functionality, you can create an auto prompting parameter that displays sorted values at run time. This feature is located in the Filter Group on the Field tab.

To create an auto prompting parameter:

  1. Select a field for which you want to create a parameter in your report.
  2. On the Field tab, in the Filter group, click Prompt.

    The Create a filtering condition dialog box displays.

  3. Double-click the Value field.

    Note: Parameter is selected, by default, when you are defining a prompt.

    The following options display, from which you can make a selection:

    • Simple. This is used for prompts using Text Input. This is the default value.
    • Static. This is used for prompts using Selection. This option allows you to select multiple values at run time.
    • Dynamic. This is used for prompts using Data Values. This option allows you to select multiple values at run time.
    • Optional. This is used for prompts using Single or Multiselect parameters.
    • Sort Prompt Values. This is used to determine how the values are sorted. Ascending is the default.
  4. Click OK, and click OK again to close the Create a filtering dialog box.

    When you run your report, you will be prompted for information based on the parameters you created.