Selecting Members with Advanced Expressions


Purpose


This tutorial covers selecting members dynamically by using expressions. You define criteria by creating expressions of members and Boolean operations.

Time to Complete


Approximately 30 minutes

Topics


This tutorial covers the following topics:

Overview
Scenario
Prerequisites
Selecting Members with the AND Boolean Operator
Selecting Members with the AND and OR Boolean Operators
Selecting Members with the NOT Boolean Operator
Summary
Related Information

Viewing Screenshots


Click icon to show all screenshots Click icon to show all screenshots

Note: Alternatively, you can click an individual icon (or image) associated with each step to view (or hide) the screenshot associated with that step.

Overview


You can make member selections in Financial Reporting 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 OperatorDescription
ANDAll conditions must be met
OROne condition of several must be met
UNIONIntersections of similar data
NOTInverse 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.

Examples of member selections:

Member SelectionDescription
Descendants of Market AND Level 0All descendants of Market that are level 0 members
Descendants of Product AND Members matching ???-10All 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)

 

Back to Topic List

Scenario


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

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

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

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

Back to Topic List

Prerequisites


Before starting this tutorial, you should:

1.Have access to a database connection, for example, for an Essbase or Financial Management data source
2.Have access to Reporting Studio
3.Have experience creating basic reports in Reporting Studio

 

Back to Topic List

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 must have a small population.

To populate a grid by using the AND Boolean operator in an expression, perform these steps:

1.

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.

layout

 

2.

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

 

3.

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

 

4.

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.

 

5.

Click OK to return to the Select Members dialog box.

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

step

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.

step

 

6.

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

step

 

7.

Add the Property function to the Selected area.

In the Edit Property Function dialog box, select these options:

Parameter NameValue
Property TypePopulation
Property ValueSmall
Property Operator=

 

step

 

8.

Click OK to return to the Select Members dialog box.

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

step

 

9.

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.

step

 

10.

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 these options:

Parameter NameValue
Property TypeUDA
Property ValueNew Market
Property Operator=

 

step

 

11.

Click OK to return to the Select Members dialog box.

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

step

 

12.

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.

step

 

13.

Click OK twice to return to the report workspace.

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

step

 

14.

In the user POV bar, set the Point of View to these members:

DimensionMember
MeasuresProfit
ProductProduct
ScenarioActual

 

step

 

15.

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

step

Back to Topic List

 

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 these steps:

1.

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 the Children of East and Children of West to the Selected area.

Position the selected members in this order:

Children of East
Children of West
Members where Population = Small

 

 

2.

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.

step

 

3.

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.

 

4.

Click OK to close the Select Members dialog box.

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

 

5.

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

Back to Topic List

 

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 these steps:

1.

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.

step

 

2.

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.

step

 

3.

Click OK to close the Select Members dialog box.

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

step

 

4.

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

step

Back to Topic List

Summary


In this tutorial, you've learned how to:

Select members with the AND Boolean operator
Select members with the AND and OR Boolean operators
Select members with the NOT Boolean operator

Back to Topic List

Related Information


To learn more about creating advanced expressions to select and exclude members with Boolean operators, refer to the Oracle Hyperion Financial Reporting Studio, Fusion Edition Release 11.1.1 User’s Guide.
To learn more about Financial Reporting, refer to additional OBEs..

Back to Topic List

Left Curve
Popular Downloads
Right Curve
Untitled Document
Left Curve
More Middleware Downloads
Right Curve