Using Filters to Customize the Display of Data

In this section: Including or Excluding a Filter Using the Prompt Functionality to Select Field Information at Run Time

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, as shown in the second image to follow.

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

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, as shown in the following image.

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:

• Constant. Enables the entry of a literal value. This value can be a positive number, a negative number, or any character string.
• Parameter. Enables you to specify a parameter by entering a name and description in the provided text input areas, as well as selecting the type of parameter (Simple, Static, or Dynamic).

  Note: If you create a filter that specifies a simple parameter, QUOTEDSTRING will be inserted at the end of the line that defines the simple parameter, as specified in the procedure (.fex), by default. In this case, when you run a .fex that contains this value, you will not be able to enter multiple values, at run time, for simple parameters.

  You can also select Optional to set the parameter as optional. This adds the default value of _FOC_NULL, which removes the parameter from the procedure. Indicating Optional also disables the prompt for parameters functionality within the procedure. Therefore, if a report has an optional parameter, it would run without any prompting. However, if another report drilled down to it, the parameter would be accepted and it would run with that value. The Optional option is used primarily for other reports that would be drilling down to the current report and passing the parameter to this report. For more information, see Using Multi Drill and Using the Auto Linking Feature to Link Content.

  When defining WHERE clauses in the Create a filtering condition dialog box, you can also create simple, static, and dynamic parameters. These parameter values are transformed into local variables that allow the content of your report to be defined at runtime by a context in your data or a selection by the user. Additionally, you can change to scope of this parameter amper variable from local to a global scope so that its value is available across multiple components on a page or in a document. To transform the parameter to have global scope, enter a single & (amper) symbol in the first character position of the parameter name field.

  Note: The & symbol can only be entered in the first position of the parameter name where it defines the parameter scope. Parameter field names cannot contain a & symbol in any other character position.

  The parameter description is used as the defining parameter prompt. In the description, a space can be placed in any position. The first character cannot be a single quotation mark (') and the following characters cannot be used within the parameter prompt: & . ; ( )

  The parameter options are shown in the following image.

  
• Field. Enables the specification of a field name to compare against.

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:

• All. Retrieves all the values from the selected field.
• First. Retrieves the first value from the selected field.
• Last. Retrieves the last value from the selected field.
• Minimum. Retrieves the minimum value from the selected field.
• Maximum. Retrieves the maximum value from the selected field.
• From File. Retrieves a value that you specify. Selecting this option opens the Select From File dialog box, where you can browse to a local file and specify a file format, such as a flat file (CSV) or an Excel spreadsheet (XLS or XLSX).

  Numeric data values can be imported into the available filter values from a flat file or a CSV as pure numbers. This import supports numbers containing leading signs (negative), numeric digits, and decimal points. Numbers containing currency symbols or commas will be imported but as shown in the Values box in the Create a filtering condition dialog box, may not be interpreted correctly. Text or CSV files with values requiring complex formatting can be opened and saved with the appropriate formatting in Excel. This Excel file can then be imported with the formatting retained.

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

Once you have retrieved the values for your filter using one of the Get Values options, you can use the Search values option to quickly locate a record in the list. For example, you can type in the letter S to go to that section in the list. Also known as type ahead search, this functionality can bring you directly to the value that you want to include, making it easy to identify the required values. Wildcards, such as an asterisk, are also supported. The Search values option is shown in the following image.

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 also add an expression to your filter by clicking the New Expression button. This creates a new WHERE clause and allows you to build an expression dynamically.

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.