SSRS Report not maintaining same format when exported to Excel

[M_nwadibia]

Hello ,

I have a Oracle SQL query

Select To_NUMBER( AG.SBA_REP, '99,999.99')

FROM TABLE A.

I used the SQL in SSRS and formatted the cell with SSRS expression FORMAT(CDec(Fields!Total_RLHAP_Award.Value), " #,0.00").

The Problem is when I export the report to excel the column records returns to text/General instead of integer. I have also tried removing the expression but the result is the same.

Any idea of how to solve this?.

[johncpratt]

I just ran into this myself, using on-prem SSRS with SQL Server 2019.  My textbox format value is "=Switch(Fields!IsContribAmtOrPct.Value = "pct", "0.#\%", Fields!IsContribAmtOrPct.Value = "amt", "C2")".  The "C2" is respected on export to Excel but the custom format is not.  Since our main Excel version is 2016, I also tried it on a laptop with Excel 365 installed locally, and got the same issue.

[johncpratt]

Aaaannnddd...  I figured out the workaround.  Plus I realized that my "format not working when exporting to Excel" issue is slightly different from the OP.  However, @d_gosbell is definitely correct and his reply pointed me in the right direction.  He deserves the solution for this post.

To address the OP and my issue, the underlying cell value MUST be in the format AND data type that is desired when exporting to Excel.  This means if you want a specific numeric format upon export to Excel, including decimal points, you either have to do that in the SQL statement or format the table cell's Value and convert it back to a number afterward.

Here is an example of the SQL (using T-SQL; no PL/SQL to test with) needed to take a decimal value and convert it to another decimal value but with the formatting specified in the OP.  For larger numbers, increase the 7 to the total number of digits on both the left and right of the decimal point.

For my issue, I only needed WYSIWYG output, so I had to take the Format property's Switch statment and incorporate it into the Value expression.

[kzoppo]

Did you ever figure this out? I'm haivng the same issue.

[d_gosbell]

This is expected since the FORMAT() function returns a string. If you want to maintain the data type you should use the Format property on the textbox in your table instead of using the FORMAT function in an expression on the Value.

[M_nwadibia]

I have tried this and it didnt work. see the screen shot. When I exported it, the excel defaulted to general intead of staying as number.