December 15, 2010 at 12:25 pm
Hi all,
I've a problem with how currency is displayed when MS Excel links to a cube in my data warehouse - it's showing dollars ($) when I want it to display GBP (£). Here's some info on various settings:
1) Checking Regional Settings via Control Panel on our Server 2008 box shows that they're all correctly set to the UK.
2) The relevant fields in the source tables the cube uses are set to a datatype of smallmoney.
3) The relevant fields in the dimensions and cube are set to Currency.
4) The Language setting of SQL Server 2008 is set to "English (United States)".
Changing the format property of the measures to use £ doesn't have any effect, and as you can imagine I've tried various combinations of properties but all to no avail. I suspect that it may be some locale setting that I need to set in SSAS or SSMS but I haven't been able to find anything as yet that looks like it could provide a solution.
Anybody have any ideas? It's not urgent, but I'm going to look pretty stupid if I can't display the correct currency to end-users... 😉
Cheers
RF
_____________________________________________________________
MAXIM 106:
"To know things well, we must know the details; and as they are almost infinite, our knowledge is always superficial and imperfect."
Francois De La Rochefoucauld (1613-1680)
December 15, 2010 at 1:48 pm
Have you tried changing the settings in Excel? That's where the actual formatting takes place.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 15, 2010 at 2:45 pm
Hi GSquared,
I was under the impression (maybe misguided!) that they would be inherited from SSAS. When I highlight and format the cells in the pivot table to currency, they display the correct £ instead of $.
There doesn't seem to be anything obvious in the Connection Properties dialog, and the Excel Options via the Office button says that my Primary Editing Language is "English (United Kingdom)" - although both it and "English (United States)" are available in the Enabled Editing Languages listbox above it. I haven't been able to find any other setting that may help.
I'm at home at the moment, but have tried using AdventureWorks and I get the same problem on my own laptop. I change the format property of the measure to currency, refresh the Excel pivot table and it displays dollars. If I set it to £#,##0.00;-£#,##0.00 then it displays it as a number with no currency symbol at all.
I can temporarily force it to display £ in Excel by clicking on the measure in the Values area section of the pivot table and choosing Number Format. If I then remove the measure from being displayed and add it back again, it loses this formatting.
I've come to the conclusion that I'm missing something blindingly obvious or doing something really stupid... 😀
Cheers,
RF
_____________________________________________________________
MAXIM 106:
"To know things well, we must know the details; and as they are almost infinite, our knowledge is always superficial and imperfect."
Francois De La Rochefoucauld (1613-1680)
December 15, 2010 at 2:48 pm
Sorry, but I forgot to mention earlier that when browsing the cube in SSAS it does display the correct currency (i.e. £). That seems to point to Excel being the culprit as you suggest.
_____________________________________________________________
MAXIM 106:
"To know things well, we must know the details; and as they are almost infinite, our knowledge is always superficial and imperfect."
Francois De La Rochefoucauld (1613-1680)
December 15, 2010 at 5:25 pm
I think I've cracked it!
So for the benefit of anyone who ever suffers the same problem as I did...
The easy way to do it is to set the Language property of the cube explicitly. From the Cube Structure tab, click on the cube in the Measures section. Go over to the Properties section and find the Language property (it's in the Advanced group). Change this to the language of your choice (in my case "English (United Kingdom)"), and any measure formatted as currency will display correctly in MS Excel.
It may be gone midnight here in the UK, but at least I'll be able to sleep easy tonight... 😀
_____________________________________________________________
MAXIM 106:
"To know things well, we must know the details; and as they are almost infinite, our knowledge is always superficial and imperfect."
Francois De La Rochefoucauld (1613-1680)
December 16, 2010 at 7:10 am
Glad you worked it out. Good job.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply