Format Strings – Who Knew?

Yet again, a post prompted by a question on OTN (this one from my UK-based co-worker Russell). And yet again, a question on OTN that results in me appreciating a feature of which I was almost completely ignorant. This one has some remarkably useful, and, as far as I know, unique applications. With format strings you can, for example:

  • Return values from Essbase including a — potentially variable — unit of measure (e.g. USD99.99, JPY250,000)
  • Return values in specific or complex formats (e.g. 22 September 2015)
  • Return values using mixed measures (e.g. 3’6″ for 42″, 12lb 5oz for 197oz)

Even better than advanced formatting, however, is the fact that Format Strings can be hacked to do some completely non-format-related but very useful things. The documentation does not discuss this possibility at all, and searching for information on this I could find only one example online in a blog post from Brian Marshall. Because Format Strings allow the use of any MDX function, we can choose to ignore data altogether and return information from metadata or other functions:

  • Retrieve a datestamp from the Essbase server on to a report
  • Show both a member and its parent (and / or grandparent, and / or great-grandparent, etc.) on the same row
  • Show the attribute associations of a member without time-consuming drill and suppress shenanigans
  • Show UDA associations

This post describes the basic Format String functionality, shows some simple and then more complex format examples, and concludes with the fun non-format ‘hacks’.

String Theory

A ‘Format String’ is a member property available in both BSO and ASO cube on which Typed Measures have been enabled. In brief, it allows developers to use MDX (again, even with a BSO cube) to format the value returned by a member. These formatted results can optionally be viewed in MDX queries, Smart View, and even the humble Report Writer.

The application of Format Strings receives limited attention in the documentation. The primary references, from the Technical Reference and Database Administrator’s Guide respectively, are:

  • http://docs.oracle.com/cd/E57185_01/epm.1112/essbase_tech_ref/mdx_cellvalue.html
  • http://docs.oracle.com/cd/E57185_01/epm.1112/essbase_db/dtypmeas.html#dtypmeas_3

At first blush there seems to be some overlap between regular text measures and Format String-enabled members. And yes, it is possible to create Format Strings that emulate the functionality (at least when retrieving) of text measures, mapping a finite list of numeric values to a list of text strings. However, and unlike text measures, Format Strings don’t support submitting data back to Essbase in non-numeric form from (e.g.) Smart View.

For all of the following examples, I’ve created a measure called ‘TestMDXFormat’ in a copy of Sample.Basic. The measure has a simple formula “=n;”, the value of n being modified to whatever is appropriate to demonstrate the relevant functionality. The Format String itself is a member property, accessible via the normal member properties dialog in the EAS console outline editor. In each case I’m providing the Format String itself followed by an example of the output in either an MDX query. Where I had to monkey with Sample.Basic to demonstrate a particular use case, the relevant simian maneuvers are described.

Simple Formatting

Negative values in parentheses

This is a simple, introductory example (in fact, it’s the same as one in the Technical Reference).

MdxFormat(
   IIF(CellValue() < 0,
      Concat(Concat("(", NumToStr(-CellValue())), ")"),
      NumToStr(CellValue())
   )
)
MAXL> SELECT {[TestMDXFormat]} ON AXIS(0),
   2>        {[Jan]} ON AXIS(1)
   3>   FROM [Sample].[Basic];

 Axis-1              (TestMdxFormat)
+-------------------+-------------------
 (Jan)                          (123.00)

One minor annoyance is that I can’t see this format reflected in Smart View irrespective of the Smart View formatting options I select unless either a) the Excel cell is formatted as ‘Text’ (not ‘General’ or ‘Number’) or b) I modify the format to prefix with an apostrophe, which forces Excel to interpret the value as text (but then includes the apostrophe in MDX query results).

Pseudo-text measures

Format Strings can  be used to map a finite list of numeric values (along with #Missing) to specific text values.

MdxFormat(
   CASE CellValue()
      WHEN 1 THEN "Red"
      WHEN 2 THEN "Orange"
      WHEN 3 THEN "Yellow"
      ELSE "Green"
   END
)
MAXL> SELECT {[TestMDXFormat]} ON AXIS(0),
   2>        {[Jan]} ON AXIS(1)
   3>   FROM [Sample].[Basic];

 Axis-1              (TestMdxFormat)    
+-------------------+-------------------
 (Jan)                               Red

Since the above functionality is already available via a more friendly interface there is little point in replicating it directly.

‘Ranged’ text measures

However, Format Strings can also be used to create ‘ranged’ text measures. Regular text measures can’t do this without creating an additional formula member.

MdxFormat(
   CASE
      WHEN CellValue() >= 0 AND CellValue() < 3 THEN "Red"
      WHEN CellValue() >= 3 AND CellValue() < 6 THEN "Orange"
      WHEN CellValue() >= 6 AND CellValue() < 9 THEN "Yellow"
      ELSE "Green"
   END
)
MAXL> SELECT {[TestMDXFormat]} ON AXIS(0),
   2>        {[Jan]} ON AXIS(1)
   3>   FROM [Sample].[Basic];

 Axis-1              (TestMdxFormat)    
+-------------------+-------------------
 (Jan)                               Red

Advanced Formatting

Seconds to HH:MM:SS

The use of Left / Right and so on deal with the fact that NumToStr returns a decimal value even when fed an integer.

MdxFormat(
   Concat
     (Concat(Concat(Left(Right(Concat("0", NumToStr(Int(CellValue()/3600))), 5), 2), ":"),
       Concat(Left(Right(Concat("0", NumToStr(Int(Mod(CellValue(), 3600)/60))), 5), 2), ":")),
           Left(Right(Concat("0", NumToStr(Int(Mod(CellValue(), 60)))), 5), 2)
   )
)
MAXL> SELECT {[TestMDXFormat]} ON AXIS(0),
   2>        {[Jan]} ON AXIS(1)
   3>   FROM [Sample].[Basic];

 Axis-1              (TestMdxFormat)
+-------------------+-------------------
 (Jan)                          03:02:01

Date to DD Mon YYYY

Dates are easily and flexibly handled thanks to the FormatDate MDX function. This understands numeric values to represent seconds since midnight on 1st January 1970 (i.e. Unix Time); appropriate offsets can be calculated to convert to other bases.

MdxFormat(
   FormatDate(CellValue(), "dd mon yyyy")
)
MAXL> SELECT {[TestMDXFormat]} ON AXIS(0),
   2>        {[Jan]} ON AXIS(1)
   3>   FROM [Sample].[Basic];

 Axis-1              (TestMdxFormat)    
+-------------------+-------------------
 (Jan)                       25 Dec 1999

Numbers to Natural Language

While the MDX is complex, it is possible to write a formula that converts a number to natural language, e.g. “Two thousand, one hundred and ninety four”. Unfortunately, the Format String property seems to be limited in length to 1032 characters, at least when setting it via EAS. I found that even a very limited formula handling for values up to 100 required more than 1032 characters.

Units of Measure

Fixed Units

Fixed units are very straightforward. I admit I haven’t built many models in real life with milliamp measures.

 MdxFormat(
   Concat(
      NumToStr(CellValue()),
     "mA"
   )
)
MAXL> SELECT {[TestMDXFormat]} ON AXIS(0),
   2>        {[Jan]} ON AXIS(1)
   3>   FROM [Sample].[Basic];

 Axis-1              (TestMdxFormat)    
+-------------------+-------------------
 (Jan)                        1,476.00mA

Variable Units

Variable units such as currency can be driven by UDA tags. In this example, I added Ontario and Quebec to the Market dimension tagged with UDA “CAD”, while the US states are tagged with “USD”.

Awkwardly, none of the MDX functions actually return the name of the UDA associated with a member. Consequently each possible UDA must be tested, meaning that this approach will work best when the UDA list is a) short and b) static.

 MdxFormat(
   Concat(
      CASE 
         WHEN IsUda([Market].CurrentMember, "CAD") THEN "CAD"
         WHEN IsUda([Market].CurrentMember, "USD") THEN "USD"
      END,
      NumToStr(CellValue())
   )
)
MAXL> SELECT {[TestMDXFormat]} ON AXIS(0),
   2>        {[Quebec], [Colorado]} ON AXIS(1)
   3>   FROM [Sample].[Basic];

 Axis-1              (TestMdxFormat)    
+-------------------+-------------------
 (Quebec)                    CAD1,476.00
 (Colorado)                  USD1,476.00

Mixed Measures

The HH:MM:SS example given above is, really, already a ‘mixed measure’ example. There are many other possibilities (especially in non-metric measurement systems), such as pounds and ounces:

MdxFormat(
   Concat(Left(NumToStr(Int(CellValue()/16)), Len(NumToStr(Int(CellValue()/16)))-3),
      Concat("lb",
         Concat(Left(NumToStr(Mod(CellValue(), 16)), 2), "oz")
      )   
   )
)
MAXL> SELECT {[TestMDXFormat]} ON AXIS(0),
   2>        {[Jan]} ON AXIS(1)
   3>   FROM [Sample].[Basic];

 Axis-1              (TestMdxFormat)    
+-------------------+-------------------
 (Jan)                           7lb11oz

Not-really-Format Format Strings

Aliases

This example replicates functionality available in almost every Essbase client where both a member name and an alias is displayed, but there are also options to further format the alias, concatenate the member name, or (as per the next example) parent name and so on. Additionally this approach allows you to display the alias in positions on the grid other than immediately to the right of the member name.

MdxFormat(
   [Year].CurrentMember.MEMBER_ALIAS
)
MAXL> SELECT {[TestMDXFormat]} ON AXIS(0),
   2>        {[Jan]} ON AXIS(1)
   3>   FROM [Sample].[Basic];

 Axis-1              (TestMdxFormat)
+-------------------+-------------------
 (Jan)                              Qtr1

Parents

Parent and child on the same row! A straightforward enough formula, but how many times has this question been asked on OTN?

MdxFormat(
   [Year].CurrentMember.Parent.MEMBER_NAME
)
MAXL> SELECT {[TestMDXFormat]} ON AXIS(0),
   2>        {[Jan]} ON AXIS(1)
   3>   FROM [Sample].[Basic];

 Axis-1              (TestMdxFormat)
+-------------------+-------------------
 (Jan)                              Qtr1

Datestamp

MdxFormat(
   FormatDate(Today(), "Mon dd yyyy")
)
MAXL> SELECT {[TestMDXFormat]} ON AXIS(0),
   2>        {[Jan]} ON AXIS(1)
   3>   FROM [Sample].[Basic];

 Axis-1              (TestMdxFormat)
+-------------------+-------------------
 (Jan)                       Jan 20 2016

Note that, although the Today() function returns a value in seconds it is rounded to a full date, and therefore this technique can’t be used to produce a timestamp.

Attribute Associations

Similar considerations apply to the variable unit example above using currency. A nice possibility here is that several attributes could be concatenated into one, and the usual “drill down / suppress missing” routine is not necessary to display the associated attribute(s).

MdxFormat(
   CASE 
      WHEN Contains([Product].CurrentMember, Attribute([Can])) THEN "Can "
      WHEN Contains([Product].CurrentMember, Attribute([Bottle])) THEN "Bottle "
      ELSE ""
   END
)
MAXL> SELECT {[TestMDXFormat]} ON AXIS(0),
   2>        {[100-10]} ON AXIS(1)
   3>   FROM [Sample].[Basic];

 Axis-1              (TestMdxFormat)    
+-------------------+-------------------
 (Cola)                             Can

Closing Thoughts

What really intrigues me about this functionality is that the whole Essbase MDX language is available for use, and the above only scratch the surface. Skimming the MDX function list I’m sure there are many more interesting possibilities. Please post yours in the comments!

9 thoughts on “Format Strings – Who Knew?

  1. G’day Tim,

    Been reading yours and Brian Marshall’s posts on this with interest – also something I’d never heard about (though, as always, Celvin posted about it almost 6 years ago…http://www.orahyplabs.com/2010/04/issue-with-hyperion-text-measure.html)

    Anyways – really basic question. Do you know if you can query on these in the same way you can query Smartlists? I’m thinking to do an IF statement on what the function returns?

    Would seem like a very sneaky way to bring MDX query functionality into BSO if that was the case!

    Cheers
    Pete

    • Sorry Pete, first for the late approval of this comment, and second for not understanding the question. Can you give me an example of how you ‘..query on these in the same way you can query Smartlists’?

      Do you mean writing an MDX query to say e.g. ‘Give me the Products where the FormatString value is “XYZ”‘?

      I have found (if this is relevant to your question) that you can’t ‘see’ any values within the context of the format string but that of the current intersection (returned by CellValue()).

      • Hey ,

        I think you’ve answered my question (and it was a reasonably dopey question to start with) – but to clarify what I mean.

        You can query a Smartlist function by querying the smartlist by name and then reference – ie: Smartlist: SL_EB_Validation and Member: Allocation_Not_100
        IF(Validation->”Entity_NA” = [[SL_EB_Validation.Allocation_Not_100]])

        I was wondering if you could do a similar thing querying the Format string. Say, you had a member (Format) with format string that just returned Positive or Negative on the number.

        Is it possible to write a function saying.
        IF(Format = “Positive”)
        perform x;
        ELSEIF (Format = “Negative”)
        Perform y;
        ENDIF

        Could be interesting as a method to ‘at run time’ defininition…where accounts go for AP or AR go or something as a simple example. The exciting bit is basically having an MDX basis IF Statement – even in a BSO Cube.

        Does that make sense? Or do I need to have a coffee first!

        Cheers
        P

        • Ah, I see! So you place MDX logic in the Format String, and then use it to drive a BSO calc script conditional. I’ll test – but I think the usefulness is limited by the restricted data value context, unless you want to do something metadata related like ‘Test for a UDA on the grandparent’ (for e.g.).

          • Yeah – I think it was a lot more exciting in my head to be honest.

            Still, be definitely interesting to see what performance was like. MDX is probably going to be significantly quicker to pull ‘parent’ details (particularly across dimensions) rather than trying to use PARENTVAL across multiple dimensions.

            Pretty specific use case though!

  2. Tim,
    I discussed format strings in at least two Kscops presentations over time (I guess you don’t come to my sessions). I’d not thought about the use case of member name and parent on the same row. That is cool

  3. Hi Tim,
    we are planning to get the format strings functionality in our production cubes.

    Does it impact any existing functionality?

    Can we enable the Typed measures in Planning/ ASO/ BSO cubes without any impact?

    Kumar

    • Hi Kumar, apologies for the slow reply – I was pretty busy at Kscope16 when you posted this!

      I would think that Planning outlines already have the property enabled to support Smart Lists. I can’t think of why there would be any impact for regular ASO / BSO cubes just from turning on Typed Measures, but I would never recommend making a change in Production without testing…

      Good luck!

      Tim

  4. Hey guys, I have a DATE typed measure in a BSO cube that is calculated. I’m trying to perform a second calc based on the month and year of that date, using the DP_MONTH and DP_YEAR function. The latter returns a number value for each, not a string. I need to convert these to individual strings so I can cross dim for Period and Year in my calc. Any ideas? This isn’t MDX or MAXL… it’s a calc script.

Leave a Reply to TimG Cancel reply

Your email address will not be published. Required fields are marked *