Working with Groups and Calculated Items

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

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 Menu for Data 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.

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.

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.

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.

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.

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.

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

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