CellValue

Syntax

CellValue

Notes

This function can be useful when defining format strings for a member. Most MDX expressions can be used to specify format strings; however, format strings cannot contain references to values of data cells other than the current cell value being formatted. Use this function to reference the current cell value.
Enclose all format strings within the MdxFormat( ) directive as shown in the examples.

Example

Example 1

The following format string displays negative values for the current measure if the current [AccountTypes] member is of type “Expense”. CellValue refers to the current cell value that is being formatted. The CurrentMember function in the expression refers to the context of the cell being formatted.

/* Display negative values if current Account is an Expense type account */
MdxFormat(
IIF(IsUda(AccountTypes.CurrentMember, "Expense"),
    NumToStr(-CellValue()),
    NumToStr(CellValue()))
)

Example 2

The following format string displays negative cell values as positive values enclosed in parentheses.


MdxFormat(
   IIF(
         CellValue() < 0,
         Concat(Concat("(", numtostr(-CellValue())), ")" ),
         numtostr(CellValue())
      )
)

Example 3

This example illustrates a dynamic member [Variance %] along the [Scenario] dimension. [Variance %] has the following formula, which specifies how to calculate its value from [Actual] and [Budget].

[Variance %] Formula

IIF(Is(Measures.CurrentMember, Title) OR
    Is(Measures.CurrentMember, Performance),
     (Actual – Budget) * 10, (Actual – Budget)*100/Budget)

[Variance %] also has the following format string, which specifies how its values should be displayed. In this case, based on the percentage value computed for a [Variance %] cell, a text value is displayed which conveys the importance of the number.

[Variance %] Format String


MdxFormat(
CASE
    WHEN CellValue() <= 5 THEN		“Low”
    WHEN CellValue() <= 10 THEN		“Medium”
    WHEN CellValue() <= 15 THEN		“High”
    ELSE                                 “Very High”
END
)