Working with Strings in ASO

I needed a break from the Planning repository and recently had a colleague inquire about getting the name of a member as a string value in ASO.  This is a relatively simple process, but it occurred to me that when working with strings in Essbase, sometimes it helps to actually see what you are working with.  So how do we display text in Essbase based on an MDX formula?

First, we have to turn on typed measures.  In Essbase, the idea of typed measures are things like dates and text lists.  Typed measures also give us the ability to display formula-based text from Essbase in Smart View.  I specify Smart View because this functionality does not work in the Excel Add-In.  So let’s start by enabling typed measures:

1. Make sure you really want to do this, because there is no undo.
2. Open your outline (I’m using ASOsamp in this example) and go to the properties tab.
3. Change Typed measures enabled to True.
4. Click OK when prompted.

Now that we have typed measures enabled, we can try to display some text.  First let’s open the Measures dimension and add a member.  I chose ProductName as my member.  Once we have that member added, we can follow a few more steps and be ready for a retrieve:

1. Open the Properties of the newly created member (in my case, ProductName).
2. On the information tab, modify the Associate Format String to the following: MdxFormat([Products].CurrentMember.Member_Name)
3. Next we need to associate a formula for the member.  We do this because Essbase will not display a format string for a missing member.  I just used a literal zero to ensure we always have a value.
4. Save and restructure your cube.

We should be ready to see some text in Smart View:

Now that we have some text, we can start to manipulate that text and see things.  First let’s do something simple, like get the length of our member name:

Set our format string: MdxFormat(NumToStr(Len([Products].CurrentMember.Member_Name)))

And in Smart View:

Quick side-note…notice that we use the NumToStr function because the MdxFormat function requires a string parameter.

As one last sample, here’s how we use a text function like Left:

Set our format string:

MdxFormat(Left([Products].CurrentMember.Member_Name,5))

And in Smart View:

There we have it.  Text manipulation that you can see.  Once you get your formula just right, you can use the strings in other formulas.  You can use all of your regular functions and go nuts with strings.