2

I have an icCube report where I want "1500000" to be formatted as "1,500K".

If I set FORMAT_STRING="#,##0\K,", the formatting works in icCube but not in the exported Excel sheet. If I set FORMAT_STRING="#,##0,\K", the formatting works in the exported Excel sheet but not in icCube.

How can I specify a FORMAT_STRING that works for both Excel and icCube?

I'm using icCube version 7.10.

Steps to Reproduce Formatting Mismatch

  1. Run the following query from the icCube MDX debugger:

     WITH
   CALCULATED MEMBER [WorksInIcCube] AS 1500000, FORMAT_STRING="#,##0\K,"
   CALCULATED MEMBER [WorksInExcel] AS 1500000, FORMAT_STRING="#,##0,\K"
 SELECT {[WorksInIcCube],[WorksInExcel]} ON 0
 FROM [MyCube]
 CELL PROPERTIES STYLE, CLASSNAME, VALUE, FORMATTED_VALUE, FORMAT_STRING

    This will create an icCube table where WorksInIcCube is formatted as 1,500K and WorksInExcel is formatted as 1500000.

  2. Click the "Export the MDX result to Excel" button and open the exported file in Excel. In Excel, WorksInIcCube is formatted as 1,500,00K, and WorksInExcel is formatted as 1,500K.

Joel Doe
  • 41
  • 3

1 Answers1

0

I cannot reproduce the behavior FORMAT_STRING="#,##0,\K" (assuming you're seeing 1500K in Excel) in Excel.

How are you exporting your MDX result? From the MDX IDE or from the Reporting (which widget?)?

Marc Polizzi
  • 9,275
  • 3
  • 36
  • 61
  • If I set the formatting to `#,##0,\K`, the number is displayed as 1500000 in the icCube table and 1,500K in Excel. I am exporting the Excel file from a Table widget in the Reporting view. I can see in the exported Excel file that the custom cell formatting is set to `#,##0,K`. – Joel Doe Mar 29 '21 at 09:58
  • You mean a Pivot Table ? Where are you defining that FORMAT_STRING ? Can you reproduce with a very simple MDX statement please. – Marc Polizzi Apr 01 '21 at 05:08
  • I was exporting the data from a pivot table in the report view, but I can also reproduce the formatting issue in the MDX debugger. Please see the steps in the updated description. – Joel Doe Apr 01 '21 at 13:31