Hyperion Essbase 11.1.1.2 – Altering Measure Formats

If you had read my blog entry here, i would have shown how to go about creating textual measures in Essbase. Though that was a new feature introduced as part of the EPM 11 release, one related feature that i did not cover was the introduction of Format Strings. This probably is one of the very important features from an end user reporting standpoint. In a couple of implementations that i was involved in on 9.3.1, i faced a situation wherein we had to control the format of the data within Essbase itself. Unfortunately, since that was not possible in 9.3 release, custom formatting had to be applied in each and every downstream tools like Excel Add-in, HFR, BI EE etc.

Format strings help in controlling the output format of the measure values. For example, if we have an Expense account and a revenue account, the normal reporting requirement is to show the variance of the current quarter Actuals with the last quarter Actuals for both the type of accounts. For Revenue accounts, when current quarter is more than last quarter, then the variance would be positive else it would be negative. For Expense accounts, when the current quarter is less that last quarter, then the variance would be positive else it would be negative. Also, all negative values would have to be shown within braces and not as negative values.

Normally to implement the above requirement, we would have to write a custom formula to change the variance formula based on the Expense/Revenue UDA in an ASO cube(BSO cube has Expense/Revenue based reporting properties. But it is very limited in its usage). Also, adding braces to negative values would have to be done in the front end. Lets see how Format Strings can help us in achieving this requirement. Lets take the example of the Sample – Basic BSO cube. The strange aspect to the Format Strings is the fact that the implementation is done using MDX even in a BSO cube (should give a hint about the strategic direction :-)).

Lets first look at the outline. There are 2 measures Sales and COGS. Sales is a revenue measure and COGS is an expense measure.

Now lets create another member called Variance as shown below in the Scenario dimension.

This variance member is actually updated through a calculation script shown below (Format strings would not work on members having a member formula in BSO cubes)

SET CACHE HIGH;SET LOCKBLOCK HIGH;
CLEARDATA "Variance";
CALC DIM ("Year");
"Variance" = "Actual"->&CurrQtr - "Actual"->&LastQtr;
CALC DIM ("Market","Product");

Run the calc script after the data load. Following would be the variance values in Excel add-in.

As you see, since there was no formatting applied on the Variance values, both COGS and Sales are getting treated the same way i.e shown as positive numbers. Now, in order to apply format strings on this, lets first convert the outline into Text measure compatible outline.

After that, go to the member properties of the Variance member and add the MDX shown below

MdxFormat(IIF(Is([Measures].dimension.CurrentMember, [COGS]),
CASE WHEN cellvalue() > 0 then Concat(Concat("(", numtostr(CellValue())), ")" ) else numtostr(-cellvalue()) end,
CASE WHEN cellvalue() < 0 then Concat(Concat("(", numtostr(-CellValue())), ")" ) else numtostr(cellvalue()) end
))

Now if you go to Excel add-in, and retrieve the same data, you would notice that the accounting format would have got applied automatically.

This is an excellent feature. In fact using this alone, one can mimic textual measures that i explained before. In a future blog entry i would show how this can be leveraged from other downstream tools like HFR, BI EE etc.