0

How can I get the format string for a currency value in analysis services?

When I run an MDX query to retrieve a measure with a type of Currency, the value is returned in the expected format e.g. €1.000,00. This is correct as my Cube's locale is set up to be Ireland.

Is there anyway for me to retrieve the currency format string?

So, for my Cube with it's locale set to Ireland, the currency format string would be something like "€#.###,##"

Is there a way that I can retrieve this information from Analysis Services?

I can run the following DMX query to get information about the measures and their formats, but this only shows the format as Currency for my currency measures:

select 
    MEASURE_UNIQUE_NAME, 
    MEASURE_NAME, 
    DEFAULT_FORMAT_STRING 
from 
    $system.MDSCHEMA_MEASURES

Any ideas?

Ed Spencer
  • 462
  • 1
  • 8
  • 21

1 Answers1

2

Be careful with using "Currency" as your format string. You don't want someone from Mexico to connect and suddenly see a different currency symbol without actual conversion to Pesos. http://blog.crossjoin.co.uk/2008/04/24/currency-formats-should-they-be-tied-to-language/

I would hardcode the FORMAT_STRING to "€#.###,##" unless you are actually converting currencies dynamically inside the cube.

If you want to retrieve the format string for a cell you can:

SELECT [Measures].[Your Measures] on COLUMNS
from [Your Cube]
CELL PROPERTIES FORMATTED_VALUE, VALUE, FORMAT_STRING

That query retrieves one cell and 3 properties. The FORMATTED_VALUE property takes the numeric value and applies the format string and returns €1.000,00. The value is just the number without any formatting like 1000. The FORMAT_STRING will be "€#.###,##".

GregGalloway
  • 11,355
  • 3
  • 16
  • 47
  • Thanks. So it sounds like if "Currency" is the format string, there is no real way of getting the format string. It looks like we're going to have to change these format strings, which sucks as it's a big change and will need lots of testing – Ed Spencer Nov 17 '15 at 15:45