SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 #Value! in Excel export from SSRS 2008
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JAG7777777
Starting Member

United Kingdom
25 Posts

Posted - 11/25/2012 :  06:52:02  Show Profile  Reply with Quote
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

United Kingdom
25 Posts

Posted - 11/27/2012 :  03:50:40  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000