Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JAG7777777
Starting Member
25 Posts |
 Posted - 2012-11-25 : 06:52:02
|
| Hi,I've recently upgraded a bunch of reports from 2005 to 2008. When they were 2005, the reports exported fine into XL. Since converting to 2008 a lot are coming up with #VALUE! in non-decimal number cells - which means the end users can't use the data in their macros etc..I've done some research and have seen a couple of possible solutions. One is to use a format code of 'N0' - although we have tried this and it doesn't seem to work for all PCs (we think due to regional settings). Another possible solution is to check the check-box on the actual text box to 'Use Regional Settings'. We haven't tried this yet.I thought I would ask first if anyone knows of an absolute solution (regardless of end user OS, XL version and regional settings)?Thanks in advance,JAG7777777 |
|
|
JAG7777777
Starting Member
25 Posts |
Posted - 2012-11-27 : 03:50:40
|
| Hi Everyone,We managed to resolve this in the end. Just in case it proves useful to anyone else, here's what we found:The N0 (or n0) format means Number with 0 decimal places (so you could have N2 or n2 for two decimal places etc..). This seems to be the most robust formatting in comparison to explicit formatting - i.e. #,##0.## - as this ends up rendering the decimal point regardless (e.g. 6,100 ends up looking like 6,100.) We didn't want to use #,###.## as this left blanks when the returned number was 0 - and the users didn't like this as it made them wonder if the dataset was empty.We also discovered that choosing a 'Number' format and checking the 'Use Regional Settings' and the 1,000 separator is the equivelant of typing nx (where x is the number of decimal places) in a 'Custom' format. So, despite these two options appearing to be posted elsewhere as possible solutions, it looks like they are essentially the same.Anyways, we have had no further issues with exporting to Excel with these settings so thought share our findings.JAG7777777 |
 |
|
|
|
|
|
|
|
#Value! in Excel export from SSRS 2008