Building Expressions

To build an expression quickly, you can type it in the expression editor without selecting components from the left pane, or you can type the parts of the expression that you know and add components.

The expression editor validates the expression as you build it. If the expression is not mathematically valid, the expression editor highlights the errors in the expression, and error messages appear in the tip box.

Tip: Before typing a function into the expression editor, confirm that it is listed in the left pane. This will ensure that your application supports that function.

For information on functions, see Functions. For information on applying or working with calculations, see the appropriate product documentation.

Steps

1. From the left pane, locate and select the component you want.

2. Click the arrow button to add the component to the expression.

3. Continue adding components until your expression is complete.

4. Click OK.

Tips

• To add components quickly to the expression, double-click the component in the left pane.

• To move around in the Available components and Expression boxes you can use the mouse, or:

  • press the space bar to select a component

  • press the up and down arrows to move within the Available Components box

  • press the left and right arrows to move within the Expression definition box

• Selecting String inserts two quotation marks and positions the cursor between them. You enter the string between the quotation marks.

• Selecting Number inserts the number 0. You overwrite it with the number you want included in the expression.

• Selecting Date inserts the current date in quotation marks. You can overwrite it with other data values.

The components of an expression can include functions, summaries, values, and operators.

Building an If-Then-Else Calculated Expression

Your models can include calculated measures that use if-then-else expressions. For example, you can define the following conditional expression:

if ("Net Income"<100000) then ("Gross Profit"*1.25) else
NULL

You can also define an if-then-else calculation to avoid division by zero:

if ("MEASURE_VALUE_A"=0) then ("MEASURE_VALUE_B"=NULL) else ("MEASURE_VALUE_B"/"MEASURE_VALUE_A")

Steps

1. In the Expression Editor, begin your calculated measure definition by clicking the if operator.

   An opening parenthesis is inserted next.

2. Enter the rest of the expression as follows:

   • Type a conditional expression that resolves to True or False.

   • Double-click a closing parenthesis.

   • After the automatically inserted expression "then (", type the result to show if the boolean expression is True.

   • Double-click a closing parenthesis.

   • After the automatically inserted expression "else (", type the result to show if the boolean expression is False.

   • Double-click a closing parenthesis and click OK.

   If your expression is valid, the definition is saved for that measure.

3. If an error appears, try again, using a mathematically correct form.

   Tip: Unlike calculated columns, calculated measures support isnull( ) expressions that resolve to True, which is the condition that arises with a missing value. You can use this capability to avoid divide-by-zero overflow errors.

4. Build the cube, open it in your reporting application, and confirm that the results correctly reflect the if-then-else condition you were trying to model.