Skip Headers
Oracle® Fusion Middleware User's Guide for Oracle Business Intelligence Enterprise Edition
11g Release 1 (11.1.1)

Part Number E10544-06
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

5 Filtering and Selecting Data for Analyses

This chapter explains how to construct filters, selection steps, groups, and calculated items in Oracle Business Intelligence Enterprise Edition. It explains how to use these objects to specify the data that is displayed in analyses and dashboards.

This chapter includes the following sections:

Saving Objects as Inline or Named

This section describes saving objects as inline or named. It contains the following topics:

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

What is the 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 Filters as Inline or Named

When you create an inline filter in the Analysis editor: Criteria Tab's "Filters pane," you can optionally save the inline filter as a named filter. Named filters can also be created at the analysis level or as a standalone object from the global header.

A named filter can filter all or some of the analyses that are embedded in a dashboard or analyses that are embedded on the same dashboard page.

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.

To save a calculated item or group as a named object to the catalog:

  1. Display the "Selection Steps pane."

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

  3. Complete the dialog to save the object to the catalog.

To save a set of steps as a group to the catalog:

  1. Display the "Selection Steps pane."

  2. Click the Save Selection Steps button to the far right of the column name.

  3. Complete the "Save Selection Steps dialog" to save the group as an object to the catalog.

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 runtime. For information, see Chapter 6, "Prompting in Dashboards and Analyses."

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. For more information about how to add views to an analyses, see Chapter 3, "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.

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.

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:

Selection steps can be one of the following types:

Note:

When working with selection steps, if you search for members in hierarchical columns that are non-text (for example, date or time), then the search will yield no results.

Creating Selection 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 on 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 Interactions in Views."

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

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.

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

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

For information on double columns, see "Understanding the Double Column Feature."

Creating Column Filters

Use the following procedure to create a named or inline filter. For information about opening a filter for editing, see "Editing Column Filters."

To create 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:

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

    2. From the Select Subject Area dialog, choose the subject area for which you want to create a filter. The "Filter editor" is displayed.

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

    1. Either create an analysis or access an existing analysis for which you want to create a filter. Click the Criteria tab.

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

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

    • Type values, using a semicolon to separate the values.

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

    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:

    • is equal to / is in

    • is not equal to / is not in

    • is between

    To specify code values, select the Select by Code Column box and then specify the values.

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

  6. Click the Add More Options button to add a SQL expression, Session variable, Repository variable, Presentation variable, or Global variable to the filter. Note that if you are setting the filter's value with a SQL expression or variable, leave the Value field blank. For more information on variables, see "Using Variables."

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

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

  9. When you are finished specifying the filter's criteria, click OK.

  10. Save the filter in one of the following ways:

    • From the Analysis Editor, you can click Save Analysis to save the filter as an inline filter.

    • From the Filter Editor, you can click Save Filter to save the filter as a named filter.

    For more information, see "Saving Objects as Inline or Named."

Editing Column Filters

Use the following procedure to open and edit a saved filter. If you edit and save a named filter, the changes that you make to the filter are propagated to wherever the filter is used.

To edit a filter:

  1. To open a named filter, use the following sub-procedure:

    1. In the global header, click Catalog. The "Catalog page" is displayed.

    2. Navigate to the filter to edit and click the Edit link. The "Filter editor" is displayed.

  2. To open an inline filter, use the following sub-procedure:

    1. In an analysis, click the Criteria tab.

    2. In the "Filters pane" locate the filter that you want to edit and click its Edit Filter button. The "Filter editor" is displayed.

  3. Make the desired changes.

  4. Save the filter.

Working with the EVALUATE_PREDICATE Function

This section provides the following topics on working with the EVALUATE_PREDICATE function:

For more information about this function, see "EVALUATE_PREDICATE."

How Can I Use the EVALUATE_PREDICATE Function with a Filter?

You can add an EVALUATE_PREDICATE function as an inline filter clause. You cannot use this function with hierarchical columns. Use this function when you cannot create the appropriate filter clause with the Oracle BI EE filter operators. This function is intended for database functions with a return type of Boolean, and can be used only for SQL functions. Support for EVALUATE_PREDICATE does not extend across all multidimensional data sources. Also, you cannot use EVALUATE_PREDICATE with XML data sources.

Example

The following is an example of how you can use the EVALUATE_PREDICATE function. This example requests that Products.P4 Brand values be greater than 6 characters. After it is run, this example returns any rows where the length of the data in the P4 Brand column is greater than 6 characters (for example, "BizTech" and "HomeView").

SELECT
0 s_0,
"A - Sample Sales"."Products"."P3 LOB s_1, 
"A - Sample Sales"."Products"."P4 Brand" s_2,
"A - Sample Sales"."Base Facts"."1- Revenue" s_3
FROM "A - Sample Sales"
Where EVALUATE_PREDICATE('length(%1)>6',"A - Sample Sales"."Products"."P4 Brand").
ORDER BY 1,2,3

Adding the EVALUATE_PREDICATE Function to an Inline Filter

Use the following procedure to add the EVALUATE_PREDICATE function to an inline filter. Note that you cannot use this function with hierarchical columns. For more information see "How Can I Use the EVALUATE_PREDICATE Function with a Filter?" and "EVALUATE_PREDICATE."

To add the EVALUATE_PREDICATE function to an inline filter:

  1. Go to the Analysis Editor's "Filters pane" and click the More Options toolbar button, and select the Add EVALUATE_PREDICATE function option.

    The "New EVALUATE_PREDICATE Function dialog" is displayed.

  2. Enter the function's formula.

    For an example of entering correct syntax, see "How Can I Use the EVALUATE_PREDICATE Function with a Filter?"

  3. Click OK.

    The EVALUATE_PREDICATE function is displayed in the Filters pane.

Applying a Named Filter to an Analysis

Use the following procedure to apply a named filter to an analysis.

To apply a named filter to an analysis:

  1. Create or open the analysis to which you want to add a named filter.

  2. Within the Analysis Editor: Criteria tab, locate the "Catalog pane" and navigate to the appropriate folder that holds the named filter. Filters are usually saved within the Subject Area subfolder. For example, Shared Folders/2. Functional Examples/Filters/A - Sample Sales/Demo.

  3. Select the named filter and click the Add More Options button. The "Apply Saved Filter dialog" is displayed.

  4. Specify how you want to add the named filter to the analysis. You can select one or both of the following options:

    • Select the Clear all existing filters before applying box to remove all existing filters from the analysis before adding the named filter.

    • Select the Apply contents of filter instead of a reference to the filter box to copy the actual contents of the filter to the analysis. Copying the contents enables you to manipulate the filter criteria without altering the saved filter. When you deselect the Apply contents of filter instead of a reference to the filter box, a reference to the filter is added to the analysis. From the analysis, you can view but not alter the named filter's contents.

  5. Click OK.

    The filter is displayed in the Filters Pane.

Combining and Grouping Column Filters

Combining and grouping column filters with AND and OR Boolean operators enables you to create complex filters without requiring you to know SQL statements. Grouping enables you to establish the precedence in which Oracle BI EE filters an analysis.

To combine a column filter with other column filters:

  1. Create or open a named filter or analysis that contains an inline filter.

  2. If you are working with a named filter, then locate the Saved Filter pane and confirm that the filter contains two or more filter items. If you are working with an inline filter, then locate the Filters Pane and confirm that the filter contains two or more filter items.

    After you add the second filter item, the AND operator is displayed before the second filter item.

    Note:

    If you want to group filters, then the filter must contain three or more filter items.

  3. To change an AND operator to an OR operator, click the word AND. You can toggle between AND and OR this way. Note the following information:

    • The AND operator means that the criteria specified in each filter must be met. This is the default method for combining column filters.

    • The OR operator means that the criteria that is specified in at least one of the column filters must be met.

  4. As you add filter items, click AND and OR operators as necessary to construct the appropriate filter combinations.

  5. Save the filter in one of the following ways:

    • From the Analysis Editor, you can select Save Analysis to save the filter as an inline filter.

    • From the Filter Editor, you can select Save Filter to save the filter as a named filter.

    For more information, see "Saving Objects as Inline or Named."

Using a Saved Analysis as a Filter

You can create a filter based on the values that are returned by another analysis. Any saved analysis that returns a column of values can be used to filter the matching column in an analysis.

To create a filter based on the results of another saved analysis:

  1. Create or open a named filter or analysis that contains an inline filter.

  2. If you are working with a named filter, then locate the Saved Filter pane and from the "Subject Areas pane," select the column for which you want to create a filter.

    If you are working with an inline filter, then locate the Filters Pane, and from the Filters Pane toolbar, click the Create a filter for the current Subject Area button and select the column for which you want to create the filter.

    The "New Filter dialog" is displayed.

  3. In the Operator field, select is based on the results of another analysis.

    The Saved Analysis, Relationship, and Use values in Column fields are displayed.

  4. In the Saved Analysis field, either enter the complete path to the saved analysis or click the Browse button to locate the analysis upon which to base the filter.

    The column names from the saved analysis are displayed in the Use Values in Column drop-down list.

  5. Select a column name from the Use Values in Column field, and in the Relationship field, select the appropriate relationship between the results and the column to be filtered.

  6. Click OK.

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

Note:

After you convert a filter to SQL statements, you can view and edit the filter item as SQL statements in the Advanced SQL Filter dialog, only. You can no longer view and edit the filter in the Edit Filter dialog.

To create and edit the SQL syntax of a column filter:

  1. Create or open a named filter or an analysis that contains an inline filter.

  2. If you are working with a named filter, then locate the Saved Filter pane and from the "Subject Areas pane," select the column for which you want to create a filter.

    If you are working with an inline filter, then locate the Filters Pane, and from the Filters Pane toolbar, click the Create a filter for the current Subject Area button and select the column for which you want to create the filter.

    The "New Filter dialog" is displayed.

  3. Specify the filter's criteria and select Convert this filter to SQL.

  4. Click OK.

    The "Advanced SQL Filter dialog" is displayed.

  5. Enter your modifications in the SQL field, and click OK. Not that after you convert the filter to a SQL statement, you can no longer view and edit the filter in the Edit Filter dialog.

Working with Calculated Measures

Calculated measures are derived from other measures and created by using formulas.

To create a calculated measure:

  1. Perform one of the following actions:

    • To create a calculated measure for data views that contain columns within the Compound Layout, click the New Calculated Measure button on the "Analysis editor: Results tab."

    • To create a calculated measure for a specific data view within the Compound Layout, edit the specific data view, and then click the New Calculated Measure button on the "Results tab: Data View editor."

      Note:

      You cannot add calculated measures to direct database queries or combined requests.

  2. Complete the "New Calculated Measure dialog." See "Editing the Formula for a Column" for information about column formulas.

  3. Click the OK button.

To edit a calculated measure:

You can edit a calculated measure from the "Analysis editor: Criteria tab."

  1. In the Criteria tab, click the Options button to the right of the column name for the calculated measure.

  2. Modify the columns as needed.

    • Specify the sort order for columns. (You cannot specify the sort order for hierarchy levels.) For more information, see "Sorting Data in Views."

    • Edit formulas for attribute columns and measure columns, including customizing headings, and specifying the aggregation rule. (You cannot customize headings, specify the aggregation rule, or edit the formulas for hierarchical columns or for hierarchy levels.) For more information, see "Editing the Formula for a Column."

    • Edit column properties to control the formatting and interaction of columns and hierarchy levels. For more information on applying formatting, see "Applying Formatting to a Column."

Working with 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."

Both groups and calculated items become selection steps for the entire analysis when you select the All Views option while creating the group or calculated item. Therefore, they apply to all views for that analysis. These groups and calculated items can be saved as inline or named objects. For information, see "Saving Objects as Inline or Named."

If you create a group or calculated item for a specific view by selecting the Current View option, the group or calculated item only applies to the specific active view.

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 create a custom group and add the custom group to a column within a view.

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. See "Overriding a Selection Step With a Column Prompt" for information on overriding selection steps with prompts when the user selects groups or column members.

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

How Will Calculated Items Created in Previous Releases Be Upgraded?

In previous releases (prior to 11g) of Oracle BI EE, when you created a calculated item in a pivot table, that calculated item applied only to that pivot table for that analysis. If you upgrade from a previous release (prior to 11g), then all calculated items are automatically converted to selection steps, which apply to all views for an analysis.

For more information, see "What are Filters and Selection Steps?" For upgrade information, see "Upgrading Calculated Items" in Oracle Fusion Middleware Upgrade Guide for Oracle Business Intelligence.

Creating Groups and Calculated Items

Use the following procedure to create a calculated item or group.

To create a calculated item or group:

  1. Perform one of the following actions:

    • Click the button to create a calculated item or group.

      The button is available in multiple locations. The following list provides a few of these locations:

      • On the toolbar of the view editor, click the New Calculated Item button or the New Group button. The new calculated item or group will be created for all views.

      • In the Layout pane, in the <view-type> Rows or Columns area, click the More Options button to the right of a column name, then select New Calculated Item. You can choose to add this calculated item or group to the current view or to all views.

    • Click the Then, New Step link in the "Selection Steps pane."

    • In a table, pivot table, or trellis (on an outside edge), select the column or column header, member, group or calculated item to include, then right-click and select Create Group or Create Calculated Item. See "Right-Click Interactions in Views."

  2. Complete the appropriate dialog, either "New Calculated Item dialog" or "New Group dialog."

  3. Consider the following when adding a calculated item or group:

    • From within the Compound Layout:

      • If you are using the right-click menu, then select either the Current View or All Views option button to add the group or calculated item to the specific active view or to all views in the Compound Layout.

      • If you are using the toolbar buttons or Selection pane, then the group or calculated item will be added to All Views in the Compound Layout.

    • From within a view editor, then select either the Current View or All Views option button to add the group or calculated item to the specific active view or to all views in the Compound Layout.

    • From within the Pivot Table or Table editors by using the More Options button, then select either the Current View or All Views option button to add the group or calculated item to the specific active view or to all views in the Compound Layout.

    Note:

    If you add a calculated item or group to a view within a dashboard by using the right-click menu, the calculated item or group will only be added to the Current View.

    If you add a group or calculated item by using the All Views option button, any new view that you add will also have the group or calculated item added to it.

    If you add a group or calculated item by using the Current View option button, any new view that you add will not have the group or calculated item added to it.

  4. When the calculated item or group is complete, click OK.

    If any errors are detected, then a message is displayed. Correct the error and click OK again.

Note:

You might encounter an error when using groups when Oracle OLAP is the data source for the analysis. If the group is included in a view and an error message is displayed, then the problem is likely that a measure column in that analysis uses an external aggregation.

Check with the administrator about implementing one of the following possible solutions:

  • Explicitly specify the aggregation rule for the measure in the repository.

  • Create the appropriate group of values as a dimension member in the Oracle OLAP analytic workspace.

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 (on an outside edge), 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.

If you remove a group or calculated item from all views that was created by using the Current View option button, the group or calculated item remains and will not be added to any new view that you subsequently add.

If you remove a group or calculated item from all views that was created by using the All Views option button, the group or calculated item remains and will be added to any new view that you subsequently add.

Consider the following if you are removing a group or calculated item by using the right-click menu:

  • From a view within a dashboard page — The calculated item or group will be removed only from the current view.

  • From a view within a view editor — The calculated item or group will be removed only from all views.

Adding a Group to Another Analysis

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

To add a group to another analysis using the Catalog pane:

  1. On the Results tab, display an analysis that contains the same column to which you want to apply the selections from a group.

  2. Locate the Catalog pane and navigate to the appropriate folder that holds the saved group.

  3. Select the group and click the Add More Options button on the toolbar of the Catalog pane.

  4. Select one of the following:

    • Add to add the group itself, which generates an outline value for the group. In the table, pivot table, or trellis, you see the group name, which can be expanded to see its member values. (Default)

    • Add Members to add only the groups members to the analysis. You do not see the outline value.

    The group or its members are included as an "Add" step on the Selection Steps pane. You can reorder the steps as appropriate.

Nesting Groups and Calculated 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.

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 5-1 shows the code required to obtain the value of the current measure, such as dollar sales, for each of the products SoftDrinkA, SoftDrinkB, and SoftDrinkC, and adds the values.

This is equivalent to selecting Sum from the Function list, and then typing or clicking 'SoftDrinkA','SoftDrinkB','SoftDrinkC' to add them to the Function field.

Example 5-1 Obtaining the Value of the Current Measure

sum('SoftDrinkA','SoftDrinkB','SoftDrinkC')

Example 5-2 obtains the minimum current measure, such as dollars in sales, for SoftDrinkA or SoftDrinkB, whichever is lower.

In Example 5-1 and Example 5-2, each functional calculated item is performed for each member in the outer layer, such as the Product layer. For example, if Year and Product are positioned on an axis, and one of the preceding calculated items is built on the Product layer, then the results are computed per year.

Example 5-2 Obtaining the Minimum Current Measure

min('SoftDrinkA','SoftDrinkB')

Example 5-3 obtains the values for each item in the outer layer, such as Year and Product, and adds them.

Instead of specifying a named item for an attribute column, such as SoftDrinkA, you can specify $n or $-n, where n is an integer that indicates the item's row position. If you specify $n, then the measure is taken from the nth row. If you specify $-n, then the measure is taken from the nth to the last row.

For example, for dollar sales, $1 obtains the measure from the first row in the data set, and $-1 obtains the measure from the last row in the data set.

Example 5-3 Obtaining the Values of Each Item in the Outer Layer

sum(*) 

Example 5-4 obtains the current measure, such as dollar sales, of the item from the first, second, and third rows, and sums them.

Example 5-4 Obtaining the Values of Measures and Summing Them

sum($1,$2,$3)

Example 5-5 adds sales of SoftDrinkA, SoftDrinkB, and SoftDrinkC. Note that the two calculated items shown in the example are equivalent. That is, Sum is the default function; therefore, it can be omitted.

Example 5-5 Adding Sales Values

'SoftDrinkA' + 'SoftDrinkB' + 'SoftDrinkC'
sum('SoftDrinkA','SoftDrinkB','SoftDrinkC')

Example 5-6 adds sales of SoftDrinkA with sales of diet SoftDrinkA, then adds sales of SoftDrinkB with sales of diet SoftDrinkB, and returns the maximum of these two amounts.

Example 5-6 Adding Sales Values and Returning the Maximum

max('SoftDrinkA' + 'diet SoftDrinkA', 'SoftDrinkB' + 'diet SoftDrinkB')