Working with Format Strings
Using format strings, you can format the values (cell contents) of Essbase database members in numeric type measures so that they appear, for query purposes, as text, dates, or other types of predefined values. The resultant display value 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 or block storage databases.
Format strings can be defined on the following members:
-
All members in Measures dimension
-
Members associated with explicit formula strings on other dimensions
Topics:
Implementing Format Strings
Format strings are supported for aggregate and block storage databases. You implement format strings at the database level.
Use the following workflow to enable and use format strings:
-
In the outline properties, enable typed measures.
-
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.
MDX Format Directive
A format string is defined by the following syntax:
format_string_expression = MdxFormat (string_value_expression)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
The following MDX functions can be useful when applying format strings to a measure. Format strings are applied as MDX expressions, in both aggregate and block storage databases.
-
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.