Creating calculated members

A calculated member is a hierarchy member whose value is calculated at run time using an expression that you specify when you define the calculated member. Calculated members can also be defined as measures. Only the definitions for calculated members are stored; values are calculated in memory on the server when needed to answer a query.

Calculated members can be created one of the following two ways:

To start the calculated member wizard, click the Calculated Member Wizard button on the editor toolbar of the hierarchy you want to create the member in.

This will display the calculated member wizard as shown below.

Select the template you want to by double-clicking on it or by selecting it and pressing Next.

Note: the available templates can differ based on the hierarchy you selected. The wizard will display different templates for the measures than for other hierarchies.

This will take you to the page on which you need to enter the parameters for the template.

You can select a value for the parameter by double clicking on the text box or by clicking on the ... button.

In the next step you can supply a name and description for the calculated member and click on Finish to complete the creation of the calculated member.

At any time during the wizard, you can switch to the editor, to modify the current state of the MDX by hand and/or set advanced options. You can also switch back to the wizard again; however, any modifications you make to the member in the editor will not be used by the wizard in this case.

A new calculated member can also be created by clicking the button on the editor toolbar of the hierarchy you want to create the member in.

This will display the calculated member dialog as shown below.

In the top part, you can provide values for the name and description fields. In the name field, you must provide the name of the calculated member.

Note that the member name must be unique among the children of its parent member in the hierarchy it belongs to.

Below is the area where you can enter the MDX formula for the member.

To help with the formula creation, you can use the tools on the left hand side.

Testing

You can check if the formula you have entered is syntactically valid and produces valid results by pressing the Test button. This will execute a query in the background using the formula specified. Upon completion, you will be notified if the query was successful or not, and the reason for failure, if any.

Options

A number of options can be set in the advanced panel that control how the calculation works:

Format string	A Microsoft Office style format string that is used when displaying cell values. A list of common format strings is provided in the drop-down list. See format strings for information on how to create your own format strings.
Parent member	The parent member determines the location of the calculated member in the hierarchy structure. By default, the parent member is the 'All' member for non-measure hierarchies and the hierarchy itself (root) for the measures dimension.
Solve order	This property specifies a priority for solving calculated members, zero being the highest priority. The order of solving calculated members becomes an issue for cells at intersections where two or more calculated members are involved and the result depends on the order in which the calculated members are solved. For example, if a calculated member for Annual Growth is presented in columns and Performance is presented in rows, the cell at the intersections can mean Annual Growth of Performance or Performance of Annual Growth, depending on which calculated member is solved first.
Non empty behavior	Specifies the measure used to determine the behavior of calculated members when resolving empty cells.
Show member in query results	Check this box if you want this calculated member to be visible in query results. If this box is not checked, this member will not be visible, however, you can still use it within other calculated member formulas. This is useful when this calculated member will hold an intermediate result that you only wish to use as a basis for another calculation.

This information can also be displayed by hovering with the mouse over the corresponding image.

Editing an existing calculated member

You can edit a previously created member by selecting the member, right-clicking and selecting Properties item from the menu. This will bring up the calculated member editor as displayed above.

If you just want to rename the member, you can press F2 or select Rename from the context menu.

Deleting a calculated member

To remove a calculated member from the hierarchy, select the member and press the Delete button, or right-click and select Delete.

If the calculated member has been selected as part of the current query, it will be removed from the query and the query will be re-executed.

Format Strings

The format string drop down list has a number of predefined formats.

You can also construct your own format strings using the following elements:

Format character	Name	Description
0	Zero placeholder	If the value being formatted has a digit in the position where the '0' appears in the format string, then that digit is copied to the output string. The position of the leftmost '0' before the decimal point and the rightmost '0' after the decimal point determines the range of digits that are always present in the output string.
#	Digit placeholder	If the value being formatted has a digit in the position where the '#' appears in the format string, then that digit is copied to the output string. Otherwise, nothing is stored in that position in the output string. Note that this specifier never displays the '0' character if it is not a significant digit, even if '0' is the only digit in the string. It will display the '0' character if it is a significant digit in the number being displayed.
.	Decimal point	The first '.' character in the format string determines the location of the decimal separator in the formatted value; any additional '.' characters are ignored. The actual character used as the decimal separator is determined by your regional settings.
,	Thousand separator and number scaling	The ',' character serves two purposes. First, if the format string contains a ',' character between two digit placeholders (0 or #) and to the left of the decimal point if one is present, then the output will have thousand separators inserted between each group of three digits to the left of the decimal separator. The actual character used as the decimal separator in the output string is determined by your regional settings. Second, if the format string contains one or more ',' characters immediately to the left of the decimal point, then the number will be divided by the number of ',' characters multiplied by 1000 before it is formatted. For example, the format string '0,,' will represent 100 million as simply 100. Use of the ',' character to indicate scaling does not include thousand separators in the formatted number. Thus, to scale a number by 1 million and insert thousand separators you would use the format string '#,##0,,'.
%	Percentage placeholder	The presence of a '%' character in a format string causes a number to be multiplied by 100 before it is formatted. The appropriate symbol is inserted in the number itself at the location where the '%' appears in the format string. The percent character used is dependent on your regional settings.
;	Section separator	The ';' character is used to separate sections for positive, negative, and zero numbers in the format string. Other All other characters All other characters are copied to the output string as literals in the position they appear.