More about context sensitive formatting

Technical Value

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:

  1. Create Member CurrentCube.[Measures].[Sales Amount Fmt]      
  2. AS  [Measures].[Sales Amount],      
  3.   FORMAT_STRING =      
  4.     iif(vba!abs([Measures].[Sales Amount])<1000,"\$0",      
  5.     iif(vba!abs([Measures].[Sales Amount])<1000000,"\$0,\K",      
  6.      "\$0,,\M"      
  7.     )),      
  8.   FORE_COLOR=iif([Measures].[Sales Amount]<0,RGB(255,0,0),RGB(0,0,0)),      
  9. NON_EMPTY_BEHAVIOR = { [Measures].[Sales Amount] }      
  10. ;

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


Neuen Kommentar schreiben

Der Inhalt dieses Feldes wird nicht öffentlich zugänglich angezeigt.


  • Keine HTML-Tags erlaubt.
  • HTML - Zeilenumbrüche und Absätze werden automatisch erzeugt.
  • Web page addresses and email addresses turn into links automatically.
Teilen auf

Newsletter Anmeldung

Abonnieren Sie unseren Newsletter!
Lassen Sie sich regelmäßig über alle Neuigkeiten rundum ORAYLIS und die BI- & Big-Data-Branche informieren.

Jetzt anmelden