Filtering and Ranking a Crosstabulation Table :: SAS(R) Web Report Studio 4.2: User's Guide

Create a Category or Hierarchy Filter for a Crosstabulation Table

To create a category or hierarchy filter, complete these steps:

Right-click on the crosstabulation table, and then select Filter and Rank to open the Filter and Rank dialog box.
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) along with an indication of any currently active filters. Data item names might wrap multiple lines.

Note: The list does not include categories and hierarchies that are assigned to group breaks or percent of total calculations.
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.

Depending on your filter type selection, take the appropriate action, as shown in Data Item Types, Filter Types, and Available Actions for Crosstabulation Tables.

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 .
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 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.

Click OK.

Create a Measure Filter for a Crosstabulation Table

To create a measure filter, complete these steps:

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.
Select the Measure Filter or Rank tab. This feature is available only in View mode if your data is not synchronized.

Note: The Measure Filter or Rank tab is not available if the table is part of a synchronized group. For more information, see Using Synchronized or Independent Objects.
Select the Filter option.
In the Show values of drop-down list, select an option.

Depending on your Show values of selection, specify the criteria for the filter, as shown in Filter Criteria Options for Crosstabulation Tables.

Filter Criteria Options for Crosstabulation Tables

Show Value 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
Outermost category or hierarchy on the columns	Select a Measure and an Operator, and type a Value.2
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.

Click OK.

Create a Ranking for a Crosstabulation Table

To create a ranking for a measure in a crosstabulation table, complete these steps:

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.
If you are in View mode, select the Measure Filter or Rank tab.

Note: The Measure Filter or Rank tab is not available if the table is part of a synchronized group. For more information, see Using Synchronized or Independent Objects.
Select the Rank option.
In the Show field, select Top or Bottom, and then type a value next to the option that you chose.
(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.
In the Show values of drop-down list, select an option.

Depending on your Show values of selection, specify the criteria for the ranking, as shown in Ranking Criteria Options for Crosstabulation Tables.

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.
Outermost category or hierarchy on the columns	Select a Measure.
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.

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:

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.
If you are in View mode, select the Measure Filter or Rank tab.

Note: The Measure Filter or Rank tab is not available if the table is part of a synchronized group. For more information, see Using Synchronized or Independent Objects.
Select the Filter radio button.
Select the Based on member property check box. The Operator drop-down list and the Value field appear. The Category Filters Tab with the Based on Member Property Check Box Selected 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
Select the Operator. The choices are either Is equal to or Is not equal to.
Enter a filter value in the Value field.
Click OK.

Remove a Filter or Ranking from a Crosstabulation Table

To remove a filter or ranking from a crosstabulation table, complete these steps:

Right-click on the table, and then select Filter and Rank to open the Filter and Rank dialog box.
Remove category and hierarchy filters, measure filters, or rankings as described next:
- On the Category Filters tab, for each data item that should not be filtered, select No filter (show all values) as the Filter 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.
Click OK.