More about context sensitive formatting

In the last post I wrote about context sensitive formatting. The goal was to format values differently depending on their digits, so that for example. 532 formats to 532 but 143,134 formats to 143K.

When you try the formatting from my post with Excel you’ll find that Excel has some problems interpreting the format string. The reason is that Excel simply gets confused by the characters (e.g. M). So you might better want to escape those characters in the format-string.

Furthermore you could also think of changing the color dynamically too. Maybe you want to format negative values in red.

Here is the completed cube script statement to create the measure in an Excel compliant way:


Create Member CurrentCube.[Measures].[Sales Amount Fmt]
AS  [Measures].[Sales Amount],
    iif(vba!abs([Measures].[Sales Amount])<1000,"\$0",
    iif(vba!abs([Measures].[Sales Amount])<1000000,"\$0,\K",
  FORE_COLOR=iif([Measures].[Sales Amount]<0,RGB(255,0,0),RGB(0,0,0)),
NON_EMPTY_BEHAVIOR = { [Measures].[Sales Amount] }


In Excel this may result in the following visualization (since all values are positive the FORE_COLOR has no effect in this example):

This entry was posted in Allgemein. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s