Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Currency formats: should they be tied to language?

with 17 comments

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:
http://www.sqljunkies.com/WebLog/mosha/archive/2005/10/13/mdx_format_currency.aspx

and Vidas:
http://www.ssas-info.com/analysis-services-faq/27-mdx/244-how-change-currency-symbol-based-on-selected-currency-dimension-member

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:
100,001
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:
100.001
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:

currencylocale2

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
FROM         DimCurrency
WHERE     (CurrencyKey IN
                          (SELECT DISTINCT CurrencyKey
                            FROM          FactCurrencyRate))

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";
END SCOPE;

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:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=339913

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:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=218858

and here’s a forums thread on the subject:
http://forums.microsoft.com/msdn/showpost.aspx?postid=1488729&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=1

Hmmmmm…. I need to check if this has been fixed properly in Katmai.

Written by Chris Webb

April 24, 2008 at 7:31 pm

Posted in MDX

17 Responses

Subscribe to comments with RSS.

  1. Hi Chris, just dealing with that issue and trying to implement your solution in a SQL2012 cube.
    Is Excel 2010 able to pick up the Format_string defined through the SCOPE like that?
    When I query the cube using Management Studio I can see the symbol correctly, but Excel seems to ignore it.
    Thanks

    Francesco Quaratino

    February 24, 2014 at 5:34 pm

    • The trouble with Excel is that it doesn’t use the formatted values that SSAS generates: it takes the raw values and the format strings for each cell and then tries to convert the format strings into Excel formats. Mostly this works, but in this case I guess it didn’t. Altering your format string might make it work though, if you’re lucky.

      Chris Webb

      February 24, 2014 at 9:46 pm

  2. Thanks Chris for you prompt reply.
    I’ve found a tip this morning on this post: http://www.bidn.com/blogs/MikeDavis/ssis/1851/ssas-%E2%80%93-using-the-euro-or-pound-currency-symbol-in-a-measure-format-string

    Excel just needs a back slash to make it working.
    For instance:

    SCOPE ( [Currency].[Currency Code].Members);
    Format_String(This) = “\” + [Currency].[Symbol].MemberValue + “#,#.00″;
    END SCOPE;

    Cheers

    Francesco Quaratino

    February 25, 2014 at 11:22 am

  3. […] The language property of a cell controls things like the currency symbol displayed when you are using the “Currency” built-in format string and the thousands and decimal separators used. Whether Excel returns the language property or not is also linked to the Number Format checkbox in the Connection Properties dialog. I strongly recommend that you do not use the “Currency” format string and the Language property if you are working with multiple currencies, for reasons I outlined here. […]

  4. […] The language property of a cell controls things like the currency symbol displayed when you are using the “Currency” built-in format string and the thousands and decimal separators used. Whether Excel returns the language property or not is also linked to the Number Format checkbox in the Connection Properties dialog. I strongly recommend that you do not use the “Currency” format string and the Language property if you are working with multiple currencies, for reasons I outlined here. […]

  5. HI Chris,

    In your example you are using a fixed formatstring for the , and the . (period? dot?) . You also mention that this endpart of the Formatstring should be depending on the locale of the user. Do interpret this Correctly?

    You also say that it is fixed in 2008R2? How?

    And in your other blogpost about currency there is also a Local row for the reporting currency. It works great with the language property but what value should it be for the Local value, in case of a formatstring?

    Thnx,
    Hennie

    Hennie de Nooijer

    October 27, 2014 at 2:21 pm

    • The post says that the thousand and decimal separators that are shown depend on the locale of the server, not the client. I haven’t tested this recently though so it may have changed.

      For local currency, then I guess you would need to choose a format string based on the currency being displayed (which would depend on the currentmember on the Currency dimension).

      Chris Webb

      October 27, 2014 at 4:40 pm

  6. Thnx Chris,

    Appreciate your quick response.

    I was struggling with the Formatstring and therefore my interpretation of it may be not quite right. The Formatstring says where to put the deceimal separator. It does not say how to format on the client. Correct?

    Regional settings doesn’t seems to work in SSMS for presenting numbers.

    When i opened the cube in Excel, the decimal separator is , in dutch regional settings (Format) and in american regional settings . (dot) I’ve haven’t tested this in Reporting Services.

    My next question is about the LOCAL currency record. That is added by the currency wizard. How to display the right currency there? That depends on the current entered currency value

    The easiest way is to delete this but I would like to give it a try…

    Hennie de Nooijer

    October 28, 2014 at 8:53 am

    • The Format String property controls where to put the decimal and thousands separators AND it also does lots of other formatting things. I think the reason you see a difference between Excel and SSMS is that SSMS (like most client tools except Excel) takes the formatted values that SSAS returns – that’s to say it lets SSAS apply the formats to the measure values and then just displays the results. Excel on the other hand takes the unformatted values and the format string property that should be applied to those values, then turns the format string into an Excel format and does the formatting in Excel. SSRS doesn’t use any SSAS formatting at all by default and just gets the raw values from the cube.

      Regarding the local currency values, as I said you would have to have a local currency dimension in the cube (as well as a reporting currency dimension) and show the appropriate format based on the member selected in this dimension.

      Chris Webb

      October 29, 2014 at 9:37 pm

      • Well, I finally got it working. I’ve created a cube with currency conversion, added the currencysymbol in the ValueColumn of the Reporting Currency. Do not forget to add the \ for the symbol or else you get unpredicted results. I deleted the Local column. This is working:

        Format_String(This) = “\” + [ReportingCurrency].[ReportingCurrencyName].CurrentMember.MemberValue + “#,#.00″;

        men…men…

        Now, the next problem is that I want to show this in SSRS. That seems to be working but when I try to summarize the currency values in the report no formatting is done. I try to =SUM(Fields!Amount.FormattedValue) but that gives me errors.

        Any suggestions?

        Gr,
        Hennie

        Hennie de Nooijer

        October 30, 2014 at 11:16 am

      • Try using the Aggregate() function in SSRS to do your subtotals, not Sum() – that might work.

        Chris Webb

        October 30, 2014 at 11:36 am

      • Aggregate() function in SSRS doesn’t show anything or perhaps I have to do more?

        Hennie de Nooijer

        October 30, 2014 at 12:46 pm

      • Are you using custom MDX in your report, or is the MDX generated by the query builder?

        Chris Webb

        October 30, 2014 at 12:49 pm

      • This is the Query:

        SELECT
        NON EMPTY { [Measures].[Amount] } ON COLUMNS,
        NON EMPTY { ([ReportingCurrency].[ReportingCurrencyName].[ReportingCurrencyName].ALLMEMBERS * [SourceCurrency].[SourceCurrencyName].[SourceCurrencyName].ALLMEMBERS ) } ON ROWS
        FROM [MultiCurrencyV5] –CELL PROPERTIES VALUE, FORMAT_STRING

        I’ve commented CELL PROPERTIES VALUE, FORMAT_STRING

        Hennie de Nooijer

        October 30, 2014 at 12:59 pm

      • If you’re using custom MDX then you’ll have to alter it to get the subtotals back. This post should help: http://blog.datainspirations.com/2007/04/04/showing-server-aggregations-in-reporting-services/comment-page-1/

        Chris Webb

        October 30, 2014 at 1:02 pm

      • Hi Chris,

        Played around with the server aggregates and I don’t think it’s working because the AGGREGATE() function in SSRS works only with .value and not with .Formattedvalue. Using Aggregates is understandable when you’re using percentages. The aggregation is done on the cube and you can use this calculation instead of summing in the reporting resulting in incorrect results.

        In Excel it’s working so I’m thinking that I will take a look at profiler to look behind…

        Greetz,
        Hennie

        Hennie de Nooijer

        October 31, 2014 at 9:04 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,310 other followers

%d bloggers like this: