Selecting Members with Advanced Expressions

<Do not delete this text because it is a placeholder for the generated list of "main" topics when run in a browser>

Purpose

In this tutorial, you learn how to select members dynamically by using expressions. You define criteria by creating expressions of members and Boolean operations.

Time to Complete

Approximately 30 minutes.

Overview

You can make member selections in Oracle® Hyperion Financial Reporting, Fusion Edition based on user-defined expressions. You create the expressions in the Selected area of the Select Members dialog box. You can use Boolean operators in your expressions, and you can select members, lists, and functions as the expression operands.

Boolean operators enable you to specify member combinations in your report. You use the AND, OR, UNION, and NOT Boolean operators, combined with member expressions, to refine your member selections.

Boolean Operator Description
AND All conditions must be met
OR One condition of several must be met
UNION Intersections of similar data
NOT Inverse of the selected condition

In the Selected Options menu, when you select Advanced, additional columns are displayed in the Selected area to select the NOT option, to insert parentheses, and to select the operator (AND, OR, or UNION). You can drag the Member Selection box to make it larger so you can more easily see the contents of these columns.

The following table lists examples of member selections:

Member Selection Description
Descendants of Market AND Level 0 All descendants of Market that are level 0 members
Descendants of Product AND Members matching ???-10 All descendants of Product with a member name that begins with 3 characters and ends with -10 (100-10, 200-10, 300-10, 400-10)
(Children of East OR Children of West) AND Members where
UDA=Major Market
All members that are children of East or West and are also major markets
Descendants of Market AND Members matching N* All descendants of Market with a member name that begins with N (New York, New Hampshire, New Mexico, Nevada)

Scenario

In the Oracle® Essbase database, Sample:Basic, there are markets with small, medium, and large populations.

The following portion of the Sample:Basic outline displays each market and its population:

The following portion of the Sample:Basic outline displays the size of the small, medium, and large populations:

In Oracle® Hyperion Financial Reporting Studio, Fusion Edition (Reporting Studio), you create a report that retrieves markets based on their population. You use advanced expressions to select and exclude members with Boolean operators.

Prerequisites

Before starting this tutorial, you should:

Selecting Members with the AND Boolean Operator

In Reporting Studio, you add a grid in a report to retrieve Market members. You add an advanced expression that specifies two conditions - Market members must be level 0 members and they must have a small population.

To populate a grid with the AND Boolean operator in an expression, perform the following steps:

.

In Reporting Studio, create a report and insert a grid.

In this example, you select the Essbase database connection, Sample:Basic, add the Market dimension to the rows, and the Year dimension to the columns.

 

.

For the Year dimension in column A, select Qtr1 and Qtr2.

 

.

For the Market dimension in row 1, select the Lev0,Market list to display all the level 0 members of the Market dimension.

 

.

In the Selected area of the Select Members dialog box, click the View button, and select Preview.

The Preview Market Items window displays a list of the 20 level 0 members of the Market dimension. These members have small, medium, or large populations.

 

.

Click OK to return to the Select Members dialog box.

In the Selected area, click the View button, and select Advanced.

The Selected area displays additional columns for Boolean operators and parentheses. You can drag the Select Members dialog box to make it larger so you can more easily see the contents of these columns.

a

 

.

In the Select Members dialog box, select the Functions tab, and select the Property function.

 

.

Add the Property function to the Selected area.

In the Edit Property Function dialog box, select the options listed in the following table:

 

.

Click OK to return to the Select Members dialog box.

In the Selected area, change the Boolean operator from Union to And.

 

.

In the Selected area, click View, and select Preview.

The Preview Market Items dialog box displays the 13 level 0 members that have a small population.

 

.

Click OK to return to the Select Members dialog box.

From the Functions tab, select the Property function and add it to the Selected area.

In the Edit Property Function dialog box, select the options listed in the following table:

 

.

Click OK to return to the Select Members dialog box.

In the Selected area, change the Union Boolean operator to And.

 

.

In the Selected area, click View, and select Preview.

The Preview Market Items dialog box displays the 3 level 0 members that have a small population and are new markets.

 

.

Click OK twice to return to the report workspace.

With the row heading selected, the member selection is displayed in the formula bar.

 

.

In the user POV bar, set the Point of View to the members listed in the following table:

 

.

Preview the report to display the 3 level 0 members that have a small population and are new markets.

 


Selecting Members with the AND and OR Boolean Operators

You continue to work with the same report. You modify the advanced member expression to specify that one of two conditions must be met. You select Market members that are children of East or children of West. You also specify that the members must have a small population.

To populate a grid with the AND and OR Boolean operators in expressions, perform the following steps:

.

For the Market dimension in row 1, open the Select Members dialog box.

From the Selected area, remove the Lev0,Market member and the Members where UDA = New Market member.

From the Available area, add Children of East and Children of West to the Selected area.

Position the selected members in the following order:

  • Children of East
  • Children of West
  • Members where Population = Small

 

.

In the Selected area, to the right of Children of East, change the Boolean operator from Union to Or. To the right of Children of West, change the Boolean operator from Union to And. This expression selects the children of East or children of West that have a small population.

 

.

In the Selected area, click View, and select Preview.

The Preview Market Items dialog box displays the 6 members that are the children of East or West and have a small population.

 

.

Click OK to close the Select Members dialog box.

With the row heading selected, the member selection displays in the formula bar.

 

.

Preview the report to display the 6 East or West children that have a small population.

 

Selecting Members with the NOT Boolean Operator

You continue to work with the same report. You modify the advanced member expression to specify the inverse of one condition. The Market members must be children of East or children of West, and the members must not have a small population.

To populate a grid with the NOT Boolean operator in expressions, perform the following steps:

.

For the Market dimension in row 1, open the Select Members dialog box. In the Selected area, to the left of Members where Population = Small, select the Not check box.

This expression selects the children of East or children of West that do not have a small population.

 

.

In the Selected area, click View, and select Preview.

The Preview Market Items dialog box displays the 4 members that are children of East or children of West, and do not have a small population. These members have a medium or large population.


.

Click OK to close the Select Members dialog box.

With the row heading selected, the member selection is displayed in the formula bar.

 

.

Preview the report to display the 4 East or West children that do not have a small population.

 

Summary

In this tutorial, you should have learned how to:

Resources

Hardware and Software Engineered to Work Together About Oracle | Oracle and Sun | Oracle RSS Feeds | Subscribe | Careers | Contact Us | Site Maps | Legal Notices | Terms of Use | Your Privacy Rights