CODEWITHSUNDEEP
iccubeiccube-reporting
Joel Doe
Joel Doe

Reputation: 41

icCube - How to create Excel compatible thousands formatting?

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.

Upvotes: 2

Views: 85

Answers (1)

Marc Polizzi
Marc Polizzi

Reputation: 9375

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?)?

Upvotes: 0

Related Questions