Database Reference
In-Depth Information
right-click on the created member and in the Associate format string input the
following:
MdxFormat(IIF(CellValue()<0, "Sales Need to Increase", "Sales are
Great!"))
When a query is run, if the Sales value is less than zero then the message “Sales need
to Increase” will be displayed. otherwise, “Sales are great!” will be displayed. While this
is a trivial example, it is easy to see what an easy to use and powerful analytic feature
this can be. Instead of displaying a number, the report can display a more meaningful
textual string in its place (without altering the original number; this is just a format of the
number). Let's imagine that satisfaction rankings for customers are captured with a rank
value of 1 to 10, with 1 being low and 10 being high. Instead of displaying these as values,
we could convert the values to three ranges: highly Satisfied (8-10), neutral (4-7), and
Dissatisfied (1-3). now instead of the report displaying a series of numeric values, it can
display their textual equivalents. These can be tied to conditional formatting, and,
voila
,
I created a stoplight report in seconds. In this instance, in a BSo cube, the satisfaction
ranking could be averaged as it was aggregated and these formatted values will appear
at every level. Because an ASo cube cannot do anything but sum the values (no custom
aggregations), this is not as helpful and is something to be considered when using them.
The upper level results may not be what is desired and are difficult to control.
While format strings have great potential, there are some limitations to keep in mind:
• Format strings cannot be used across partitions.
• Shared members (including implied shared members) have the same format
string as the member they are based on.
• Essbase ignores invalid format strings and outlines can be saved with invalid
strings.
These apply to any member in the dimension marked as accounts as well as any member
in any other dimension that has a formula associated with it.
For those who have built Planning cubes, the idea of text measures is very similar
to functionality that already existed for years in the Planning Product in the form of
Smart Lists. At their essence, the text measure is nothing more than a metric value that
is converted to a mapped list value at query run-time. once the outline is enabled to use
typed measures, then text measures can be set up by following these steps:
1. In EAS, open the outline in outline Editor.
2. Select the third tab—text List manager.
3. Click new then double-click the entry in the text lists panel to give the list a name.
Note:
once a list is created with a name, the name cannot be changed. to change
the name of the list, the list must be deleted and recreated.
4. on the right-side panel, click the plus sign to add new members to the list.
5. Input the name (text that will display) and the ID (numeric value that Essbase
will store).
6. Save the list and restructure.
7. Create a member in the dimension tagged as accounts that the list will be
associated to.
8. right-click on the new member from Step #7 select Edit member Properties.
