SQL Server 2005 | SQL Server 2008
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],
-
FORMAT_STRING =
-
iif(vba!abs([Measures].[Sales Amount])<1000,„\$0“,
-
iif(vba!abs([Measures].[Sales Amount])<1000000,„\$0,\K“,
-
„\$0,,\M“
-
)),
-
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):
Kommentare (0)