You can use a combination of members, functions and/or operators to create a formula using the Functions and Members tabs, as well as the operators buttons. You can create a formula by using members and operators, without functions, or you can combine members, functions and operators in a formula. You also can create and edit advanced expressions by manually entering and editing the formulas.
An editor field, Formula Body, allows you to manually enter and edit the formula using a syntax that resembles multidimensional expressions (MDX) for defining members. However, while the syntax definition for defining members resemble the MDX format, MDX statements are not supported in the Web Analysis calculations. A formula is created by selecting a combination of Function and Member(s). For more information, see Using the Manual Formula Syntax.
Toolbar buttons for Cut, Copy, Paste and Validate are activated when a formula is entered in the Formula Body. The Validate button checks the validity of the formula for any errors and returns an error box to confirm whether validation succeeded or an error exists. The result of the formula is shown in the Formula field and can be inserted into the report by clicking the Insert button. Existing functions can be grouped together in one expression. If you want to switch between seeing only members or only calculations, or both, you can use the Hide option in the shortcut menu on your document.
Note: | You can select Dynamic row or column references instead of selecting members from the report. The formatting is applied regardless of the member selection. If a calculation refers to a specific column, and that column is deleted through member selection, the calculation is handled correctly. If a swap or pivot is performed, the calculation will “carry” where possible. |
To create a calculation:Right-click a dimension member header and select Analysis Tools, then Calculation.
The Calculation Definition dialog box is displayed, containing three tabs: Functions, Members and Options.
The Members tab is for selecting members and adding them to a formula (for example, [Sales]-[COGS], outside a formula function. You can select one or more members in the Select Members list, click Insert to insert to the formula Definition.
The Functions tab is for inserting formula functions into the Definition area after selecting a function and members. You can add and remove members and constants to the formula. Once a function is selected, it appears in the Formula area. Members (or dynamic references) can be added to the formula by clicking on each member/reference, then clicking on Add to add it to the Arguments area. The Formula area is updated with arguments once they are added. When the formula is complete, it can be inserted to the Definition area by clicking Insert.
The Options tab provides Missing Values and Select Positions settings.
The following areas are displayed for all three tabs:
Name — the name of the formula.
Definition — displays the formula. It allows for manual entry and editing of the formula. The Cut, Copy, Paste, and Validate buttons become enabled.
Mathematical Operators — symbols that provide types of calculations that you can perform on the elements of a formula; addition (+), subtraction (-), multiplication (*), division (/), parenthesis (( )).
You can create a formula by using members on the Members tab. Perform the following:
In the Select Members area, select members.
In Advanced — click to list all dimension member combinations and add them to the formula Definition area. When deselected, only members from one dimension are shown.
In Dynamic References — select to enable the selection of a row or column reference
To create a formula using functions, in the Functions tab, perform the following:
Replace the undefined arguments, displayed with a (?) in the Arguments area, with a member located in the Select Members area. Do this by selecting the member and clicking the right arrow button.
To replace an undefined argument (?) with a value, enter a value in the Constant text box, and click the right arrow button.
To replace an undefined argument from dimension member combinations, select Advanced to display dimension member combinations. Click to select a dimension member combinations label, and click the right arrow button to replace an undefined argument (?).
Select All Members to quickly select all available dimension members.
The following list describes the items on the Functions tab.
Formula — displays the formula result
Functions — provides pre-written formulas such as adding values, dividing values, calculating averages
Select Members — provides members to be included during calculation
Advanced — click to select a dimension member combination label. If the row or column context of the calculation has multiple dimensions, Advanced shows all dimension in the row or column. When deselected, only members from one dimension are shown.
All Members — select to select all dimension members
Dynamic References — enable to select a row or column reference.
Constant — type a value to use in a function. For example, instead of Sum(“Qtr1”, “Qtr2”), you could have Sum(“Qtr2”, 155)..
Optional. Opposite Member — select to evaluate a dimension member argument as a percentage of another dimension member argument. The second argument is defined by the dimension member intersected on an opposite axis
Select a Percent of Member calculation from the Function list.
Select a dimension member argument.
Select a dimension member from the opposite axis using the Optional Opposite Member list.
Note:
The Opposite Member list in the arguments frame is enabled only for the Percent of Member Calculation. This control evaluates a dimension member argument as a percentage of another dimension member argument. The second argument is defined by the dimension member intersected on an opposite axis.
On the Options tab, perform the following:
Ignore Calculations — excludes calculated members from the equation when “All Members” is selected.
Optional. Missing Values — provides option for how missing values are treated; include, exclude, or treat as a number that you specify.
Include — calculates missing values as they are stored.
Exclude — removes arguments populated by missing values from the calculation.
Treat as Number — populates the argument with the value indicated. Default is zero (0).
Select Position — provides options for specifying the location of the calculated row or column.
Front/Top
Back/Bottom
Insert Before
Insert After
Insertion Point — If you selected Insert Before or Insert After, select an insertion point from the corresponding list. The calculated row or column is inserted before or after the specified dimension header.
Ignore Calculations — selected to exclude calculated members from the equation when “All Members” is selected.
Using the Manual Formula Syntax
If a member name contains unsupported characters or symbols, it should be enclosed in brackets – []; If the name does not contain such symbols, it can be used as is. Some guidelines:
[New York] + West + East — correct
East + New York — incorrect: member name New York contains spaces.
Old style syntax (member names are separated by “:”). For advanced members, can be used if member names contain “good” symbols only. for example:
Qtr1:West + Qtr2:East — correct
Qtr3:New York — incorrect
For new MDX style syntax, a list of comma – separated member names is included between “('and')”. Member names must be included within [] according to the same rules as for simple members. For example:
([New York], West) — correct
(New York, West) — incorrect
Simple and advanced members must not be mixed in one formula body. For example:
([New York], West) + East — incorrect
Qtr1:West + South — incorrect
All members' specification in the script is defined by keyword @selectAllMembers. For example:
Average(@selectAllMembers) + 1234 + [East]
All members' specification can be used in the script several times. For example:
Sum(@selectAllMembers) – Sum(@selectAllMembers) — correct, always zero
Dynamic references are specified by the following way; for rows they are in form @row<ROW NUMBER>, for columns they are @col<COLUMN NUMBER>. For example:
@col + @col5 – @col16/100@row2 – @row3
In one expression, row and column dynamic references should not be mixed. For example:
@col + @row1 — incorrect. An error message occurs.
You can mix members and dynamics references in one expression, with the following restriction: If the axis (row or column) contains several dimensions, an advanced members' specifications must be used. For example:
@row1 + [Qtr1] — correct only if row axis contains one dimension – Year
@col2 + Qtr1:West — correct if column contains Year and Market.
Opposite members are used only for one function – Percent_of_Member. An @ character must precede the opposite member. For example:
2* Percent_of_Member (Qtr1, @Colas) — correct
[Qtr2] + Percent_Of_Member (Qtr, @Root Beers]) — correct
Percent_Of_Member (Qtr1, @ [Root Beers]) — incorrect; space after @