1. Database Administrator's Guide for Oracle Essbase
  2. Typed Measures
  3. Format Strings

Format Strings

Using format strings, you can format the values of Essbase members in numeric type measures so that they appear, for query purposes, as text, dates, or other predefined value types. You can define format strings on all members in the measures dimension, and on formula members in other dimensions.

The displayed value when you use format strings is the cell’s formatted value (FORMATTED_VALUE property in MDX).

The underlying real value is numeric, and this value is unaffected by the associated formatted value. Format strings enable you to display more meaningful values in place of raw numeric values. For example, using a text based formatted value, you might display data cells as "High," "Medium," and "Low."

Text and date type values are additionally supported using the built-in text and date measure types. Format strings add more flexibility to your implementation, in that you can apply format strings to members in multiple dimensions, whereas with text and date measures, you can only apply one or the other to a single measures dimension. You can apply format strings to numeric dimensions; you do not have to type the dimension as text or date.

Format strings can be applied to either aggregate storage (ASO) or block storage (BSO) databases.

Topics:

Implement Format Strings

Format strings are supported for aggregate storage (ASO) and block storage (BSO) databases. You implement format strings at the database level. Use the following workflow to enable and use format strings in Essbase.

  1. In the outline properties, enable typed measures.

  2. In the Accounts dimension, create a measure whose members you want to format, and in its member properties, edit the Associate Format String field to create an MDX format directive. For the syntax to create an MDX format directive, see MDX Format Directive.

Limitations of Format Strings

Format strings are not supported across partitions.

The maximum length a format string can be is 256 characters.

Shared members (including implied shares) cannot have separate format strings. Shared members inherit the format string of the prototype member.

MDX Format Directive

You define an Essbase format string using an MDX expression, in both ASO and BSO cubes.

The MDX syntax for applying a format string is:

format_string_expression = MdxFormat (string_value_expression)

where string_value_expression is a valid MDX string value expression as described in the MDX specification (see MDX Grammar Rules).

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. The current cell value can be referenced using the MDX CellValue function.

Essbase treats members with invalid format strings as if there is no format string defined. Outlines can be saved with invalid format strings. Essbase generates a warning if a query consists of a member with an invalid format string.

If a member is not associated with a format string, default format rules are applied. The default format rules format a cell based on whether the measure is numeric, text, or date type. For numeric measures, the default formatted value is the text version of that number. For text measures, the default formatted value is the text value based on the associated text list object. For date values, the default format is a date string formatted according the date format string defined in the outline properties.

Functions Supporting Format Strings

There are some MDX functions that are useful when you have format strings applied to Essbase measures.

Format strings are applied as MDX expressions, in both ASO and BSO cubes.

  • EnumText returns the text list label associated with the internal numeric value.

  • EnumValue returns the internal numeric value for a text list label.

  • CellValue returns the internal numeric value of the current cell.

  • NumToStr converts a value to a decimal string.

The @ENUMVALUE calculation function can be useful when writing calculation scripts for a block storage database that has text measures or format strings. This function returns the text list label associated with the internal numeric value.

The MaxL alter session set dml_output statement has a clause set formatted_value on | off. By default, formatted values are displayed in queries, but this statement can be used to turn off the display of formatted values.

The OUTFORMATTEDVALUES Report Writer command returns formatted cell values in a report.