Context sensitive dynamic formatting for measures

17.03.2009 Hilmar Buchta

SQL Server 2005 | SQL Server 2008

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

  1. with MEMBER [Measures].[Sales Amount Fmt] AS  [Measures].[Sales Amount],
  2. FORMAT_STRING = „$0,000″,
  3. NON_EMPTY_BEHAVIOR = { [Measures].[Sales Amount] }
  4. select  [Measures].[Sales Amount Fmt] on 0,
  5. [Product].[Subcategory].[Subcategory] on 1
  6. 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. However, you may overwrite the local using the language property as shown below:

  1. with MEMBER [Measures].[Sales Amount Fmt] AS  [Measures].[Sales Amount],
  2. FORMAT_STRING = „$0,000″,
  3. LANGUAGE=1031,
  4. NON_EMPTY_BEHAVIOR = { [Measures].[Sales Amount] }
  5. select  [Measures].[Sales Amount Fmt] on 0,
  6. [Product].[Subcategory].[Subcategory] on 1
  7. 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

  1. „$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

573 as $573
12,319 as $12K
9,312,131 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:

  2. iif([Measures].[Sales Amount]<1000,„$0“,
  3. iif([Measures].[Sales Amount]<1000000,„$0,K“,
  4. „$0,,M“
  5. ))

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:

  1. /*– Set language property for the Destination Currency cube dimension –*/
  2. Scope
  3. (
  4.    [Destination Currency].[Destination Currency Code].Members,
  5.    [Destination Currency].[Destination Currency].[Destination Currency].Members
  6. ) ;
  7.    Language( This ) =
  8.    [Destination Currency].[Destination Currency].Properties( „Locale“ ) ;
  9. 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:

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


Your email address will not be published. Required fields are marked *

Join #teamoraylispeople

Gestalte mit uns
die Welt der Daten