Thousand separators in AS 2005

My problem is about measure formats in AS 2005.

I would like to present the number ten thousand(10000) as 10.000,00 , with (.) as thousand separator(European/Swedish format for numbers and (,) for the decimal).

I do not want(10,000.00) because this is a problem when third part clients export data to Excel. The above 10,000.00 can be translated as 10 after an export to Excel..

If I run Pivot Tables, directly against As 2005, in Excel 2003, it simply ignores all AS 2005 formats and the problem. Other clients, like ProClarity displays server formats but in a US-way, on clients running Swedish regional settings.

Export from third party clients, like ProClarity, to Excel can also translate 10000 as 10 000,00 which will be treated as text in Excel. Notice the tab in this example( 10000 will be inserted as 10°000) in Excel)

All the standard formats in AS 2005(like #,#.00 ) will not solve this problem.

The main issue here is that numbers will be twisted to text or changed to something completely different in Excel.

I have not seen the same problem on Reporting Services 2000 after exporting to Excel. You get server defined formats on the client.

Any suggestions

Regards

Thomas Ivarsson

Answer this question

Thousand separators in AS 2005

Worf
Hello! This was one year ago but I have used (### ### ###.##) in the format property for measures and calculated measures in SSAS2005.

This worked for ProClarity and Excel 2003 clients that use Swedish as a regional setting in the control panel.

I think there is a regional settings property regarding the service account for SSAS2005(and other servers), that you can check in the control panel of Windows 2003 Server.

I can check this further tomorrow.

HTH

Thomas Ivarsson
LaurieD

I’m also interested in how you solved the problem. Please tell us what you did to solve this issue.

Regards,

Inmon
Biju S Nair
Late answere. Do not use the dot and the decimal in ### ### ###.##.

Do it like this ### ### ###

HTH

Thomas Ivarsson
Dragon Li

With your suggestion I recieve the following format for my measures: 3 991.

How do I remove the point
ilovecplusplusitturnsmeon
In my regional settings(Windows XP) I have (,) as decimal and(.) as thousand separator.

For [Internet Sales Amount] , in the Adventure Works cube, I have the formatstring property set to ### ### ###.00

In Excel2007, on the same machine, this measure will be presented as 3 266 373,66 (Internet Sales Amount for CY2001 and Bikes).

In ProClarity 6.3 Professional it will be presented as 3 266 373.66

When I export from ProClarity to Excel2007 it will be presented as 3266373,657

This means that it will depend on the client, what formats you get, even on two different clients running on the sam machine.

Regards

Thomas Ivarsson
Anthony Hodsdon - MSFT
Can you tell me what you did to solve the problem I'm having exactly the same problem with Microsoft Business Scorecard Manager, that displays "." as decimal separator when I need it to use ",".

Thanks.
granny8
The problem is solved. AS 2005 always demands (.) for decimals(in the format_string property, even if this is not correct according to my regional settings.
danclarke_2000
Check under the controlpanel/regional settings/ Advanced. In this tab you have the option to set your regional settings for all user accounts on the server, including the service accounts.

HTH

Thomas Ivarsson
Thousand separators in AS 2005

Answer this question