Create Member Formulas

You can create and edit member formulas for both block storage and aggregate storage cubes. Formulas are calculated through default calculations and calculation scripts. Add member formulas using the outline editor in the Essbase web interface.

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

To work with member formulas, we'll import the Sample_Basic application.
  1. In the Essbase web interface, click Import.
  2. In the Import dialog box, click Catalog.
  3. Go to gallery > Applications > Demo Samples > Block Storage, select Sample_Basic.xlsx and click Select.
  4. Rename the application if needed and click OK to build the cube.

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 Home page, navigate to the application, and select the Actions menu Image of the Actions menu icon in the Redwood interface, on the Applications page, next to the application name..
  2. Choose Databases > Database Name > Launch Outline.

  3. If the outline is locked, and you are an administrator, click Unlock 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 Image of the Redwood Edit outline icon..

  5. Select the Product dimension, add a child called Watchlist_Products, and click Add.

  6. Click Close to close the Add Members dialog box.

  7. Right-click Watchlist_Products, and select Inspect, and then click Formula.

  8. To show the member tree, click the Show Member Tree arrow on the left hand side of the Formula Editor.

  9. To show the functions list, click the Show 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 Image of the Redwood save outline icon..

  1. On the Applications page, expand the Sample application 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.
  16. Choose from Restructure Database Options. See Restructure Cubes.

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 to the formula. 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.