3

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)

Josh Gallagher
  • 5,211
  • 2
  • 33
  • 60
Gilbrilthor
  • 432
  • 4
  • 14

1 Answers1

4

The issue was that the format statement was being set right after the declaration of the calculated measure. This placement wouldn't have been an issue if there were no scope statements afterward that modified the measure.

However, the addition of several scope statements related to the measure were present. Because of these, the FORMAT_STRING was being lost.

To fix this issue, the FORMAT_STRING must come after all the scope statements regarding the measure.

FORMAT_STRING([Measures].[Dimension]) = "#,##0.00;-#,##0.00";

After this code was added to the end of the MDX statement, Excel pulled the format correctly.

Gilbrilthor
  • 432
  • 4
  • 14