Formula Basics

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 cross-cube formulas) in the same database. Formula expressions are always ended with a semi-colon (;).

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 “intra-dimensional,” 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 “cross-dimensional” formula so that, for example, Margin % could be shown as a Member of the Months dimension.

proCube also enables you to create cross-cube formulas. Cross-cube formulas are used to dynamically calculate values in a Cube using data from other Cubes. An example of a cross-cube formula is shown in Cross-cube 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.

formulasdialog.png

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 Control-Z/Control-Y.

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

  • Bookmarks to speed navigation within large files.

  • Automatically color-coded 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 = Left-hand side range, for the data to be calculated by the formula (i.e., where data will appear in the Cube).

RHS = Right-hand 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.

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk