Friday, July 24, 2009

Display currency symbol based on country

Last week, I got to do a little bit research on displaying different currency symbols based on a country or currency member.

I learnt that SSAS 2005 supports Language () MDX function, which takes care of displaying measures database based on language (currency) settings that are setup in SCOPE() Expression.

1) Hard code language to locale mapping
Using BIDS, I've opened my cube and navigated to Calculation tab Switch to "Script View" and put the following expression

Optin #1: Using Scope expression, in this approach you can include multiple company members.

SCOPE ([Company].&[Germany]);
Language(This) = 2067;

SCOPE ([Company].&[UK]);
Language(This) = 2057;

Optin #1: Without using Scope expression

In this approach you can specify the locale to language mapping for only one company.
Language([Company].&[Germany]) = 2067;
Language([Company].&[UK]) = 2057;

Option 2: Dynamically provide mapping between language to locale
Option #1 can be made dynamic by creating an attribute on company dimension and storing the Locale ID.

Create an attribute on Company and assign the attribute to Language expression in Calc script

Language(This) = [Company].[Currency Locale ID];

Language(This) = [Company].Currentmember.Properties("Currency Locale ID");