Filtering and Ranking a List Table

Create a Filter or Ranking for a List Table

To create a filter for a list table, complete these steps:
  1. Right-click on the list table, and then select Filter and Rank to open the Filter and Rank dialog box.
  2. Select the data item that you want to filter from the Categories list. This field displays the data items that are used in the list table. Any currently active filters display with this icon: funnel icon.
  3. Select a Type. The two filter types for alphanumeric categories are No filter (show all values) and Filter. Measure data items have an additional Rank type.
    Note: You cannot create rankings if the list table is part of a synchronized group. The list table also does not include categories that are assigned to group breaks or percent of total calculations.
  4. If you selected a category data item, click Get Values for a list of available values. In the list of Available values, you can use either the Browse or Search tabs to find the values that you want to select. For more information, see Browsing or Searching for Filter Values.
  5. (Optional) If the selected data item enables you to filter on formatted values, then you can select the Filter on formatted values option. Regardless of your selection, the query results show formatted values. (footnote1)
  6. Depending on your filter type selection, take the appropriate action, as shown in the following table:
    Data Item Types, Filter Types, and Available Actions for List Tables
    Data Item
    Filter Type
    Action
    any type
    No filter
    None. No filter will be applied to the selected data item.
    alphanumeric category
    No filter (show all values) or Filter1
    Selecting the filter opens a widget in which you can either select or type in values. If you are not allowed to view the values, then you can type in values.2
    Select one or more items from the Available values list and move them to the Selected values list.
    Type a value1 and click Add to add it to the Selected values box. Repeat this procedure for each value that you want to filter for. You cannot use these characters: < > ( ) \
    To remove a value, select it in the Selected values box and click delete icon.
    date category
    Filter
    If you are entering a date, then the Fixed radio button should be selected. If you are entering a relative period, then the Relative radio button should be selected.
    Use one of these methods:3
    • Select a date using the calendar.
    • Select an Operator and enter a Day, Month, and Year.
    • Select an Operator, and then select a relative time period. Options include Today, Previous quarter, and a user-specified number of periods.
    timestamp category
    Filter on this date
    If you are entering a date, then the Fixed radio button should be selected. If you are entering a relative period, then the Relative radio button should be selected.
    Select an Operator and enter a Date, Hour, Minute, and Second.
    time category
    Filter
    Use one of these methods:3
    • Select an Operator and enter an Hour, Minute, and Second.
    • Select an Operator, and then select a relative time period. The options are Current hour, Previous hour, and a user-specified number of periods.
    measure4
    Filter
    Select an Operator and enter a Value. For the is between values (inclusive) and is not between values (inclusive) operators, type a Minimum and Maximum value. You do not type a value for the Is missing and Is not missing operators.
    Rank
    Select Top or Bottom from the Show field. Then, type a value next to the option that you chose. The percent check box is not available for relational data.
    To exclude tied rankings, select the Exclude ties option. For example, by default, if you request the top five products and there are three products tied for fifth place, then seven products are returned. If you select the Exclude ties option, then only five products are returned.
    Note: Rank is not available if the section is synchronized.
    1If you are filtering on unformatted (actual) values, then you must enter values that match the casing of the values in the data source unless you have selected the Ignore case check box. If you select the Filter on formatted values option, then you must enter the formatted values. If the filter does not return any results, then try using a different casing.
    2The data source administrator controls whether you can select category values.
    3Filtering by relative time means that the filter is relative to the time that the report is run, not the time that the filter is imposed on the table.
    4Enter values in the number format that is appropriate for the locale that is set for the browser.
  7. Click OK.

Remove a Filter or Ranking from a List Table

To remove a filter or ranking from a list table, complete these steps:
  1. Right-click on the list table, and then select Filter and Rank to open the Filter and Rank dialog box.
  2. In the Data Item list, for each data item that should not be filtered, select No filter (show all values) as the Type.
  3. Click OK.
FOOTNOTE 1: Filtering on formatted values can sometimes adversely affect query performance. If you have questions about whether you should select this option, contact your data source administrator.[return]
Copyright © SAS Institute Inc. All rights reserved.