Currency Conversion: Formatting Symbols in Tabular

This article on currency conversion was written on my old blog in August 2006.  It still applies today for Multidimensional in 2012.  Update August 17 2013: see revised version for SSAS 2012.  One of the items covered is how to format the currencies so they look like the following rather than all use one currency’s symbol.

I created a couple of extra columns in my currency dimension table.  My examples use the LCID column for multidimensional and FormatString for tabular.

You can get the LCIDs from here.  An easy way to get the format strings is to select Format=Currency and set the currency symbol in the properties window of SSDT.  This internally sets the FormatString property to the custom string.

Multidimensional

Set the currency attribute’s ValueColumn property to the LCID column.

And then the MemberValue function in MDX script to set the Language property.

Language(([Currency].[Currency].Members, [Measures].[Internet Sales Amount])) = [Currency].[Currency].CurrentMember.MemberValue;

Tabular

I couldn’t find a way to use LCID in tabular, but we can use the FORMAT function and pass in the FormatString value from the currency table.

DEFINE
MEASURE 'Internet Sales'[Internet Sales] =
    SUMX
    (
        'Currency Rate'
        , CALCULATE( Sum('Internet Sales'[Sales Amount]) / Sum('Currency Rate'[End of Day Rate]) )
    )
MEASURE 'Internet Sales'[Internet Sales FORMATTED] =
    FORMAT
    (
        [Internet Sales]
        , VALUES('Currency'[Format String])
    )
MEASURE 'Internet Sales'[Internet Sales ALL MEMBER DOLLARS] =
    IF
    (
        HASONEVALUE('Currency'[Currency Name])
        , [Internet Sales FORMATTED]
        , [Internet Sales FORMATTED]('Currency'[Currency Name] = "US Dollar")
    )
EVALUATE
ADDCOLUMNS
(
    FILTER
    (
        VALUES('Date'[Calendar Year])
        , [Internet Sales] > 0
    )
    ,"US Dollar", [Internet Sales ALL MEMBER DOLLARS]( 'Currency'[Currency Name] = "US Dollar" )
    ,"United Kingdom Pound", [Internet Sales ALL MEMBER DOLLARS]( 'Currency'[Currency Name] = "United Kingdom Pound" )
    ,"Brazilian Real", [Internet Sales ALL MEMBER DOLLARS]( 'Currency'[Currency Name] = "Brazilian Real" )
    ,"Saudi Riyal", [Internet Sales ALL MEMBER DOLLARS]( 'Currency'[Currency Name] = "Saudi Riyal" )
    ,"ALL MEMBER", [Internet Sales ALL MEMBER DOLLARS]
)
ORDER BY 'Date'[Calendar Year]

In multidimensional, we can use the following properties to hide the all member and choose the default currency – summing multiple currencies obviously doesn’t make sense.

  • IsAggregatable=False removes the all member
  • DefaultMember set to the desired default currency

Given that tabular doesn’t have anything equivalent to these features at this time, we need to check if we are at the all level of the currency dimension using HASONEVALUE(‘Currency'[Currency Name]).  The above example sets the all member to US Dollars.  This also covers some clever clogs selecting a multi-value filter on currency.

Advertisements