1. Using Oracle Essbase
  2. Create and Manage Cube Outlines Using the Web Interface
  3. Set Dimension and Member Properties
  4. Create Member Formulas

Create Member Formulas

Build an example member formula in the outline editor, and learn how formulas are constructed using Calc language for block storage cubes and MDX for aggregate storage cubes.

You can create and edit member formulas for both block storage and aggregate storage cubes. These formulas are calculated through default cube calculations and calculation-script calculations.

You can construct block storage member formulas from operators, functions, dimension names, member names, substitution variables, and numeric constants. To write formulas for block storage outlines, a set of calculation functions and operators is provided. For syntax and examples, see Calculation Functions.

Aggregate storage member formulas cannot be created using Calculator language. Instead, create them using Multidimensional Expression Language (MDX).

Let’s create an example member formula. Suppose you have a dynamic calc member called “Watchlist Products” and you want it to be the sum of products “100-10”, “200-10” and “300-10.”

  1. On the Applications page, open <yourapplication> and then open the database (cube).
  2. Click Launch Outline.
  3. If the outline is locked, and you are an administrator, click Unlock outline Image of the Unlock outline icon in Redwood..

    Before you forcefully unlock a locked outline, make sure that no one else is working with it.

  4. Click Edit outline Image of the Redwood Edit outline icon..
  5. Select the Product dimension, add a child called Watchlist_Products, and click Add.
  6. Click Image of the Redwood close icon. to close the Add Members dialog box.
  7. Right-click Watchlist_Products, and select Inspect, and then click the Formula tab.
  8. To show the member tree, on the Formula tab, click the Show or Hide Member Tree arrow on the left hand side of the Formula Editor.
  9. To show the functions list, click the Show or Hide Functions List arrow on the right hand side of the Formula Editor.
  10. In the member tree, in the left panel of the Formula Editor, drill into Product to find the first product member to add to your formula, “100-10.” Right click the member name and click Insert Name to insert it into your formula.
  11. Place the cursor after "100-10" in the formula being created and press the + key.
  12. Use the member tree to pick the next product member to insert, 200-10. Right click the member name and click Insert Name to insert it into your formula.
  13. Place the cursor after "200-10" and press the + key.
  14. Repeat for the last product member, 300-10 and put a semi-colon (;) at the end of the formula.

    The formula should look like this: "100-10"+"200-10"+"300-10";

  15. Click Verify and fix any errors.
  16. Click Apply and Close.
  17. For the Watchlist_Products member, double-click Store data in the Data storage type column, and select Dynamic calculation.
  18. Click Save outline Image of the Redwood save outline icon..
  1. On the Applications page, expand <yourapplication> and select the Basic cube.
  2. Click the Actions menu and select Outline.
  3. Click Edit.
  4. Select the Product dimension, add a child called Watchlist_Products, and press the Tab key.
  5. Right click on Watchlist_Products and select Inspect.
  6. Select the Formula tab.
  7. In the member tree, in the left panel of the Formula Editor, drill into Product to find the first product member to add to your formula, “100-10.” Right click the member name and click Insert Name to insert it into your formula.
  8. Place the cursor after "100-10" and press the + key.
  9. Use the member tree to pick the next product member to insert, 200-10. Right click the member name and click Insert Name to insert it into your formula.
  10. Place the cursor after "200-10" and press the + key.
  11. Repeat for the last product member, 300-10 and put a semi-colon (;) at the end of the formula.

    The formula should look like this: "100-10"+"200-10"+"300-10";

  12. Click Verify and fix any errors.
  13. Click Apply and Close.
  14. In the Data Storage Type column for Watchlist_Products, select Dynamic Calculation.
  15. Click Save to save the outline.

Member formulas like the one you just created can also include Essbase functions. When using Essbase functions in member formulas, use the Function Name menu on the right side of the formula editor to find and add calculation functions the script. See the Function description under the menu to read descriptions of each function.

See Developing Formulas for Block Storage Databases.

To write formulas for block storage outlines, a set of calculation functions and operators, known as the Calculator, or Calc, language, is provided. For descriptions of calculation commands and functions, see Calculation Commands and Calculation Functions.

Aggregate storage member formulas cannot be created using Calculator language. Instead, create them using Multidimensional Expression Language (MDX). See Aggregate Storage and MDX Outline Formulas and Developing Formulas on Aggregate Storage Outlines.