format issue

I've created a new calculate member in (SQL Server Business Intelligence Studio 2005) with the following format string: "#,#0.00". This measure is displayed like this: 22,250.22

Then I created a Measure in the Cube structure menue and used the same format string:"#,#0.00" and this measure is displayed like this:0,66 -->

Comma and decimal point are changed in the display of the measures!!!!

Can anyone help me with this? I just like every measure to use a decimal point for the decimal place or the other way round. But I want every measure to be displayed in the same format!

Thanks a lot!

We've had a problem like that too. As far as I can remember, it is a known bug where calculated members are formatted according to the server locale instead of the client locale.

Verify which is the account that your SSAS service is running under, and then verify the locale of that account.

By the way, we're also having problems when the calculated member is a division, and the result is close to zero, the format switches to scientific notation (like 3.45E-7) instead of showing 0.00.

|||But I get different results from the same SSAS installation using the same format ?!|||If I understood your question correctly you get the expected format for regular measures and the wrong format for calculated measures. The bug only applies to calculated measures.|||Have you solved the problem or do you know a workaround?|||I don't know a workaround. We are just lucky that all users across the world accept a US locale, so we set the account of SSAS to a US locale. This way regular measures and calculated measures show in the same format. I assume this will be fixed in SP2.|||

Here is a link to a BLOG that might solve your problem:http://sqljunkies.com/WebLog/mosha/archive/2005/10/13/mdx_format_currency.aspx

I have seen the same problem because i work in a scandinavian country. If I have a column that indicates the currency I simply do like this (### ### ### ###.##).

Note that you will have to use space as thousand separator.

With my language settings SSAS2005 thinks that a (,) is a thousand separator and a (.) is always a decimal indicator.

This is quite stupid but it is the way it works presently in SSAS2005. It will also cascade as a problem in Excel because if you do not get it right all measures will be converted to strings in Excel, if you use language settings different from US-settings.

HTH

Thomas Ivarsson

|||(### ### ### ###.##) thanks for this hint but unfortunately it doesn't work either. I used this format for both measures but in one , appeared as the comma seperator and in the other the decimal point appeared.

Is there an official site where this bug is admited by Microsoft?

Thanks!
|||

OK. What is your regional/national settings for the O/S?

What thousand separator and what decimal sign are you using with these settings? look at regional settings in the control panel.

Does this problem appear in the BI-Dev Studion Browser or in a client, like Excel?

I have found it to work on clients with swedish settings( space as thousant separator and decimal(,) as decimal sign). In USA the dot is used as deciaml sign and decimal as thousand separator.

I recommend you to have a look at Moshas Blog entry of how to solve this in script.

Regards

Thomas Ivarsson

|||Regional Language settings are swedish.
Nummer is formatted like this: 123 456 789,00

It appears in the BI-Dev studio and in the Management studio.

I created a measure in the Cube structure with the following format: ### ### ###.## -->result: 22 387.31

and I created a measure in the calculation tab with the following format: "### ### ###.##" --> result: ,66

I have no idea how to solve this. Is there an official Microsoft page where this bug is recognized?

Any more suggestions?|||

Hello again. So we are both swedish.

I have tried the same scenario you are describing but I do not get the same result for the calculated member.

Both "### ### ###.##" in a measure and in a calculated measure returns 000 000 000.00, with my swedish regional settings in the O/S. Check the sign for thousand separator and decimal in regional settings.

This is not correct because we use (,) as decimal sign but it will secure that client applications will fomat values in the correct way. Else, values can be converted to text in Excel.

I have heard som "talk" that the currency format in SSAS2005 will automatically turn in to the currency settings in your O/S. This have not happened yet om my machine.

My assumption is that, standard format settings, have to do with the NET-framework on your machine.

Have you tried the script, for formatting, in Moshas Blog that I have pointed at?

Regards

Thomas Ivarsson

|||

To reproduce the problem, you need to have the client in one locale, and the account under which the SSAS service is running in another locale. In case the service is running under a non domain user, you need to check the "Apply all settings to the current user account and to the default profile" box in Regional and Language Options, Advanced tab.

|||

Hello,

I had this problem because the collation of my AS2005 server was diferent from the collation of my cube.

Can you confirm if both settings are the same?

Best Regards

|||Hi,

where can I look the collation up?

Thanks!|||

Right click on SSAS2005-server in management studio.

Select properties.

Select languages/collation.

HTH

Thomas