Number format from SSAS cube lost

Ok, Microsoft have been investigating this, and have come back with a response.Note that this bug is also present in the AdventureWorks2012 multidimensional cube.

The root cause: the DIVIDE() function does not retain the formatting of a measure.

Due to our business requiring currency conversion, we had a scope statement performing a division by a month end currency rate. This DIVIDE function was removing the formatting!

here's the MDX we were using for our currency conversion:

Scope ( {
                Measures.[Gross Sales - DRTV]          
                } );                                                                                            
                        Scope( Leaves([Currency Conversion Date]) ,
                                [Reporting Currency].[NZD],     
                                Leaves([Currency]));                                                                                            
                      
                                // Convert Local value into Pivot currency for selected Measures that must be converted with Measure rate [Day Rate] 
                                Scope( {
                                Measures.[Gross Sales - DRTV]
                          } );                                                                                            
                                 
                      This =  Divide([Reporting Currency].[Local],  Measures.[Month End Rate];

Here's the response from Microsoft.

-----

"It looks like the MDX function Divide() will cause the result to lose the format. I would suggest that you try the following 

1. Define a new calculated member as the following:

2. CREATE MEMBER CURRENTCUBE.[Measures].[Currency Converted]
   AS Divide([Reporting Currency].[Local],  Measures.[Month End Rate]), 
   FORMAT_STRING = "Currency", 
   VISIBLE = 0 ,  ASSOCIATED_MEASURE_GROUP = 'Fact Currency Rate'  ;

   2. Modify the above “this=” line to the following:

This=[Measures].[Currency Converted];    

Since the calculated member is forced to have the format string, the cell should show the value with the currency format in the scopes.

The above is the idea on how we can change the MDX script to force the format. You can use the similar workaround on other measures."

----

I hope this helps anyone else who runs into the same issue.