Internal Cube Formula
Our first example involves calculating figures for Margin % in the Sales cube from Margin and Revenue values, also in the Sales cube. Proceed as follows:
To create a formula for Margin %:
1. In the PowerOLAP ribbon, select Model tab and Cubes command.
The Cubes dialog box is opened.
2. Select the Sales cube, then click the Formulas… button.
(Alternately, double-click once on the Sales cube.)
Under the last formula written in the Formula text box, press the Enter key to put a space between the last and the new formula to be written.
You may write a comment explaining what the formula is calculating. The comment can begin with two forward slashes (//), which will cause the formula editor to ignore everything that follows on that line. Alternatively, you can use a single forward slash followed by an asterisk (/*)—everything that follows (on consecutive lines) will be ignored up to the point where an asterisk, then forward slash (*/) conclude the comment.
3. With the cursor on a new line in the Formula text box, click the Build Range Reference button to define the LHS range reference. The Build Range Reference dialog box appears:
4. Select a Qualifier (in this case just select All).
Specifies that the LHS range includes all, i.e., both Aggregate and detail level, cells within the specified Member set(s).
Specifies that the LHS range includes only aggregate level cells within the specified Member set(s).
Specifies that LHS range includes only detail level cells within the specified Member set(s).
5. Select a <Dimension> (in this case, Sales Account)
from the Dimension list box on the left.
6. Select Margin % from the Members list box on the right.
7. Click OK.
8. Type = , or press the button, after the LHS of the formula.
9. Select the Build Cube Reference button.
The Build Cube Reference dialog box appears.
10. Select the <Cube> from the top drop-down menu, whose data you want to use (in this case, it is the same Cube, Sales—so Sales appears in the grayed-out Formula text area, at bottom).
[Note, if you selected another Cube, you would be building a cross-cube formula.]
11. Select a <Dimension> (in this case, again, it is Sales Account)
from the Dimension text box.
12. Select a <Member> from the Members text box for the first part of the equation
(in this case, Margin).
13. Click OK.
You can add a line break (Enter) anywhere in the formula text (as above, after the equals sign), to make the formula more legible—this has no bearing on the formula result.
14. Now you must finish the equation by building the last part of the statement so that Margin % = Margin/Revenue.
15. Again, click on the Build Cube Reference button and choose the Sales cube and Sales Account dimension.
16. Select the Member Revenue and click OK.
17. The RHS of the formula now must be completed by adding the semi-colon.
18. Now click on the Check Syntax button in the Formulas dialog box to check the syntax of the formula for errors.
The following message box appears (against the background of the Formulas dialog box, as in the following figure):
If there is an error in the formula syntax, PowerOLAP® will return a message stating this, and a possible resolution (see example message box below).
19. Click OK.
20. You are returned to the Cubes dialog box.
Click OK to exit the dialog box.
In this next topic, we will discuss how to create an IF statement in PowerOLAP. In the previous topic, we have noted that the slice returned some error values (i.e., #DIV/0 errors). These errors resulted from trying to divide a certain number or value by zero, which then will yield an undefined result.
To resolve those errors, as in the example above, you can define an IF statement to set a condition that if the divisor is empty, then force the result to become a certain number, text or value. In the demonstration below, an IF statement will be created to set the result to zero if the divisor is zero.
1. To create the IF Statement, go to the Cubes dialog and return to the Formula Editor dialog box for Sales cube.
2. Go to the formula for calculating the Margin % and place your cursor just after the “equals” sign, i.e., in front of the RHS of the equation:
3. We can use the IF statement pre-formatted for us in the Functions list box of the Formula Editor on the left by double clicking on the function, which brings it into our Formula text, or you may simply type in directly the IF expression:
4. First define the test Condition by highlighting the word testCondition and replace it with the appropriate conditional statement —i.e., Revenue = 0.
5. To create the first part of our statement
(the “testCondition”, meaning “if Revenue equals zero”), first build the reference to Revenue by clicking on the Build Cube Reference button.
From the Sales cube, choose the member Revenue from the Sales Account dimension. Click OK.
Then add =0, (don’t forget the comma after the zero).
6. Next, create the “trueExpr” (“then return the value zero”),
highlight the word and simply type 0, (don’t forget the comma).
7. Next, create the “falseExpr” (“otherwise return the calculation Margin/Revenue“)—Since this already exists as the formula entered earlier in the exercise, simply higlight the formula, then paste it over the parameter “falseExpr”.
8. Next, make sure that your formula is ended with a semi-colon ( ; ).
9. Click the Check Syntax button and click OK.
At this point, you can delete the formula, copy it to a new location (e.g., a text file), or surround it with forward-slash, asterisk (concluding with asterisk, forward-slash) in order to proceed with the following example, also a calculation of Margin %, but this time the result of a cross-cube formula.