Forum Discussion

M_nwadibia's avatar
M_nwadibia
Icon for Resolver III rankResolver III
3 years ago

SSRS Report not maintaining same format when exported to Excel

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....
40
johncpratt's avatar
johncpratt
Icon for Advocate III rankAdvocate III
2 years ago

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's avatar
johncpratt
Icon for Advocate III rankAdvocate III
2 years ago

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.