FORMAT STRING is not applied to SSAS calculated member nor measures

2
votes

I am trying to format a calculated members and measures using "Currency" format string, however this property is not being applied to the value.

I've checked this question related to this issue but that is not my case. For my case cube browser and Excel still do not work.

Why is not format string property being applied?

This issue is only related to FORMAT_STRING property since I set color foreground property to the calculated member and it is shown in Excel client, "currency" property is not.

enter image description here

Below the script for calculated member creation.

CREATE MEMBER CURRENTCUBE.[Measures].GananciaBrutaTotal
 AS [Measures].[Ganancia Bruta - Fact Ventas]-[Measures].[Ganancia Bruta], 
FORMAT_STRING = "Currency", 
FORE_COLOR = 4227327 /*R=255, G=128, B=64*/ , 
LANGUAGE = 9226, //9226
NON_EMPTY_BEHAVIOR = { [Ganancia Bruta], [Ganancia Bruta - Fact Ventas] }, 
VISIBLE = 1  ;

Note language property set to 9226 Locale ID for Spanish Colombia

This is the cube language setting:

enter image description here

Client machine language setting (Spanish Colombia):

enter image description here

Server machine language setting (Spanish Colombia):

enter image description here

Excel language setting:

enter image description here

EDIT: In response to Peter. I had forgot mention that usage Excel connection properties were set for retrieve data format from server.

enter image description here

1
sql-server-2012ssasmdxcubeformat-string

1 Answers

1
votes

Double check your data connection properties Usage tab in EXCEL. There is a check box for OLAP Server formatting "Retrieve the following formats from the server when using this connection." You may need to check "Number Format" for EXCEL to honor the formatting set by the server.

According this this thread, Currency may be ignored by EXCEL and you might have to specify an actual format:

MSDN Thread