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:
Viewing 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 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.
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 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.
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 |
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.
| ||||||||
| 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.
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.
| ||||||||
| 6. | In the Select Members dialog box, select the Functions tab, and select the Property function.
| ||||||||
| 7. | Add the Property function to the Selected area. In the Edit Property Function dialog box, select these options:
| ||||||||
| 8. | Click OK to return to the Select Members dialog box. In the Selected area, change the Boolean operator from Union to And.
| ||||||||
| 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.
| ||||||||
| 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:
| ||||||||
| 11. | Click OK to return to the Select Members dialog box. In the Selected area, change the Union Boolean operator to And.
| ||||||||
| 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.
| ||||||||
| 13. | Click OK twice to return to the report workspace. With the row heading selected, the member selection is displayed in the formula bar.
| ||||||||
| 14. | In the user POV bar, set the Point of View to these members:
| ||||||||
| 15. | 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 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:
| ||||||
| 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.
| ||||||
| 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.
|
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.
|
| 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.
|
| 3. | Click OK to close the Select Members dialog box. With the row heading selected, the member selection is displayed in the formula bar.
|
| 4. | Preview the report to display the 4 East or West children that do not have a small population.
|
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 |
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.. |
 | Popular Downloads |  |
|
| ||
|
More Middleware Downloads
|
|
Printer View