Filtering and Ranking a Crosstabulation Table

You cannot create measure filters or rankings for crosstabulation tables in Edit mode. If there are existing measure filters or rankings (which might be true for existing reports), then the Measure Filter or Rank tab lists the filters or rankings. To keep the filters or rankings, click OK in the dialog box. To delete the filters or rankings, select the Delete Filters or Rankings option, and then click OK.

Create a Category or Hierarchy Filter for a Crosstabulation Table

To create a category or hierarchy filter, complete these steps:
  1. Right-click on the crosstabulation table, and then select Filter and Rank to open the Filter and Rank dialog box.
  2. In the Categories list, select a category or hierarchy. This field displays the categories and hierarchies that are used in the table (including hidden categories and hierarchies). Any currently active filters display with this icon: funnel icon.
    Note: The list does not include categories and hierarchies that are assigned to group breaks or percent of total calculations.
  3. Select a Type. The two filter types for category and hierarchies are No filter (show all values) and Filter. Hierarchies in the Time dimension of a multidimensional data source have an additional Filter (relative time) type.
  4. 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 Crosstabulation Tables
    Data Item
    Filter Type
    Action
    any type
    No filter
    None. No filter will be applied to the selected data item.
    categories from relational data sources
    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
    Type a value and click Add to add it to the Multiple 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.
    hierarchies in the Time dimension of a multidimensional data source (for example, a Year hierarchy)
    Filter (relative time)
    Select a Period type and enter Show and Select criteria. In the Date Range section, specify your starting and ending period information.
    date categories from relational data sources
    Filter
    Use one of these methods:3
    • 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.
    time categories from relational data sources
    Filter
    Select an Operator and enter an Hour, Minute, and Second.
    timestamp categories from relational data sources
    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 Date, Hour, Minute, and Second.
    • Select an Operator, and then select a relative time period. Options include Today, Previous quarter, Current hour, and a user-specified number of periods.
    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 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.
    2For relational data sources, your 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 section query is generated, not the time that the filter is imposed on the table.
  5. Click OK.

Create a Measure Filter for a Crosstabulation Table

To create a measure filter, complete these steps:
  1. Perform one of these tasks to open the Filter and Rank dialog box:
    • Right-click on the table, and then select Filter and Rank.
    • Click a measure heading in a row or column, and then select Filter by this Row or Filter by this Column.
    Note: You cannot filter on percent of total calculations.
  2. Select the Measure Filter or Rank tab. This feature is available only in View mode.
  3. Select the Filter option.
  4. In the Show values of drop-down list, select an option.
  5. Depending on your Show values of selection, specify the criteria for the filter, as shown in the following table:
    Filter Criteria Options for Crosstabulation Tables
    Show Values Selection
    Criteria
    (rows)1
    Select a value for each category or hierarchy level on the columns. Then, select a Measure and an Operator, and type a Value.2
    (columns)3
    Select a value for each category or hierarchy level on the rows. Then, select a Measure and an Operator, and type a Value.2
    Outermost category or hierarchy on the rows
    Select a Measure and an Operator, and type a Value.2
    Note: These values are not available when a section is synchronized.
    Outermost category or hierarchy on the columns
    1If the measures are on the columns, then a (rows) option is available.
    2Do not include a currency symbol in the Value field. In addition, enter values in the number format that is appropriate for the locale that is set for the browser.
    3If the measures are on the rows, then a (columns) option is available.
  6. Click OK.

Create a Ranking for a Crosstabulation Table

To create a ranking for a measure in a crosstabulation table, complete these steps:
  1. Perform one of these tasks to open the Filter and Rank dialog box:
    • Right-click on the table, and then select Filter and Rank.
    • Click a measure heading in a row or column, and then select Rank by this Row or Rank by this Column.
    Note: You cannot filter on percent of total calculations.
  2. If you are in View mode, select the Measure Filter or Rank tab.
  3. Select the Rank option.
  4. In the Show field, select Top or Bottom, and then type a value next to the option that you chose.
  5. (Optional) Choose one of these options:
    • To evaluate the data as a percentage, select the percent(%) option, and then enter a value. The value cannot exceed 100.
      Note: This option is not available for relational data sources.
    • 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.
  6. In the Show values of drop-down list, select an option.
  7. Depending on your Show values of selection, specify the criteria for the ranking, as shown in the following table:
    Ranking Criteria Options for Crosstabulation Tables
    Show Values Selection
    Criteria
    (rows)1
    Select a value for each category or hierarchy level on the columns, and then select a Measure.
    (columns)2
    Select a value for each category or hierarchy level on the rows, and then select a Measure.
    Outermost category or hierarchy on the rows
    Select a Measure.
    Note: These values are not available when a section is synchronized.
    Outermost category or hierarchy on the columns
    1If the measures are on the columns, then a (rows) option is available.
    2If the measures are on the rows, then a (columns) option is available.
  8. Click OK.

Create a Member Property Filter for a Crosstabulation Table

Note: This capability is available only for multidimensional categories with child filters. Not all cubes have member properties, so this capability is not available if either the cube or the selected category does not have member properties. You cannot filter on member properties with a relative time filter.
To create a filter for member properties, complete these steps:
  1. Perform one of these tasks to open the Filter and Rank dialog box:
    • Right-click on the table, and then select Filter and Rank.
    • Right-click a measure heading in a row or column, and then select Filter by this Row or Filter by this Column.
  2. If you are in View mode, select the Category tab.
  3. Select the Filter radio button.
  4. Select the Based on member property check box. The Operator drop-down list and the Value field appear. The following display is an example of the Category Filters tab when the Based on member property check box is selected.
    The Category Filters Tab with the Based on Member Property Check Box Selected
    The Category Filters Tab with the Based on Member Property Check Box Selected
  5. Select the Operator. The choices are either Is equal to or Is not equal to.
  6. Enter a filter value in the Value field.
  7. Click OK.

Remove a Filter or Ranking from a Crosstabulation Table

To remove a filter or ranking from a crosstabulation table, complete these steps:
  1. Right-click on the table, and then select Filter and Rank to open the Filter and Rank dialog box.
  2. Remove category and hierarchy filters, measure filters, or rankings as follows:
    • On the Category Filters tab, for each data item that should not be filtered, select No filter (show all values) as the Type.
    • On the Measure Filter or Rank tab, select No filter (show all values).
    Note: If you already have a measure filter on a crosstabulation table in Edit mode, then you can select the Delete Filters or Rankings check box on the Measure Filter or Rank tab. Then, click OK.
  3. Click OK.
Copyright © SAS Institute Inc. All rights reserved.