Formula Basics
Formulas are written using a rich syntax that allows the straightforward specification of complex regions in multidimensional space. A formula assigns a portion of the specified Cube to the value of an equation. You create an ordered list of formulas in the Formulas dialog.
In addition to utilizing standard mathematical and accounting functions, formulas may also make references to other areas of the same cube or to other cubes (via crosscube formulas) in the same database. Formula expressions are always ended with a semicolon (;).
Before proceeding with the following Cube formula exercises, some basics aspects of Cube formulas are as explained below. Advanced Reference Materials includes a detailed explanation of Cube Formula Grammar, as well as Dependencies Grammar Definition.
Cube formulas are created and associated for a specific Cube. As a result, your first step in creating a formula will be to select a Cube from the Model menu. After creating the formula, proCube will add it to an ordered list of formulas within that Cube (you will create the list in the Formulas dialog). proCube checks the list and applies the first formula it finds for a selected variable.
Formulas appear in order of priority in the Formulas dialog. Since more than one formula can “overlap” within a given area of a Cube, there is a priority based on where a formula appears in the Formulas dialog (and you will determine that priority in the dialog). As a general rule, the higher the formula in the dialog, the greater the priority. Thus, the following priority rules apply:

The first formula takes precedence over all other formulas.

The second formula takes precedence over all that follow it.

The third formula takes precedence over all that follow it; and so on.
Keep this in mind as you create and list formulas in the dialog.
A valuable rule of thumb applies for the priority order of formulas: In general, the ordering of formulas takes the form of an inverted triangle, with the more complex formulas at the top. Begin with the formula whose definition contains the greatest number of constraints and work through to the formula that will encompass most of the cells at the outcome.
Formulas override values of Detail and Aggregate members. Thus, Aggregate member values calculated in the dimension hierarchy are replaced by formula results.
Formulas may be “intradimensional,” or calculated across dimensions. The first example in this topic demonstrates a calculation for data derived from two Members in the same dimension (Sales Account): Margin % =Margin/Revenue. But it would also be possible to create a “crossdimensional” formula so that, for example, Margin % could be shown as a Member of the Months dimension.
proCube also enables you to create crosscube formulas. Crosscube formulas are used to dynamically calculate values in a Cube using data from other Cubes. An example of a crosscube formula is shown in Crosscube formula.
Using the formulas text editor
On the right side of the Formulas dialog is the text editor area. The editor provides extensive text controls, including standard tools as well as special techniques helpful for formula work. Notice the toolbar at the top of the dialog.
Figure 1. Formulas Dialog
You can roll the mouse over any button in the toolbar for an explanation and a description of the corresponding keyboard shortcut. The following features are available:

Search and replace.

Ability to expand and collapse long lines to improve legibility. Click the plus [+] and minus [–] buttons.

Multiple undo and redo. Use the buttons or ControlZ/ControlY.

Block indenting. Select one or more lines and press [Tab].

Bookmarks to speed navigation within large files.

Automatically colorcoded text:

Comments, which are defined as any text that resides between /* */ or any that is on a line following //, are shown in green text.

Keywords, which include All, And, Aggregates, and Details, are shown in red text.

Functions, which include all the functions in the formula language, are shown in blue text.
Support of extensive keyboard shortcuts for cursor movement and text selection, similar to Microsoft Word, is described next.
Keyboard shortcuts in the formula text editor
In addition to the common keyboard shortcuts such as Home and End, the Formula Editor supports the following special shortcuts.
Action 
Keyboard shortcuts 
Magnify text size 
Ctrl+Keypad+ 
Reduce text size 
Ctrl+Keypad 
Restore text size to normal 
Ctrl+Keypad/ 
Indent block 
Tab 
Outdent block 
Shift+Tab 
Delete to start of word 
Ctrl+BackSpace 
Delete to end of word 
Ctrl+Delete 
Delete to start of line 
Ctrl+Shift+BackSpace 
Delete to end of line 
Ctrl+Shift+Delete 
Line cut 
Ctrl+L 
Line copy 
Ctrl+Shift+T 
Line delete 
Ctrl+Shift+L 
Line transpose with previous 
Ctrl+T 
Line duplicate 
Ctrl+D 
Previous paragraph (shift extends selection) 
Ctrl+[ 
Next paragraph (shift extends selection) 
Ctrl+] 
Formula syntax and terminology
Formulas take the following general form:
<LHS> = <RHS>;
LHS = Lefthand side range, for the data to be calculated by the formula (i.e., where data will appear in the Cube).
RHS = Righthand side, which will either be a constant or a calculation performed on data from the range of a specified, originating Cube (may be the same Cube).
A semicolon ( ; ) always completes an expression.
As will be demonstrated, the LHS can be constructed using the Build Range Reference dialog and the RHS can be constructed using the Build Cube Reference dialog.
For convenience’s sake, the following formula terminology summary is included. It may be helpful to briefly familiarize yourself with this terminology before you proceed through the demonstration exercises in this topic.
A Numeric Formula is any valid arithmetic expression composed of one or more of the items in the table:
Numeric Constants 
e.g., 2, 3.5, 2.5E10, etc. 
Numeric Operators 
e.g.; +,  , * , /. 
Parentheses 
( ) 
Specific Functions 
e.g.; round, abs(x), IF, etc. 
Numeric Constants are the simplest components of a numeric formula. A numeric constant consists of digits, an optional leading sign, and an optional decimal point.
Valid Numeric Constants 
6 2 5.0 
Invalid Numeric Constants 
0a 0 3..4 
Numeric Operators are simple mathematical expressions:
Addition 
+ 
Subtraction 
 
Multiplication 
* 
Division 
/ 
Exponentiation 
^ 
If different numeric operators are used in an expression, the order of computation is:

1st — Exponentiation

2nd — Multiplication and Division

3rd — Addition and Subtraction
Parentheses may be used to force a different order of computation and are used in traditional algebraic notation. For example, 2*3+4 is the same as (2*3)+4, which equals 10. In contrast, 2*(3+4) equals 14.
Specific Functions are provided by proCube for additional computation capabilities. They have a wide variety of uses from simple sums to trigonometric and financial functions to logical constructions. For more information, go to Advanced Reference Materials.
0 Comments