SSAS Calculation Formatting Not Showing in Excel 2007 or Excel 2010

Occasionally in SQL Server 2005 and 2008 Analysis Services, you'll build a cacluation and set the formatting to be a value like a currency and are surprised that in Excel 2007 and 2010 the formatting does not carry over. The "randomness" of this actually does have a bit of a method behind it. The formatting issue that you see in browsers like Excel is because Excel is looking for the type of language that you want to display the currency format. In other words, should Excel show GB Pounds or US Dollars? You'll see the behavior when creating calculations and you specify currency for example in BIDS as in the below screenshot.

Notice they're no language here. To fix this, you'll have to click the Script View in the Calculation Tab. Next, add the statement Language=1033 (where 1033 is for US English) after the calculation close but before the semicolon. You can see an example of one below.

 Create Member CurrentCube.[Measures].[Reseller Average Unit Price]

 As [Measures].[Reseller Unit Price] 
    /
    [Measures].[Reseller Transaction Count],
 
Format_String = "Currency", Language=1033,
Associated_Measure_Group = 'Reseller Sales', 
Non_Empty_Behavior = [Reseller Unit Price] ;    

As you can imagine, this can be quite a pain. There are a few ways to fix this so you don't have to specify the language on the entire server. Chances are, some if not most servers already have this set. You can either set the default language on the entire server, database, cube or cells. If the calculation is not set, SSAS will go to the next highest level to see if it can find how to format the currency.

For example, you can set the language for the entire cube to English (US). Sometimes though the higher setting is not good enough for the client browser and you'll need to set it at the calculated measure level.

I hope this helps prevent you from having to knock your head into the keyboard as I did!