First, Essbase does support formatted values in Essbase 11. You can see in this screenshot from EAS, the format string is a property of the member.
The format string is in MDX format and there seems to be no valid examples in the docs of how to actually do a format. Thanks to the beauty of the internet and the hard work of Venkat at RittmanMead Consulting, I found an example on their blog. In the screenshot below, you will see the result of the formatted value:
Under the covers, there is a setting at the API level that determines if the formatted values are retrieved from the database. Uses of Smart View control this setting using the 'Format String' option in the Options dialog.The same setting also governs whether Date and Text Measures are returned as either the formatted values or as numbers (which are what are stored in the database). Here is a screenshot of how the Java API returns formatted values with both the formatted values returned and the the formatted values not returned:
The value circled in yellow is a formatted value; note that the number below is the numeric value (which can also be returned in the same retrieve at the API level). The value circled in green is a Date measure; I don't think my date values are valid though. It seems limited that the earliest date supported is January 1, 1970 (and I think I used Excel dates to fill the cell which starts at January 1, 1900.
The values circled in red and blue are Text Measures. Text Measures get their values from Smart Lists. Here is the EAS editor showing my simple Smart List:
OK, so it says 'Text Lists' in the dialog, but if you look at the API level, you see Smart Lists:
Now you probably know more about Formatted Values, Text and Date Measures than you ever cared to know.
Posts
6 comments:
Tim,
>>It seems limited that the earliest date supported is January 1, 1970
^^^ This is an artifact of adopting Unix time.
See: http://en.wikipedia.org/wiki/Unix_time
Regards,
Cameron Lackpour
Hi Cameron,
I wasn't questioning where the 'first' date came from but rather the usefulness of having 1/1/1970 as the 'earliest' date you could analyze using a date measure.
Tim
If I remember correctly, the last date you can posrt is in 2043 (or around there somewhere). Again a limiting factor. its Y2K all over again for Essbase. The way companies upgrade, there will still be some places still on Versio 11 by then :). I find the formatted strings to be much more useful than the text lists as they can contain ranges of numbers. For instance if the number is between 0 and 3.3 it is low, 3.3 and 6.6 it is medium and above that high. Much more flecible than the text lists.
I also find it interesting that you beat me to this (and went into greater detail tha I would have) I wasw jsut putting together an entry on my blog for this.
Hi Glenn,
Interesting points.. I would love to see your blog post showing the formatted text/number ranges; I hadn't thought of using them that way.
Tim
Hi Tim,
I have to update the date type records for Planning application.
The way you have updated was workingly perfectly for essbase BSO application.
Please help me out that what could be the right approach for Planing application.
Thanks,
Harsh
I don't use Hyperion Planning directly. You should post your question on the OTN forums.
Tim
Post a Comment