TL;DR: What (Where) do I need to make changes in order to instruct Excel to use the format string to format the values pulled from the cube?
I have a SSAS cube that adds some calculated measures onto an existing user-defined function. On top of that, I use Excel and Power Pivot to display the data for the user. The user wants the resulting value (generally a number less than 2) to display with 2 decimal points.
In Visual Studio, I have added the following line to the MDX declaration:
FORMAT_STRING = "#,##0.00;-#,##0.00",
However, when I go to pull the data in using Excel, it doesn't use the formatting.
I have made sure that my connection configuration includes OLAP Server Formatting. I have also seen that Excel has a hard time with some of the formatting options such as "Currency" with certain language settings or case issues.
Edit: I have also made sure to refresh the excel connection, pulling in the data again.
What (Where) do I need to make changes in order to instruct Excel to use the format string to format the values pulled from the cube?
If this requires much work, would offering the user a template in Excel with the formatting set Excel-side offer the same capability? I am wondering specifically about the users removing the values and adding them back.
Edit: The version of Excel I am using is Excel Professional Plus 2010, version 14.0.7015.1000 (64 bit)