0

Currently if I run MDX queries I get the formatted values like 

123.456.789,01

What I want to achieve is that (period) is used as a decimal separator and (comma) as a thousands separator.

Is there any way to specify the separators/culture globally for all queries?

Dmitri Tsoy
  • 561
  • 6
  • 21

2 Answers2

0

FORMAT_STRING is the standard way of altering formats: https://msdn.microsoft.com/en-us/library/ms146084.aspx

It can be implemeted within an mdx script like this:

WITH [Measures].[aMeasureFormat] AS
  [Measures].[aMeasure]
, FORMAT_STRING = "#,##0.0"  
SELECT
  {} ON 0,
  [Measures].[aMeasureFormat] ON 1
FROM [yourCube];
whytheq
  • 34,466
  • 65
  • 172
  • 267
  • it is not what I am looking for: format_string only allows me to specify the placeholders for the separators, not the values of the separators themselves – Dmitri Tsoy Nov 07 '16 at 10:47
  • 1
    @DmitriTsoy I do think @whytheq is on the right track. You can set the format string `#,##0.00` in the measures in the cube designer and it will work as you want. No matter the locale of the users it will be formatted that way. I assume you are currently using a format string like `Standard` which I believe will differ based upon the locale of the user. – GregGalloway Nov 07 '16 at 14:22
  • @GregGalloway appreciate your follow up - I've never been involved with cube design (long story but a decision made by my company) so I don't know the cube designer. If you're interested in something more interesting then maybe this post: http://stackoverflow.com/questions/40401652/fast-dynamic-named-set-calculation?noredirect=1#comment68146788_40401652 .... my answer is effectively "feeling around in the dark" interested to know the user's best approach. – whytheq Nov 07 '16 at 14:33
  • Thank you for your comments guys. However it doesn't work this way. When you specify the format_string as FORMAT_STRING = "#,##0.0" you only define the the placeholders, where "," is ALWAYS a thousands separator and "." is ALWAYS a decimal separator. Then, when the mdx is executed, depending on your/server local settings you get "," or "." as thousands and "." or "," as decimal separator. So far I could not find the way on how to force certain local/combination. – Dmitri Tsoy Nov 07 '16 at 14:48
  • From MSDN (https://msdn.microsoft.com/en-us/library/ms146084.aspx): "." Represents a decimal placeholder that determines how many digits are displayed to the left and right of the decimal separator. "," Represents a thousand separator that separates thousands from hundreds within a number that has four or more places to the left of the decimal separator. – Dmitri Tsoy Nov 07 '16 at 14:56
  • @DmitriTsoy did you try changing your settings in the cube designer as suggested by Greg ? – whytheq Nov 07 '16 at 15:04
  • @DmitriTsoy ok - well if greg suggests "You can set the format string #,##0.00 in the measures in the cube designer and it will work as you want." then I suspect it is possible. – whytheq Nov 07 '16 at 15:35
0

too late but it is probably:, LANGUAGE=1034, FORMAT_STRING="$#,##0.00" will set apropriate code page.

double-beep
  • 5,031
  • 17
  • 33
  • 41
tsql
  • 11
  • 1