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.seco
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
=dateadd(dateinterval.seco
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
Expand the column width and see if it shows the complete value.
ASKER
Sure, I will give this a try and get back,
ASKER
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
Ohh...then you need to set the format to time, instead of number.
ASKER
How do I do this?
Click on the column, right clik, select format, you would see time option, select appropriate time format.
ASKER
Well, that is being implemented as my format, it's not number. Otherwise 9:00:00 wouldn't come :)
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.
ASKER
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 ##########
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?
ASKER
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
"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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
You need not change the cell value. Just try setting the option that I suggested in Excel options.
ASKER
You are great! The time comes out perfect, but there is a -ve attached on it :( something like -09:00:00
Type the following in the custom format
hh:mm:ss;hh:mm:ss;- ;
ASKER
Can't really format all the cells being exported to excel. The whole intent of ssrs would be worthless :)
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.
Try selecting format on the xls column as number OR try changing the datatype of the field being exported to xls as text.