Search This Blog

Sunday, November 15, 2009

How to define an Excel compliant format strings for a cube

SQL Server 2005 | 2008

Cube defined formats are not necessarily displayed properly in Excel 2007. Sometimes the format seems to be ignored completely although it is working fine in the cube browser.

For a simple example I created a cube based in the following source table which acts as dimension and fact at the same time:

UnitID FormatString SampleValue
1 #,##0.00 12345.678
2 #,##0.00€ 12345.678
3 #,##0.00\€ 12345.678
4 #,##0.00 EUR 12345.678
5 #,##0.00 \E\U\R 12345.678
6 #,##0.00" EUR" 12345.678
7 #,##0.00"€" 12345.678

The ideas is simply to try out different format strings. I used the first two columns for my sample dimension and the first and the last column for my fact table giving a very simple cube

image_thumb9

In order to use the format string to format the sample value, I used this simple cube script:

scope ([Dim Unit].[Unit Format].[Unit Format]);
format_string(this) = [Dim Unit].[Unit Format].currentmember.name;
end scope;

Now, let’s browse our cube using the cube browser:

image_thumb2

As you can see, every of our approaches for formatting the value worked as expected. Now let’s take a look at the same cube using Excel 2007:

image_thumb8

As you can see, not all format strings are also understood by Excel. Simply using € or writing EUR confuses Excel – the value is displayed without any format. And although the cube browser understands even these formats, it’s dangerous to use unescaped characters in your format string as many of them have a meaning (like HH for the hour in a 24 hour representation). So it’s always a good idea to escape the characters. As you can see, both the backspace and the quotation marks work fine with Excel so it’s up to you what you prefer (or how other clients interpret the format string…).

4 comments:

  1. Hey Hilmar

    Thanks for the code example you provided... it's very useful :)

    YL

    ReplyDelete
  2. What if the custom formats are exceeding more than a certain Limit say 200. Excel gives an error message that 'No More custom formats can be added' when try to add many custom formats. If i have more than 300 Units in my Dimension then how do i display it in excel.

    ReplyDelete
  3. Never experienced this error with Excel 2007 or 2010. Which version of Excel are you using?

    ReplyDelete
  4. Excel 2007.. We too have a requirement of Unit similar to what displayed above.. But we have more than 220 Units.. so for 'All' selection Excel Closes with an Message that excel stopped working.

    ReplyDelete