Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

When connecting Excel 2010 to a SSAS 2008 R2 datasource, calculated fields(Standard Margin) do not display with the appropriate currency symbol. When I define the measures in the cube, I am setting the 'format string' property to the appropriate value.

There are three cases-

  1. Fixed measures marked as currency; they display correctly
  2. Calculated measures marked as percentages; they display correctly
  3. Calculated measures marked as currency; they display as simple decimal numbers.

This appears to specific to Excel. Management Studio displays the cell data correctly.

Is this a known issue? Alternately, can anyone reproduce this issue?

share|improve this question
    
What happens if you round the given number to two decimal places using the ROUND formula, and then apply formatting to the cell? –  Breakthrough Apr 17 '12 at 16:08
    
Round does not work. However if I select the canned format string - "$#,##0.00;($#,##0.00)" from the list(which should be an alias for "currency"), it works. Let's just call that 'unexpected'. –  Jeff Sacksteder Apr 17 '12 at 16:27

1 Answer 1

Seems to be an issue specific to calculated members (not base measures on measure groups).

The unfortunate workaround is to add both format string and language to your calculated measures: FORMAT_STRING = 'Currency', LANGUAGE = 1033,

In theory this shouldn't be necessary if the language of the whole cube is set correctly to 1033. I've found other people making this claim. But in my experience, setting the language of the cube to 1033 doesn't fix the issues with calculated measures.

Here is a reference to the known issue: http://connect.microsoft.com/SQLServer/feedback/details/600246/excel-does-not-use-format-string-currency-when-browsing-a-cubes-calculated-member

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.