Expression Section – Use this part of the window to build the expression for the calculated measure item. Objects selected from the View Items and Functions for Expression portion of the window display in the Expression section on the right side of this window. You can also add objects to the expression by typing directly in the space provided on the right side of the window.

Objects available to work with as you build your expression are described below.

Hierarchies Folder – Hierarchies, levels, and attribute relationships that are part of the view definition are in list in the Hierarchies folder. They appear in the same order as in the view explorer and regardless of whether or not they are visible or actively showing in the grid.

The Levels folder can be expanded to see the Attribute Relationships and Members folders. If attribute relationships were selected for the level, they will display when the Attribute Relationships folder is expanded.

• Levels and Attribute Relationships - Click a level or attribute relationship to add it to the expression. Objects can also be drag and dropped into the Expression portion of the window.

• Members - Another tool for building the expression is the Members folder. Click it to access the Select Filter Method window. From there, you can access the Select or Advanced Select Members window. This allows you to select specific members for the expression. Or, you can access the Named Set window and select a named set for the expression in cases when you are working with a single level time dimension.

When you add objects in this manner to the expression, they are added in MDX format. That format includes a reference to the object dimension and hierarchy. The standard MDX format for objects commonly used in expressions follows.

• Level - [Dimension name].[Hierarchy name].[Level name].members

• Attribute Relationship - [Dimension name].[Hierarchy name].Properties("Attribute Relationship name")

• Member - [Dimension name].[Hierarchy name].[Level name].[Member value]

Measure ItemsFolder – The Measure Items folder lists all the measure items associated with the view. Each measure item displays as Name (Caption). The name is the unique identifier associated with the measure item, which can be seen in the Properties window for the measure item. The caption makes it user friendly. They appear in the same order as in the view explorer.

You can include measure items as part of the expression using any of the following methods:

• Select measure items by clicking or drag and drop.

• Key in measure item names and captions directly in the Expression portion of the window in proper MDX format:

[Measures].[Name(Caption)]

where Name is the unique identifier that you can see for the measure item displayed in the Measure items folder of the expression window and Caption is the text that identifies the measure item in the view (caption also shows next to the Name for the measure item in the Measure Items folder).

MDX Functions Folder – A Functions folder provides you with logical, member, numeric, set, tuple, and VBA functions that can be used for building the calculated or distinct calculated measure item expression. You can select a function by clicking, double clicking, or drag and drop. You can also key in a function directly in the Expression portion of the window.

If you select a function for your expression, then the formula for it will display in the Expression section and each parameter will be enclosed in double arrows (<< >>). Function parameters enclosed in double arrows, such as «PARAMETER», are required. Parameters enclosed in brackets and double arrows, such as [«PARAMETER»], are optional. You can highlight each parameter and type over it directly in the Expression section. Or you can click the needed element from the tree structure in the window and it will be inserted into that section of the function.

Stratum.Viewer Functions Folder – This folder includes custom Stratum.Viewer functions that serve as shortcuts for building commonly used calculations, such as cumulative and percent of total functions. If you use them in an expression, they will be preceded by a pound sign (#) to distinguish them from standard MDX functions. The Stratum.Viewer specific functions are:

• ABC Cumulative Percent of Total

• ABC Cumulative Total

• Achievement Percent

• Cumulative Percent of Total

• Cumulative Total

• Divide With Zero Check

• Percent of Change

• Percent of Subtotal

• Percent of Total

• Total

There are pop-up labels for all functions, and they give you a brief description of the functions.

General Section – Use this section to set up a custom caption for the calculated measure item and to select a format for it.

Caption Expression – Clicking the Browse button  opens a Caption Expression window. You can set up a custom caption from that window. Typing directly in the Caption Expression field is another option for setting up a caption. This is recommended if your caption uses only static text. If your caption includes variables, then we recommend that you use the Caption Expression window.

Format – Choose the format from this drop-down list.  

Validate – Click the Validate button at any point while you are building the expression. Stratum.Viewer will verify whether or not the format of your expression is valid. If you do not click the Validate button while building the expression, then validation will be performed once you click the OK button.

OK / Cancel – Click OK to add the measure item or Cancel to discard the measure item you were setting up.

Help – Click Help or the Examples link in the Expression section to access example expressions for calculated measure items.