Geek Answers Handbook

Non-formatting numbers when exporting from SSRS 2008 R2 to Excel 2010

I use an expression to display the currency depending on whether the value is in millions or thousands. This expression is set in the "Format" property of my report:

=iif((Fields!PrevActual.Value < 100000), "'$'#,,0,'K';('$'#,0,'K')", "'$'0,,.0,'M';('$'0,,.0,'M')'")

Thus, if this value is 1,500,000, the number will be displayed as $ 1.5 million, and if it is 15,000, it will be displayed as $ 15 thousand.

The problem is that I export the file to Excel. Numbers in thousands retain formatting, but numbers in millions lose it. So I have a table with numbers, for example, 1,500,000 and $ 15,000. All numbers are exported as text.

Does anyone know a way to save the formatting in excel so that it matches the way it was displayed in the SSRS report, while making sure that it is also a number?

Essentially, I want to emulate what excel did before we automated this report using SSRS. Numbers in millions were in this usual number format: $ #. 0, \ M and thousands were in it: $ #, K.

Thanks!

+4
source share
1 answer

If you export an excel formula, it may work. that is, export = "text (1500000," "'$' 0, .0, 'M'; ('$' 0, .0, 'M')" ")"

Excel then converts the formula to the final formatted value of $ 15M

The tricky part is making the format string valid in excel and creating an SSRS formula that concatenates the lines and generates an Excel formula.

This may help: http://msdn.microsoft.com/en-us/library/ms159220%28SQL.90%29.aspx

0
source

More articles:


All Articles