1. Designing with Reports for Oracle Enterprise Performance Management Cloud
  2. Defining Members
  3. Assigning Members
  4. Assigning Members Using Functions
  5. Example: Combining the Intersect and Except Functions

Example: Combining the Intersect and Except Functions

The Intersect function combines selections between members. The Exclude function excludes members from a selection.

You can combine these functions to narrow your results. In this example, we will create the following function: Except the Descendants of ‘Total Equity’ (250000) from the Intersection of Descendants of ‘Balance Sheet’ AND Level 1.

Figure 9-1 Except Function


screenshot showing except function parameters, described as follows

  1. Begin by selecting a member, click the function button, and then select View All Functions.

  2. Select the Except function.

  3. In the Parameters panel, click member selector next to Member Set 1. Figure 9-1 shows the Except function with the member select button highlighted.

    This opens a second iteration of function builder that enables you to nest an Intersect function inside the Except function.

  4. Select Intersect, and then enter Descendants of BalanceSheet in Member Set 1, and Level 1 in Member Set 2.

    Figure 9-2 Intersect Function


    screenshot showing intersect function as described in current step

  5. Click Select, and then click OK.

  6. In Member Set 2, enter Descendants of 25000.

Figure 9-3 Result of Except and Intersect Functions


screenshot showing the result of the function as described below

Figure 9-3 shows the results. The members returned include all of the level 1 descendants of Balance Sheet, except for those that are also descendants of Total Equity.