I was playing around with the new Text measure in Essbase and found out a bug. I was informed that it’ll get fixed in 11.1.2 release….so i’m waiting…..
For those who don’t know about the Text measure.
“Essbase was only capable of handling numbers not strings…” However Planning 9.3 versions had Text, Date measure where you can enter descriptions like “not satisfied” into Essbase, but the retrieve will still show them as numbers.
So with new Hyperion v11 release, now you can handle those.
You have a Text list manager now in EAS console, where you can define Text lists, it is similar to Smart list of Planning. Or you can use format strings to show strings instead of numbers. More on this later.
So I created a members called Units and tagged it as Text member.
It has simple formula which says if uda is thousand then units is 1000, and if it is million then units is 1000000.
Then I created a text list and mapped Units to that text list.
I did a retrieve and found at some places i still have numbers (1000 in the excel sheet) and some had text (Million in excel sheet). I had to do a lot of fact findings and found that it is working only for level 0 members in the row!!!!!
Then again lots of tests……and different combinations and I was sure that this is working for Sample Basic, even if there is higher level in row. So I tried a different approach.
Why not play around with the Format strings.
So I made Units as numeric and associated a format string to it.
mdxformat(CASE
WHEN CellValue() = 1000 THEN “Thousand”
WHEN CellValue() = 1000000 THEN “Million”
WHEN CellValue() = 100 THEN “Hundred”
ELSE “No Unit”
END
)
Still the result is the same. Again lot of testings and finally i found out where the problem is.
The ROW dimension that i’m using in EXCEL is dense dimension, if i change that to SPARSE then it works!!!!!! else it is not…..but I cant change it just to get this new feature, so we created an alias table for units and solved the issue. So the TEXT measure wont work always….and i’m hoping that this gets fixed in 11.1.2
Looking great in your work. Congrats!