8Filtering and Selecting Data for Analyses
Filtering and Selecting Data for Analyses
Inline or Named Objects
As you work with certain objects, you can create other objects that are saved with those objects. When you save one object with another, that object is saved "inline." You can save filters, groups, and calculated items inline. For example, you can create a group as part of an analysis. When you save the analysis, the group is saved "inline" or along with the analysis.
In addition to saving these objects inline, you can save them as individual objects with the subject area in the Oracle BI Presentation Catalog. When you save an object on its own, its becomes a "named" object. Named objects provide reusability, because you can create one object and use it multiple times with any analysis, dashboard (for filters), or dashboard page (for filters) that contains the columns that are specified in the named object. When the named object is updated and saved, those updates are immediately applied to all objects where the named object is used.
For example, after you save a group inline with an analysis, you can save the group as its own object in the catalog. You can then apply that named group from the Catalog pane to other analyses.
Folder Structure for Named Objects
Named filters, groups, and calculated items are generally saved to their related subject area folder. By saving the objects to a subject area folder, you ensure that they are available when you create an analysis for the same subject area.
Named objects saved in the /My Folders folder are available only to you. Objects saved in the /Shared Folders folder are available to other users who have permission to access the folders. If a subject area folder does not exist in your /My Folders folder or within the /Shared Folders folder, then the subject area folder is created automatically. When you save the object, the Save As dialog displays a default save path to /My Folders/Subject Area Contents/<subject area>. However, the dialog's Folders area continues to display all instances of the subject area folder in the catalog.
Saving Groups and Calculated Items as Inline or Named
You can save groups and calculated item as an inline or named object:
When you create a group or calculated item while editing and saving a view or while working in the Compound Layout, the group or calculated item is saved inline with the analysis.
-
When you work in the Selection Steps pane:
You can save a group or calculated item that is within a step as a named object in the catalog.
You can save a set of steps or the resulting members list for a column as a named object. You cannot save a set of steps as a group if one of the steps includes a calculated item.
See "Adding a Group to Another Analysis" for information on adding a saved group to an analysis.
Saving a Calculated Item or Group as a Named Object to the Catalog
BI Author Limited
BI Author Developer
BI Tenant Administrator
Edit the analysis.
In the Results tab, display the Selection Steps pane.
Click the link for the calculated item or group, then click Save Calculated Item As or Save Group As to display the Save As dialog.
Complete the dialog to save the object to the catalog.
Saving a Set of Steps as a Group to the Catalog
BI Author Limited
BI Author Developer
BI Tenant Administrator
Display the Selection Steps pane.
Click the Save Selection Steps button to the far right of the column name.
Complete the Save Selection Steps dialog to save the group as an object to the catalog.
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 runtime. For information, see Prompting in Dashboards and Analyses.
Oracle BI 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. For more information about how to add views to an analyses, see Adding Views for Display in Dashboards.
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.
Working with Selections of Data
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 runtime.
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.
Creating Selection Steps
BI Author Limited
BI Author Developer
BI Tenant Administrator
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.
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 runtime 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.
Editing Selection Steps
BI Author Limited
BI Author Developer
BI Tenant Administrator
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.
Selection Steps as a Group Object
If you have created a set of selection steps, then you can save and reuse the set as a group object, as described in "Saving Groups and Calculated Items as Inline or Named".
Selection Steps and Double Columns
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.
Creating a Column Filter
BI Author Limited
BI Author Developer
BI Tenant Administrator
Editing a Column Filter
BI Author Limited
BI Author Developer
BI Tenant Administrator
To open a named filter, use the following sub-procedure:
In the global header, click Catalog.
Navigate to the filter to edit and click the Edit link.
To open an inline filter, use the following sub-procedure:
In an analysis, click the Criteria tab.
In the Filters pane locate the filter that you want to edit and click its Edit Filter button.
Make the desired changes.
Save the filter.
Combining and Grouping Column Filters
BI Author Limited
BI Author Developer
BI Tenant Administrator
Creating a Filter Based on the Results of a Saved Analysis
BI Author Limited
BI Author Developer
BI Tenant Administrator
Creating and Editing the SQL Statements for a Column Filter in an Analysis
You can create and edit the logical SQL WHERE clause to be used as a filter. While generally not necessary, this feature is available for users who want advanced filtering capability. For descriptions of SQL clauses, see Appendix D, "Logical SQL Reference."
BI Author Limited
BI Author Developer
BI Tenant Administrator
Groups and Calculated Items
You can create a group or calculated item as a way to display data in a table, pivot table, trellis, or graph. Groups and calculated items allow you to add new "members" to a column, when those members do not exist in the data source. These members are also known as "custom members."
-
A group is a static list of members that you select or a static or dynamic list that is generated by selection steps. A group is represented as a member. You can drill in a group that was created for a hierarchical column but not in one that was created for an attribute column.
A group uses the existing aggregation function of the measure column with which it is displayed. The aggregation is performed up from the lowest detail level in the Oracle BI Server, but no values are double-counted.
A calculated item is a computation between members, which is represented as a single member that cannot be drilled. When you create a calculated item, you add a new member in which you have selected how to aggregate the item, such as Sum or Average or a custom formula. The calculation is performed at the aggregated level, not at the lowest detail level.
Both groups and calculated items become selection steps for the analysis. Therefore, they apply to all views for that analysis. Both groups and calculated items can be saved as inline or named objects. For information, see "Inline or Named Objects".
What are Groups?
A group (also known as a "saved selection" in other products) is a user-defined member of a column. A group can be a list of members or a set of selection steps that can be executed to generate a list of members. All the members must be from the same attribute column or hierarchical column, and in a hierarchical column, members can be from different hierarchy levels. Groups are always displayed at the bottom of the column list in the order in which they were added (as shown in the Selection Steps pane). Groups can contain members or other groups.
You can save a group to the catalog and reuse it. For example, you can apply a group to analysis and dashboard column prompts or variable prompts. If a group is applied to a prompt, then the prompt presents either the group as a choice list option or members of the group as choice list options to the user at runtime.
What are Calculated Items?
A calculated item is a calculation between members, which is represented as a single member. A calculated item enables you to override the default aggregation rule that is specified in the Oracle BI repository, and for an existing analysis, the aggregation rule chosen by the designer. You can define a calculated item using a custom formula (which is the default) or by combining selected members with a function (for example, SUM).
A calculated item is a user-defined member of a column. A calculated item can contain members or other calculated items. Calculated items are always displayed at the bottom of the column list in the order in which they were added (as shown in the Selection Steps pane). You can save a calculated item to the catalog and reuse it.
A calculated item is useful when you want to view and manipulate a set of members as a single entity. For example, you might define calculated items for the following:
Key accounts in a geographic region
High-end products
Non-standard time periods, such as the first three weeks in September
You can create calculated items for columns in various places. Calculations differ from the default aggregation rules that are applied to measures, as described in Adding Totals to Tables and Pivot Tables. Internally, calculated items are processed as SQL SELECT statements, and the indicated functions are performed on the result set. For more information about SQL functions, see Appendix D, "Logical SQL Reference."
Creating a Calculated Item or Group
BI Author Limited
BI Author Developer
BI Tenant Administrator
Editing Groups and Calculated Items
You can edit groups and calculated items in various ways:
In the Selection Steps pane, click the link for the calculated item or group, then click Edit to display the appropriate dialog.
If you have saved the object to the catalog, then select the object in the Catalog pane and click Edit to display the appropriate dialog.
In a table, pivot table, or trellis, right-click the group or calculated item and select Edit Group or Edit Calculated Item.
The value of a group or calculated item might be affected by filters and selection steps, as described in the following list:
Filters — A group or calculated item is evaluated using only those members that have not been removed using filters. For example, if you have a calculated item for SUM(EAST + WEST) but WEST is removed through a filter, then only the EAST sum is included for the calculated item. If all members have been removed, then a null value is returned, which shows as an empty cell in a table, pivot table, or trellis.
-
Selection steps — When you create selection steps, you can add a group or a calculated item in a step. Subsequent Keep Only or Remove steps might reference members that were included in the group or calculated item.
A group list is affected by members that are kept or removed in subsequent steps, but the group outline value remains the same. For example, suppose the MyNewYork group contains Albany and Buffalo and its value is 100. Suppose Albany is removed in a later step. The value of the MyNewYork group remains at 100, but Albany is no longer listed with the group.
A calculated item is not affected by members that are kept or removed in subsequent steps.
Nesting Groups and Calculation Items
As you work with groups and calculated items, you might want to "nest" them; that is, create a group within a group, for example. The following list provides various scenarios for nesting groups and calculated items:
Groups can be nested; that is, they can include other groups. Nested groups are "flattened." For example, suppose that the my_favorite_countries group includes the my_favorite_cities group. When you display and expand the my_favorite_ countries group in a table, you do not see the my_favorite_cities group. Instead, you see the member values of the my_favorite_cities group.
Calculated items can be nested; that is, they can include other calculated items.
Groups cannot contain calculated items nor can calculated items contain groups.
-
When you work with selection steps:
You can save selection steps that include groups or calculated items as either a series of steps whose results are generated at runtime or as results that are static and are redisplayed each time.
You can apply a group that is a saved selection of steps, using the Catalog pane. If you do so when the saved selection contains a step for a calculated item, then you can only add the members of the group. You cannot add the group itself.
Adding a Group to Other Analyses
You can add a group to the same column on which it was created in another analysis. The group can be either a list of members or a set of selection steps. For information on these groups, see "Selection Steps as a Group Object"
The following procedure describes how to add the group using the Results tab, but you can do so anywhere that the Catalog pane is displayed. You can also add a group using the following means:
In the Edit Member Step dialog, select Start with Group or Calculated Item in the Action box, then select the group from the Catalog pane in the Available list.
In the Selection Steps pane, click Then, New Step for the appropriate column, select Add Groups or Calculated Items, select Select Existing Groups and Calculated Items, and select the group in the resulting dialog.
Adding a Group to Another Analysis Using the Catalog Pane
BI Author Limited
BI Author Developer
BI Tenant Administrator
The group or its members are included as an "Add" step on the Selection Steps pane. You can reorder the steps as appropriate.
Examples of Calculated Items
The examples and explanations in this section assume that you have a basic understanding of SQL statements and their syntax. The syntax shown in these examples applies to the Custom Formula function in the New Calculated Item dialog. The examples are hypothetical. Not all possible calculated items are shown.
Example 1 - Say you have a report with Org Level 1 and # Requisitions and you generate the results
Click on New Calculated Item
In Display Label, enter a name
Select the Sum function
Select Values From Org Level 1
Select a few Organizations
Click Ok and the Calculated Item will appear in the table
Example 2 - Say you have a report with Org Level 1 and # Requisitions and you generate the results
Click on New Calculated Item
In Display Label, enter a name
Select the Custom Formula function
In Selected Values, enter the formula : sum($1,$2,$3)
Click Ok and the Calculated Item will appear in the table (it will sum the first 3 rows)