Essbase Format Strings in Smart View, The Excel Add-In, and Financial Reports
Last year I had a post about working with strings in Essbase. To illustrate how to work with strings, I used a features called Essbase Format Strings. This basically allows us to return a value other than that of the intersection based on MDX logic. For instance, if a number is great than zero, return the text Positive or if it less than zero, return Negative. This allows us to show text values inside of an Essbase retrieve.
Today we will dive a little bit deeper into how this works in each of our popular interfaces with Essbase: Smart View, the Excel Add-In, and Financial Reports. First we’ll look at the classic Excel Add-In. This is pretty simple in that we really have no control over what we show. The Excel Add-In will only show the value of the format string:
As we can see, the value of the format string is displayed. There are no settings to turn that off in the Excel Add-In (that I can find).
Next up, we have Smart View. Smart View is a little bit more aware of the concept of Essbase Format Strings. It actually has a setting that we can turn on and off. Using the default settings, we get this:
This looks nearly identical to the Excel Add-In retrieve. Now let’s look in our Smart View options:
Here we see an option to enable and disable our format strings. So what happens when we turn this setting off?
Now we see the 0 that we configured as the value to be returned by our calculated member, instead of the format string. So what does this look like in Financial Reports?
We’re back to the format string showing! And like the Excel Add-In, Financial Reports just displays the value of the format string and there’s no way to turn this off. Hopefully this provides a little clarity as to how Format Strings work and how we can display them in our various popular interfaces.