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.

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 #Value! in Excel export from SSRS 2008

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
Go to Top of Page
   

- Advertisement -