Search This Blog

Saturday, March 21, 2009

More about context sensitive formatting

In my 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):


Tuesday, March 17, 2009

Context sensitive dynamic formatting for measures

It's commonly known that you can provide a format string for measures like in the following example:

with MEMBER [Measures].[Sales Amount Fmt] AS [Measures].[Sales Amount],
FORMAT_STRING = "$0,000",
NON_EMPTY_BEHAVIOR = { [Measures].[Sales Amount] }

select [Measures].[Sales Amount Fmt] on 0,
[Product].[Subcategory].[Subcategory] on 1
from [Adventure Works]

The important part of the example above is the FORMAT_STRING property. Here, it formats our measure sales amount as a dollar value without positions after the decimal point and with a separator for thousands.

The result looks like this:


It should be noted that the locale (i.e. using the comma as separator character) is derived from certain system settings (see for details). However, you may overwrite the local using the language property as shown below:

with MEMBER [Measures].[Sales Amount Fmt] AS [Measures].[Sales Amount],
FORMAT_STRING = "$0,000",
NON_EMPTY_BEHAVIOR = { [Measures].[Sales Amount] }

select [Measures].[Sales Amount Fmt] on 0,
[Product].[Subcategory].[Subcategory] on 1
from [Adventure Works]

The only difference compared to the query above is the LANGUAGE property. Being set to 1031 for Germany the result displays a point as separator character:


To get back to the topic of this post, it's interesting that the FORMAT_STRING property doesn't have to be a constant. You might play around with simple expressions like "$0,0"+"00" but you have full support for MDX expressions here.

Let's assume you want to format your values differently depending on their size, you can easily do so with an expression. For example, let's assume you want to display dollar values like this


as $573


as $12K


as $9M

This can be done with the following FORMAT_STRING property which can be used in your cube script or in your MDX query:

iif([Measures].[Sales Amount]<1000,"$0",
iif([Measures].[Sales Amount]<1000000,"$0,K",

Using this expression for the FORMAT_STRING property, the result of the query above looks like this (I also kept the originally formatted value of the example above to make it easier to compare the results):


As you can see, the values in the example above show as Thousand or Million Dollars. I think that this looks a little bit confusing and I would prefer showing all values at the same scale but at least you can use this feature to set different formatting options.

Furthermore you could think of displaying the correct unit (like meters, square meters, kilogram) with the values depending on a dimension attribute or you could display the right currency code depending on the currency of the transaction. In the Adventure Works cube for example, you can find the following cube script, which sets the language property for the destination currency dimension:

/*-- Set language property for the Destination Currency cube dimension --*/
[Destination Currency].[Destination Currency Code].Members,
[Destination Currency].[Destination Currency].[Destination Currency].Members

) ;

Language( This ) =
[Destination Currency].[Destination Currency].Properties( "Locale" ) ;

End Scope ;

In this case, the locale is stored as a dimension attribute (for example 1033 for US, see for an overview of the locale codes). The script results in the correct visualization on the currency dimension like shown in the following query:

select [Measures].[Sales Amount] on 0,
[Destination Currency].[Destination Currency].[Destination Currency] on 1
from [Adventure Works]