How to get SSAS to return the correct currency format.

Reposted from Jens Vestergaard's blog with the authors permission.

Although setting the language and collation correctly, it’s not easy to see why a specific MDX would not return the right formatting, in regards to monetary, thousands seperator etc.
There is however a way of forcing the language, through a property available on the calculated measure. In the following example, I’ll show that even though the server settings are da-DK (Danish), the results are displayed as en-US, unless we provide a property value.

First, a screenshot of the server settings:

Then the query, that returns en-US formatting:

For this to work, we need to add: Language=1030 as property to the calculated member, then we get:

See more on FORMAT_STRING and LANGUAGE @ http://msdn.microsoft.com/en-us/library/cc879322.aspx

Jens has been working in the IT Industry since mid 90s. He started focusing in on the SQL Server Stack around 2003, mainly Analysis Services and the RDBMS. His blog is at http://www.t-sql.dk and he currently works for http://www.rehfeld.dk as a senior BI consultant.