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:
- Make sure you really want to do this, because there is no undo.
- Open your outline (I’m using ASOsamp in this example) and go to the properties tab.
- Change Typed measures enabled to True.
- 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:
- Open the Properties of the newly created member (in my case, ProductName).
- On the information tab, modify the Associate Format String to the following: MdxFormat([Products].CurrentMember.Member_Name)
- 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.
- 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)))
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))
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.
Gary
January 11, 2016 - 8:10 am
Brian,
Your example above to generate a 0 does not match the screen capture and therefore does not result in a 0 in SV.
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)
Please advise,
Gary
Brian Marshall
January 11, 2016 - 10:17 pm
Hi Gary,
The example above will only a generate a 0 under two circumstances. First, if you use the classic Excel Add-In, you will see a 0, as Essbase Format Strings are not supported by the classic Add-In. Second, if you have disabled Essbase Format Strings in Smart View, it will display the actual value of the calculation. That would of course be 0 in this example. I’ll probably try to make another post soon to show this.
Let me know if this answers your question. I just went back and tested it one more time to make sure it works.
-Brian
Kommu
June 27, 2016 - 1:59 pm
Hi Brian,
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
Brian Marshall
June 29, 2016 - 1:45 am
In general it shouldn’t restrict functionality. Typed measures shouldn’t have any massive impact. Having said that, we’ve experienced certain performance impacts when you have the option enabled in Smart View. Specifically, when you have a large suppress missing retrieve, it can make performance more sluggish than usual. If you experience this, you may have to disable the option in Smart View in some instances. Clear as mud?
Kumar
May 8, 2017 - 4:54 pm
Hi Brian,
Is it possible to retrieve the associated attributes for the current member. Ex in Sample.Basic we have Caffeinated: True, Intro Date:03-25-1996, Ounces:12, Pkg Type:Can associated with ‘100-10’ member….Is it possible to retrieve them…
Thanks
Brian Marshall
June 11, 2017 - 12:13 am
Hi Kumar,
Yes you can. In ASO, you would use [Dimension].CurrentMember.[AttributeName]. This returns a string value, so handle accordingly. For BSO, you would use @AttributeSVal(“AttributeName”). This only works on the current member and cannot be cross-joined, but it will return the string. Is that what you were looking for, albeit super-late…
-Brian
Kumar
July 17, 2017 - 1:07 pm
Hi Brian,
Is it possible to have Typed measures in Planning? we don’t have the following options in planning.
1. Enable Typed measures on the outline
2. Associate format string – to add MDX
If we enable Typed measures on the outline, and refresh the DB from planning the outline is refreshed to its earlier state.(enable Typed measures is set to False)
We don’t have an option to set the MDX in planning…