17 September 2008

Analysis Services For Excel: Currency Formatting

An annoying, yet easily resolvable issue I've come across in Analysis Cubes for Excel is that the calculated measure formats do not come across to Excel (like YTD Budget - GL Trans which should be Currency but comes across as General). After going through the usual troublehsooting (is Excel 2007 setup to inherit the format from the cube, etc), I found this is a built in issue with SSAS when a default language isn't assigned to the database or cube. To resolve do the following:

1. Open Business Intelligence Development Studio, then open the Analysis Services Database in which you installed ACE.
2. Open the Financials Cube

3. Click the Calculations Button
4. Click the Script View Button and in any calculation where FORMAT_STRING = Currency, add the line LANGUAGE=1033. Careful of commas - if you get a squiggly red line, you have a syntax error

6. 1033 is US Currency. You'd have to add the appropriate language code for other formats. Also, this implies the calculcation is language specific (ie: US Dollars only) so you'd have to do something slightly more complex if the measure supported multiple formats.

7. Close the window, save the changes and reprocess the cube.
8. Repeat with other measures in other cubes as needed

This should now allow you see the Currency format default into your Excel worksheet.