What are Filters and Selection Steps?
You use both filters and selection steps to limit the results that are displayed when an analysis is run, so that the results answer a particular question. Together with the columns that you select for an analysis, filters and selection steps determine what the results contain. Based on the filters and selection steps, only those results that match the criteria are shown. For example, depending on the industry in which you work, you can use filters and selection steps to learn who are the top ten performers, what are the dollar sales for a particular brand, which are the most profitable customers, and so on.
Another kind of filter, called a prompt, can apply to all items in a dashboard. Prompts can be used to complete selection steps and filters at run time.
Oracle BI Enterprise Edition provides the Filters view and Selection Steps view, which you can add to an analysis to display any filters or selection steps applied to the analysis. Adding these views can help the user understand the information displayed in the analysis.
How Do Filters and Selection Steps Differ?
Filters and selection steps are applied on a column-level basis and provide two methods for limiting the data in an analysis. A filter is always applied to a column before any selection steps are applied. Steps are applied in their specified order. Filters and selection steps differ in various ways.
Filters
Filters can be applied directly to attribute columns and measure columns. Filters are applied before the query is aggregated and affect the query and thus the resulting values for measures. For example, suppose that you have a list of members in which the aggregate sums to 100. Over time, more members meet the filter criteria and are filtered in, which increases the aggregate sum to 200.
Selection Steps
Selection steps are applied after the query is aggregated and affect only the members displayed, not the resulting aggregate values. For example, suppose that you have a list of hierarchical members in which the aggregate sums to 100. If you remove one of the members using a selection step, then the aggregate sum remains at 100.
You can create selection steps for both attribute columns and hierarchical columns. Selection steps are per column and cannot cross columns. Because attribute columns do not have an aggregate member, the use of selection steps versus filters for attribute columns is not as distinctive as for hierarchical columns. While measure columns are displayed in the Selection Steps pane, you cannot create steps for them so steps do not affect them. Measures are used to create condition steps for attribute and hierarchical columns, such as Sales greater than $1 million.
Applying Filters to Attribute Columns to Affect Hierarchical Columns
You can use a filter on a related attribute column to affect the display of members in a hierarchical column. For example, suppose a hierarchical column contains the levels Year, Quarter, and Month. Suppose that a filter exists on the attribute column that corresponds to the Year hierarchy level. If you create a filter on Year to limit it to 2008 and 2009, then when the hierarchical column is displayed in a view, only those two years are visible. This functionality depends on the way that the logical columns have been defined in the business layer of the subject area in the Oracle BI Administration Tool.
As you specify which data members to include in an analysis, you create selections of data from the data source. Each selection specifies the criteria for a set of members for a particular column, such as Product or Geography. Each selection consists of one or more steps. A step is an instruction that affects the selection, such as add Product members whose values contain the text "ABC." The order in which steps are performed affects the selection of data. Each step acts incrementally on the results from previous steps, rather than acting on all the members for that column.
You can view these selection steps in the "Selection Steps pane". Steps are created using the following means:
· When you add a column to an analysis, a selection step is created automatically to start with all members, unless you explicitly add specific members. As you drag and drop column members in the Results tab to add to the analysis, steps are also created automatically. For example, suppose that you drag and drop the FY2007 and FY2008 members from the Year hierarchical column to a pivot table. The selection step "Add FY2007, FY2008" is created.
· As you add groups and calculated items, steps are created automatically.
· When you use right-click interactions (such as Add Related or Keep Only) to refine the selection of data for a particular hierarchical column or attribute column, steps are created automatically.
· You can create steps directly using the Selection Steps pane, to refine the selection of data for a particular hierarchical column or attribute column. You can display the Selection Steps pane from various places including the Results tab, the Criteria tab, and some view editors.
Selection steps can be one of the following types:
· Explicit list of members — A step can include a list of members for a column, such as Boston, New York, Kansas, South. For hierarchical columns, the members can be from different hierarchy levels. For attribute columns, the members can be from only that column.
· Condition step — A step can specify that members are selected from a column based on a condition, which can be one of various types including based on measures or on top/bottom values. This member list is dynamic and determined at run time.
· Based on hierarchy step — A step for hierarchical columns that enables you to select the type of relationship with which to select members. You can select a family relationship (such as children of or parent of), a specific hierarchy level (for level-based hierarchies only), or a level relationship (for level-based hierarchies only).
· Groups and calculated items — A step can include a group or calculated item. Groups and calculated items can be used only with Add steps; they cannot be used in Keep Only or Remove steps.
You create steps in the "Selection Steps pane", which you can display in various places. The following procedure describes how to create steps in the Results tab.
To create selection steps:
1. Display the "Analysis editor: Results tab".
2. If the Selection Steps pane is not visible, then click the Show Selection Steps pane button in the toolbar to display it.
The pane might also be collapsed at the bottom of the Results tab. Click the plus sign icon to expand it.
3. For the column whose steps you want to define, click the Then, New Step link.
4. From the menu, select the option for the step type to create and complete the resulting dialog.
Selection steps are automatically created when you use the right-click interactions (such as Add Related or Keep Only) to refine the selection of data for a particular hierarchical column or attribute column in a view. See "Right-Click Menu for Tables and Pivot Tables".
After you add selection steps to the analysis, you can go to the "Analysis editor: Results tab" and add the Selection Step view to the analysis. If you add the Selection Steps view, at run time the user can view the selection steps that are applied to the analysis. For more information about adding the selection steps view, see "Results tab: Selection Steps editor".
You can edit existing selection steps, as described in the following procedure.
To edit selection steps:
1. Hover the mouse pointer over the step in the Selection Steps pane and click a button on the resulting toolbar.
You can perform various tasks such as displaying a dialog for editing the step, deleting the step, or changing the order of the step in the list of steps.
For a group or calculated item, click its name to display a menu with options for editing and saving.
If you have created a set of selection steps, then you can save and reuse the set as a group object.
If your repository is configured for double columns, then you can create a selection step on a double column. To do so, select the display values for that column and the step is automatically evaluated using the code values that correspond to those display values.
If you use double columns, then use care with the "New Calculated Item dialog". You can include a positional operator in the custom formula for the calculated item, such as $1, which specifies the column from the first row in the data set. When you include a positional operator, the display values cannot be mapped to the code values when evaluating the formula.
Use the following procedure to create or edit a named or inline filter.
To create or edit a column filter:
Note:
If your repository is configured for double columns, and you want to use an operator other than is equal to / is in, is not equal to / is not in, or is between and specify code values rather than display values, then you should explicitly choose the code column rather than the display column.
For information on double columns, see "Understanding the Double Column Feature".
1. To create a named filter, use the following sub-procedure:
a. From the Oracle Business Intelligence Home page, locate the global header, hover over the New menu, and from the menu select Filter. The Select Subject Area dialog is displayed.
b. From the Select Subject Area dialog, choose the subject area for which you want to create a filter. The "Filter editor" is displayed.
c. From the "Subject Areas pane", double-click the column for which you want to create the filter. The "New Filter dialog" is displayed.
2. To create an inline filter, use the following sub-procedure:
a. Either create an analysis or access an existing analysis for which you want to create a filter. Click the Criteria tab.
b. Locate the "Filters pane" and from the Filters Pane's toolbar, click the Create a filter for the current subject area button. The analysis' selected columns are displayed in the cascading menu.
c. Select a column name from the menu. Or select the More Columns option to access the"Select Column dialog" from which you can select any column from the subject area.
After you selected a column, the "New Filter dialog" is displayed.
Note:
If you want to add a filter for a column located in a different subject area, then you must first add that subject area to the analysis by clicking the Add/Remove Subject Area button in the "Subject Areas pane".
3. In the Operator field, choose an operator for the filter. The operator list from which you can choose is populated based on the type of column that you selected. For more information about each operator, including the is prompted and is based on the results of another analysis operator options, see "Operators".
4. In the Value field, specify one or more values to use when applying the filter or condition. You can:
o Type values, using a semicolon to separate the values.
o Select values from the list or calendar.
To search for specific values, click Search in the list box. The "Select Values dialog" is displayed, where you can search for and select values.
5. If your repository is configured for double columns, and you are creating the filter on a display column, then by default, you specify display values. However, if your organization allows the display of code values, then you can specify code values rather than display values, but only if you use one of the following operators:
o is equal to / is in
o is not equal to / is not in
o is between
To specify code values, select the Select by Code Column box and then specify the values.
6. If your repository is configured for double columns, and you are creating the filter on a display column and want to filter on display values rather than code values, then deselect the Filter by Code Column box.
7. Click the Add More Options button to add a SQL expression, Session variable, Repository variable, or Presentation variable to the filter. Note that if you are setting the filter's value with a SQL expression or variable, you should leave the Value field blank. For more information on variables, see "Using Variables".
8. Select the Protect Filter box to protect the filter's value from being overridden by a matching prompt's value or when the user navigates to another report within the analysis. When you navigate from one report to another report within an analysis, any prompt values that you specified in the first report can be transferred to the second report.
9. To convert the filter to SQL statements, select the Convert this filter to SQL box. The "Advanced SQL Filter dialog" is displayed.
Note:
This is a one-way conversion. After you select the Convert this filter to SQL box, you can no longer view and edit the filter in the Edit Filter dialog. After you convert the filter to SQL statements, you can only view and edit the filter item as SQL statements in the Advanced SQL Filter dialog.
For more information about this option, see "Creating and Editing the SQL Statements for a Column Filter in an Analysis".
10. When you are finished specifying the filter's criteria, click OK.
11. Save the filter in one of the following ways:
o From the Analysis Editor, you can select Save Analysis to save the filter as an inline filter.
o From the Filter Editor, you can select Save Filter to save the filter as a named filter.
Note: only a member of this blog may post a comment.