[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1708
  • Last Modified:

Export to excel in ssrs 2008

This is an issue related with ssrs 2008, l have a text box, which has time value coming from database, I have to add or subtract a second to it while rendering the report so my expression is

=dateadd(dateinterval.second, 1, fields!abc.value)

this renders fine and everything looks good until I export the report to excel, when I export this report in excel, I see ######## on those specific text boxes, instead of the actual value. Now my question is; is this normal, as export to word looks fine as well
0
Kerau
Asked:
Kerau
  • 10
  • 9
1 Solution
 
sameer2010Commented:
Hi,
Try selecting format on the xls column as number OR try changing the datatype of the field being exported to xls as text.
0
 
sameer2010Commented:
Expand the column width and see if it shows the complete value.
0
 
KerauAuthor Commented:
Sure, I will give this a try and get back,
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
KerauAuthor Commented:
Well I tried selcting number on the excel sheet, I don't know where is this coming from but it shows something like -53688.75 instead of 9:00:00
0
 
sameer2010Commented:
Ohh...then you need to set the format to time, instead of number.
0
 
KerauAuthor Commented:
How do I do this?
0
 
sameer2010Commented:
Click on the column, right clik, select format, you would see time option, select appropriate time format.
0
 
KerauAuthor Commented:
Well, that is being implemented as my format, it's not number. Otherwise 9:00:00 wouldn't come :)
0
 
sameer2010Commented:
So does xls not show the right time even after changing the column format to HH:MM:SS? If not, try changing what you are exporting to varchar.
0
 
KerauAuthor Commented:
I am sorry, I didn't get you. The whole point of this question is export to excel, in word the same text box works fine. I know for sure that excel has it's own brain :) while it gets the values in it's cell, I am trying to figure out why the time shown on report and exported to any other format remains the same where as excel converts this to ##########
0
 
sameer2010Commented:
Yes. XLS is trying to set its own format for the column and hence, the issue. I am thinking that it has to do with the column format. Could you please attach the xls and we will figure it out?
0
 
KerauAuthor Commented:
Okay, I am finding this difficult to attach xlsx, for obvious reasons; security, sigh :(,  anyways, I will try and explain what it shows when I leave my mouse on those specific cells

"date and time are negative to too large to display" and when I double click on those particular cells I see the underlying value of -53688.625
0
 
sameer2010Commented:
Ok! Can you go to Excel options -> Advanced -> When calculating this excel and change the use 1904 date. Then change the column format and see what do you see. :-)
0
 
KerauAuthor Commented:
When I change the particular cell from -53788.625 to 1904, I see that the cell now displays 00:00:00 which shows that the format is correct but I still don't get my values :(, also one thing to be noted here is the report is rendered in en-Fr and exported in ms-office which is set in us-English
0
 
sameer2010Commented:
You need not change the cell value. Just try setting the option that I suggested in Excel options.
0
 
KerauAuthor Commented:
You are great! The time comes out perfect, but there is a -ve attached on it :( something like -09:00:00
0
 
sameer2010Commented:
Type  the following in the custom format
hh:mm:ss;hh:mm:ss;- ;

Open in new window

0
 
KerauAuthor Commented:
Can't really format all the cells being exported to excel. The whole intent of ssrs would be worthless :)
0
 
sameer2010Commented:
Then there appears to be no other solution than converting this column into text field (instead of date/time field) before exporting in the SQL query.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 10
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now