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
6 thoughts on “How to get SSAS to return the correct currency format.”
It’s probably just easier to set the currency symbol you want to use in the format string you’re using for your measure, eg ‘£#,#.00’. If you rely on the ‘Currency’ format and setting the language then you end up changing the thousands and decimal separator as well, and that’s not a good thing if you have multiple currencies in the cube: http://cwebbbi.wordpress.com/2008/04/24/currency-formats-should-they-be-tied-to-language/
I appreciate your feedback.
This particular example is to illustrate that it is possible to actually force a locale through. I’ve seen a lot of reports where the developer expected a different formatting based on the server settings, so from my point of view this was a re-occurring issue.
Of course, as you point out, this fails to support multiple locales in the cube. And make no mistake, I think your point is very accurate and when applicable, should be applied.
The deal behind the MDX construct in this example is, that a single MDX is injected with meta data about how the report, in this case every KPI of a dashboard, should be not only formatted, but also what KPI is to be displayed, what slicers are to be applied and so forth. So really, the solution runs with one currency only, but the MDX doesn’t know until runtime if the formatting is ‘Currency’ or ‘#.###.##’ or some third predefined formatting mask. The locale identifier injected is actually, now that I think it through once more, not driven by meta data – this might be something that needs to be changed (that way the data steward will be able to control what locale identifier is to be used on any given KPI).
Hello, that such an interesting article. What is the most advantage of using this approach than others?
thank you so much for the tutorial step.
Hi, I did some further investigation on Format-Strings regarding the thousands-separator on my blog at: https://nicefacts.de/2020/09/29/analysis-services-format-string-and-the-thousands-separator/