I Love Labeled Cell Text

Sometimes, you find love in unexpected place. And sometimes, love finds you.

Yes, I know love is a powerful emotion and probably not often expressed of software features, but Labeled Cell Text is just that special. To give a quick overview, Labeled Cell Text is an enhancement to the original cell text functionality in HFM. The original functionality did what one would expect, allowing you to attach text comments and/or files to intersections in the database. These comments could then be include in FR reports, making them perfect for things like variance explanations or providing additional detail for a particular number. What you could not do, however distinguish between different kinds of cell text. If you included a text column in a report and used it to display the associated cell text for members on the report, you could end up with a column where the cell text for some rows are variance explanations, while the cell text in other rows might be a brief explanation of an unusual amount, a note to follow-up on an item the next month, etc. There was no way to create, for example, a variance analysis report and include only the cell text where users have entered variance explanations.

Enter Labeled Cell Text.

Labeled Cell Text allows you to create “categories” of cell text, so that when users go to enter the text, they are presented with a drop-down box containing the cell text categories defined by the administrator:

So now users can flag their cell text as being, for example, Variance Explanation, Follow-up Required, etc. A function has been added to FR giving you the ability to include only the type(s) of cell text you want to see displayed (the last parameter for the function specifies the desired cell text item):

<<LabeledCellText(Cur, Cur, B,1,”Open Date”)>>

But all of this is just the tip of the iceberg. What Oracle has done with labeled cell text goes beyond just storing variance explanations and other commentary; they have given HFM administrators the ability to store additional information (attributes) about metadata elements and include them on reports. For example, say you have an application that includes retail store locations in the Entity dimension and users would like to be able to display each store’s Open Date and Street Address. No problem. Add two members to the Labeled Cell Text dimension in your HFM application. And don’t worry about having to manually enter all of that additional information; labeled cell text values can be loaded and extracted just like data. The format for labeled cell text records is identical to the format for data records, with the exception that the last field specifies a cell text member and value, rather than data.

But wait, there’s more. In addition to what we’ve already described, you can get and set labeled cell text values in rules. One example of this would be to have a LCT member called Favorable/Unfavorable. During consolidations, a rule could calculate the monthly change in an account and, based on the associated account types and directions of the change, assign a value of either Favorable or Unfavorable to that cell text item. I realize there are other (perhaps better) ways of indicating whether a variance is favorable or not, but this example is just to illustrate the kind of functionality that is possible.

If you intend to make extensive use of labeled cell text to store attribute-type information that generally does not change over time, there is one design decision you’ll want consider early on. Let’s take the retail store “Open Date” item as an example – the store’s open date will not be changing from period to period, so we’d prefer to store this information in only one intersection, rather than having to copy it to additional periods so that it can be included on the next month’s reports. In order to avoid this issue, choose a member from each dimension that will be the “default” member for storing this attribute information. One option would be to utilize the [None] member for every dimension in which it is available and then choose a default member for other dimensions like Period and Scenario. Those intersections do not have to be included in the report in order to retrieve the associated cell text as the FR function discussed above has an optional parameter for member overrides:

<<LabeledCellText(Cur, [1], “Open Date”,”Custom1? = “[None]”, “Custom2? = “[None]”,”Year” = “2010”, “Scenario” = “Actual”, “ICP” = “[ICP None)>>

Another option to consider would be to create a dimension called “Flags” and include a member called “Cell Text”. No data would ever be loaded to that member; it would only be used for cell text. While not strictly necessary, this would give you the ability to easily extract all labeled cell text from the application.

Hopefully my strong feelings about labeled cell text now seem more justified, if arguably still extreme. I didn’t recognize the powerful nature of this feature until I started using it, so I wanted to make others aware that it can do more than you might think just by reading about it in a features list.