Forum Discussion

kmd's avatar
kmd
Contributor II
2 days ago

Force Excel to store the source number (no scaling) when CV is exported

Hoping someone out there has some ideas on how to make this work...

Let's say we have a cube view containing a parameter that allows the user to select a scaling option (millions, thousands or no scaling). The results of the parameter are stored in the CV's default CellFormat.

Currently, when the CV is run and exported to Excel; regardless of the scaling selected by the user, the display in Excel is in thousands and Excel stores the entire number (unscaled).
For example - if scaling selected is millions:
If the number is 345,773,247.11;
it displays in the CV as 346,
it displays in Excel as 345,773 (but the stored number shown in the formula bar is the correct source value:  345,773,247.11).
In other words, regardless of whether user selects thousands, millions or no scaling.... Excel displays as thousands and stores the correct number.  If I try to change the Excel format to display in any other way (i.e. millions), it will store the scaled value based on the format and no longer stores the full number.  This happens even if I force ExcelUseScale=False.

The goal in this exercise is to figure out a way to "display" the exported CV in Excel exactly as it appears in the cube view but still retain the whole number.  Using the above example, I would like to see this result:
CV Displays 346
Excel Displays 346
Excel stores 345,773,247.11

Is this even possible?

I have tried multiple ways to make this work including manual formatting, XFBR, parameters, etc., but I still can't determine the exact combination of formatting options that will result in what I want to see.

Would love to hear if anyone has found a way to accomplish this.  Thanks

No RepliesBe the first to reply