Essbase - FormatString
By Torben Hein -Oracle on Oct 17, 2013
"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."
To actually switch ON the use of typed measures in general, you need to navigate to the outline properties:
- open outline
- select properties
- change "Typed Measures enable" to TRUE
As an example, I created two additional members in the ASOSamp outline.
- A member "delta Price" in the Measures (Accounts) Dimension with the Formula: ([Original Price],[Curr_year])-([Original Price],[Prev_year])
This is equivalent to the Variance Formula used in the "Years" Dimension.
- A member "Var_Quickview" in the "Years" Dimension with the same formula as the "Variance" Member.
This will be used to simply display a second cell with the same underlying value as Variance - but formatted using Format String hence enabling BOTH in the same report.
In the outline you now select the member you want the Format String associated with and change the "associated Format String" in the Member Properties.
As you can see in this example an IIF statement reading:
MdxFormat(IIF(CellValue()< 0,"Negative","Positive" ) )
has been chosen for both new members.
After applying the Format String changes and running a report via SmartView, the result is:
Essbase Database Admin Guide ,Chapter 12 "Working with Typed Measures "