Today I was with a customer and created a calculated measure that was formatted as a percentage. The formatting showed up fine in the cube browser but did not in Excel, which was a bit strange given that other percentage calculated measures on the cube seemed to be working fine. Now I knew that Excel doesn’t pick up the formatted_value of a cell in a cellset, but instead takes the format_string associated with each cell and interprets this as an Excel format inside a pivot table, and this explains why sometimes SSAS formats don’t work in Excel. So clearly something was going wrong with Excel interpreting the format string I’d defined.
Finally the guy I was working with, Andrew Baker, worked it out. Look at the two following calculated measures:
CREATE MEMBER CURRENTCUBE.MEASURES.C1 AS 0.99, FORMAT_STRING=’PERCENT’;
CREATE MEMBER CURRENTCUBE.MEASURES.C2 AS 0.99, FORMAT_STRING=’Percent’;
When you add them to a cube and browse the cube in the cube browser, you see this:
In Excel you see this:
What’s the difference? Yes, you guessed it, when it comes to the built-in SSAS format string types like ‘Percent’, Excel is case-sensitive. So a format string like ‘PERCENT’, while it’s valid from an MDX point of view, gets ignored by Excel whereas ‘Percent’ is correctly interpreted as a percentage format.
Yes, I found this out a few years ago. I couldn’t believe it. I now try and stay away from the standard formats.
I greatly appreciate this post. I would also appreciate it if ReportBuilder would do the same thing (utilize Fields!(“FORMAT_STRING”) automatically as the format of the tablix cell!
i also prefer using standard formats as SSRS can neither handle PERCENT nor Percent when you want to use the cube’s formatting within your report
It would be great if you could investigate deeper into formatting. I have different issues making formats work in Excel. It seems to be dependent on user locale or something like that, since on some computers things like “0,00#” work, on others no formatting is applied.