For one of the cube I’m working on, we needed to format a measure by adding the suffix k€. The front-end is Excel 2010/2013.
My colleague was in charge of this part of the development and had defined a FORMAT_STRING for the measure, as we usually do:
FORMAT_STRING ="#,##0 k€". I was in charge of the testing, with NBi, and I had defined a test for the format of a cell and this test was successful. Sun shinning.
A few days later, I received a screenshot of an Excel sheet and my eyes were caught by a poor formatting. In place of an expected
1.075 k€ I had an awful
1075,2357777. My first move was to connect with SSMS and I ran the equivalent MDX query and surprise … the format was correct
1.075 k€. Back to an Excel sheet, connected to this cube, and indeed the format was wrong
1075,2357777. Confirmation from the cube definition, the format was defined and confirmation from NBi the format was correctly applied. WTF? Why Excel wasn’t able to display the value correctly formatted when other tools were able?
Since the development of NBi, I knew that SSAS is returning a VALUE and a FORMATTED_VALUE. NBi is able to retrieve this FORMATTED_VALUE to perform a test of a cell’s format. The documentation of SSAS is describing the relation between FORMATTED_VALUE and FORMAT_STRING:
FORMAT_STRING: The formatting template to be applied to the value of the cell to generate FORMATTED_VALUE property
I decided to use the SSAS profiler to retrieve the query emitted by Excel. The query wasn’t really complex but the CELL PROPERTIES were a bit embarrassing: no trace of FORMATTED_VALUE just FORMAT_STRING. Based on this observation, a search on Google confirmed that Excel is retrieving the FORMAT_STRING and is applying the format by itself (in place of relying on the FORMATTED_VALUE returned by SSAS).
Apparently Excel and SSAS don’t have the same interpretation of the FORMAT_STRING. A miracle occurred and I took the decision to place a backslash before the k:
#.##0 \k€. The format was still correct in SSAS and suddenly a k was also visible in Excel … a few seconds later , I added another backslash in front of the euro symbol and the value was correctly formatted in Excel and SSAS.
Based on SSAS documentation, the backslash is not needed and indeed SSAS is correcty managing the FORMAT_STRING without the backslash. But apparently Excel is less tolerant and the backslash is mandatory in this kind of formatting.