SQL Server 2005 | SQL Server 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
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:
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:
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…).
Kommentare (0)