Currency formats: should they be tied to language?
One of the most commonly asked questions on the AS MSDN Forum is how to format measures that contain values in different currencies with the correct currency symbol. I’ve never blogged about this because a lot of people have already written up the solution in detail, for example Mosha:
However I was thinking about this recently and in my opinion there’s a big problem with using the Language property to do this. And that is that when you set the Language of a cell, you not only change the currency symbol but you also change other ways that the number is formatted, for example the symbols used as thousands separators and decimal separators. In the US and UK of course, we use full stops (I think they’re called ‘periods’ in the US?) as decimal separators and commas as thousands separators, but in continental Europe the roles are reversed. So the value:
would be interpreted as one hundred thousand and one in the UK, but one hundred point zero zero one in Germany, say, and the value:
would be interpreted in the opposite way. Borrowing one of the screenshots from Vidas’s post you can see how the Language property respects these conventions:
So you can see what the potential problem is – what happens if you have values in Euros, USDs and GBPs in your cube? However much you educate your users you can guarantee that someone at some time is going to get confused or worse not realise what’s going on and interpret the values incorrectly.
What’s the alternative then? I think using Format_String has to be the way to go. If you alter Vidas’s example so that instead of locale ids you put currency symbols inside the currency dimension named query, for example:
SELECT CurrencyKey, CurrencyAlternateKey, CurrencyName,
CASE CurrencyAlternateKey WHEN ‘GBP’ THEN ‘£’ WHEN ‘EUR’ THEN ‘€’ WHEN ‘JPY’ THEN ‘¥’ WHEN ‘USD ‘ THEN ‘$ ‘ END AS LocaleID
WHERE (CurrencyKey IN
(SELECT DISTINCT CurrencyKey
and then change his MDX assignment to be something like:
SCOPE ([Destination Currency].[Destination Currency].[Destination Currency].Members);
Format_String(This) = [Destination Currency].[Destination Currency].[Symbol].MemberValue + "#,#.00";
Then you get the desired result. However, one thing I did notice when I was experimenting with this is that if you try to use more than one character for your symbol (for example you might want to use CHF for Swiss Francs) you sometimes get the following error:
#Error The following system error occurred: Out of present range. .
Not good. Here’s the bug logged on Connect:
Following on from all this, it also makes sense that users connecting from different locales automatically see numbers (but not currency symbols) formatted in the convention of their locale. So a German person might connect to the cube and see Euros with a € and USDs with a $, but see commas used as decimal separators, whereas a user in the UK would still see €s and $s with the correct symbol but full stops used as decimal separators. Now AS2K I seem to remember used to be able to handle this perfectly well – it could display the appropriate decimal separator and thousand separator depending on the client locale. However AS2005 RTM had a problem in that it worked ok for calculated measures but not for real measures; this was ‘fixed’ in SP2 so both calculated measures and real measures always got displayed in the locale of the server. American software, eh? And to think that so many members of the dev team are from Europe too. Here’s the Connect:
and here’s a forums thread on the subject:
Hmmmmm…. I need to check if this has been fixed properly in Katmai.