Creating an Expression on Which to Base a Dimension Element

  To create an expression:

1. If you have not already done so, create a dimension element or open one for editing.

   • To create a dimension element, complete a procedure described in Creating Dimension Elements.

   • To edit a dimension element, locate it in the Metadata Navigator, right-click, and select Edit.

     Note:

     You can also open the dimension element for editing by double-clicking it.

2. In the Edit Properties dialog box, create an expression on which to base the dimension element.

   For example, the following expression bases the dimension element on the SKU column from the Product table:

   connection : \'tbc_source'::'tbc.product'.'SKU'

   You may also enter (hand type) an expression or statement of your own. For example, you may want to enter the string “Seasons” to use as the member name that displays in the Essbase outline or spreadsheet for this element.

   If you enter (hand type) an expression or statement of your own to define or edit a dimension element, you must follow the guidelines for delineating strings and names. See Rules for Entering User-Defined Expressions.

   Notes:

   • By default, dimension elements, whether they are text-, numeric- or date-based, retain the same datatype as the physical element on which they are based. For example, if you create a dimension element based on AMOUNT from the SALES table in the TBC database, the datatype, or unit, of that dimension element is now numeric. The key binding expression for that element must always be numeric.

     You can use operands with different unit types in the key binding expressions but the expression result should return the values with the unit that is compatible with the unit of the dimension element. If the unit of the dimension element is a string, you can apply the property .toStringto any non-string expression in order to convert the expression result to string.

     For example, suppose you created a dimension element based on the FAMILY column from the FAMILY table in the TBC database. The caption and key binding expression for this dimension element is:

     connection : \'tbcSource'::'TBC.tbc.FAMILY'.'FAMILY'

     You can change the key binding expression to a numeric-based expression by appending .toString as follows:

     connection : \'tbcSource'::'TBC.tbc.FAMILY'.'FAMILYID'.toString

   • The expression on which the dimension element is based is written in CPL (Common Platform Language). The expression is a sequence of operands and operators following the language-defined syntax. Each expression returns a value, the type of which defines the type of the expression. See CPL Reference.

   • For caption binding and key binding expressions, text file data sources only support the substring (substr) function and the concatenation operator ( || ) .

   • Essbase Studio does not verify cycle dependencies between metadata elements. Cycle dependency is a relationship between metadata elements that cycles back upon itself. See Cycle Dependency Guidelines for more information.

   • For limitations on creating expressions for independent dimensions, see Independent Dimension Bindings Limitations.

   Use the following steps as guidelines to create the expression:

   1. Select the Source tab in the lower-left of the dialog box.

   2. Expand the dimensions to display the members.

   3. Select a member.

   4. Use the right-direction arrow to move the connection string for the member to the Caption Binding text box.

   5. Select the Functions tab.

   6. Expand the SQL level to display the function types.

   7. Expand the function types to display the functions.

   8. Select a function.

   9. Use the right-direction arrow to move the function string to the Caption Binding text box.

   10. Select the Operators tab.

   11. Expand the operator types to display the operators.

   12. Select an operator.

   13. Use the right-direction arrow to move the operator to the Caption text box.