| Groups | Blog | Home |
sql server reporting services : excel export problem
We have a big report with 4 or 5 levels of drill downs and various other expressions. While exporting to excel format, unlike other formats, the drill down capability and other expressions as formulas are also exported. Thus if the report that is exported is large then exporting does not succeed and excel throws an error. "Damage to the file was so extensive that repairs were not possible. Excel attempted to recover your formulas and values, but some data may have been lost or corrupted." I am using sql server 2005 reporting services sp1. I think this was common problem and it seems it is not yet fixed. Also I see the answer to this question as exporting to csv format with ASCII encoding. However, we cannot use this trick. My question is, is there a way (something like device information setting or any other property that exists in reporting services) to prevent drill down information and formulas from reaching excel. however the font/background color, heading and other stuff, as they appear in pdf format, should also appear in excel? is this possible? Please reply as early as possible.
Hi,
Formulas can be omitted with "rc:OmitFormulas=True" in the URL. May be you can use two URL's one with HTML's and the other with Excel as format and omitting formula's and removing "Action" from the second report so when you save in excel it removes hyperlink and formulas. Let me know how you are intend to display from report manager or your customized code. In the second option we can do it using two URL's. Amarnath [quoted text, click to view] "msdnuser" wrote:
> Hi all, > > We have a big report with 4 or 5 levels of drill downs and various other > expressions. > While exporting to excel format, unlike other formats, the drill down > capability and other expressions as formulas are also exported. Thus if the > report that is exported is large then exporting does not succeed and excel > throws an error. > > "Damage to the file was so extensive that repairs were not possible. Excel > attempted to recover your formulas and values, but some data may have been > lost or corrupted." > > I am using sql server 2005 reporting services sp1. I think this was common > problem and it seems it is not yet fixed. Also I see the answer to this > question as exporting to csv format with ASCII encoding. However, we cannot > use this trick. My question is, is there a way (something like device > information setting or any other property that exists in reporting services) > to prevent drill down information and formulas from reaching excel. however > the font/background color, heading and other stuff, as they appear in pdf > format, should also appear in excel? > is this possible? > > Please reply as early as possible. >
Hi,
Thank you for the reply. Can you tell me how to remove actions for excel export. We are using custom webapplication to display the reports. I tried to render the report in excel format using "url acess" method applying rc:omitFormula=true. Still I got the same error message. Is there a way to stop the drill down information from reaching excel? does removing actions will help this drill down issue too? Thanks. [quoted text, click to view] "Amarnath" wrote:
> Hi, > Formulas can be omitted with "rc:OmitFormulas=True" in the URL. May be you > can use two URL's one with HTML's and the other with Excel as format and > omitting formula's and removing "Action" from the second report so when you > save in excel it removes hyperlink and formulas. > > Let me know how you are intend to display from report manager or your > customized code. In the second option we can do it using two URL's. > > Amarnath > > > "msdnuser" wrote: > > > Hi all, > > > > We have a big report with 4 or 5 levels of drill downs and various other > > expressions. > > While exporting to excel format, unlike other formats, the drill down > > capability and other expressions as formulas are also exported. Thus if the > > report that is exported is large then exporting does not succeed and excel > > throws an error. > > > > "Damage to the file was so extensive that repairs were not possible. Excel > > attempted to recover your formulas and values, but some data may have been > > lost or corrupted." > > > > I am using sql server 2005 reporting services sp1. I think this was common > > problem and it seems it is not yet fixed. Also I see the answer to this > > question as exporting to csv format with ASCII encoding. However, we cannot > > use this trick. My question is, is there a way (something like device > > information setting or any other property that exists in reporting services) > > to prevent drill down information and formulas from reaching excel. however > > the font/background color, heading and other stuff, as they appear in pdf > > format, should also appear in excel? > > is this possible? > > > > Please reply as early as possible. > >
Hi,
If you are using custom there you can do it. Create a copy of your original report and keep everything same except all hyperlinks, e.g report1.RDL, Report_1.RDL. You can create a hyperlink something like "Export to Excel" when the users click the link it will be directed to the "copy of the report which you removed links (Report_1.RDL)" You should be able to do it with a little bit of coding. Amarnath [quoted text, click to view] "msdnuser" wrote:
> Hi, > > Thank you for the reply. Can you tell me how to remove actions for excel > export. > We are using custom webapplication to display the reports. > > I tried to render the report in excel format using "url acess" method > applying rc:omitFormula=true. Still I got the same error message. Is there a > way to stop the drill down information from reaching excel? does removing > actions will help this drill down issue too? > > Thanks. > > "Amarnath" wrote: > > > Hi, > > Formulas can be omitted with "rc:OmitFormulas=True" in the URL. May be you > > can use two URL's one with HTML's and the other with Excel as format and > > omitting formula's and removing "Action" from the second report so when you > > save in excel it removes hyperlink and formulas. > > > > Let me know how you are intend to display from report manager or your > > customized code. In the second option we can do it using two URL's. > > > > Amarnath > > > > > > "msdnuser" wrote: > > > > > Hi all, > > > > > > We have a big report with 4 or 5 levels of drill downs and various other > > > expressions. > > > While exporting to excel format, unlike other formats, the drill down > > > capability and other expressions as formulas are also exported. Thus if the > > > report that is exported is large then exporting does not succeed and excel > > > throws an error. > > > > > > "Damage to the file was so extensive that repairs were not possible. Excel > > > attempted to recover your formulas and values, but some data may have been > > > lost or corrupted." > > > > > > I am using sql server 2005 reporting services sp1. I think this was common > > > problem and it seems it is not yet fixed. Also I see the answer to this > > > question as exporting to csv format with ASCII encoding. However, we cannot > > > use this trick. My question is, is there a way (something like device > > > information setting or any other property that exists in reporting services) > > > to prevent drill down information and formulas from reaching excel. however > > > the font/background color, heading and other stuff, as they appear in pdf > > > format, should also appear in excel? > > > is this possible? > > > > > > Please reply as early as possible. > > >
Hi ringt,
Thank you for your post and thanks Amarnath's solution. I would like to know whether the solution from Amarnath is helpful. If the solution is not match your scenario or you still have further questions or concern, would you please send the RDL file and the datasource to me? I understand the information may be sensitive to you, my direct email address is weilu@ONLINE.microsoft.com ( Please remove the ONLINE. when you send the email), you may send the file to me directly and I will keep secure. Sincerely, Wei Lu Microsoft Online Community Support ================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights.
Hi Amarnath and Wei Lu,
The solution that Amarnath has given will make the excel export always expanded. But what we want is something similar to PDF export. Initially the report will be all collapsed and the user will see this report in our web application in HTML format. Then user might expand one or two nodes to any level that he likes. Then user might export to several formats, one among them is excel. If he export to excel, then whatever he saw in HTML page before clicking excel export button should be available/visible in excel report. But there should not be drilldowns in excel report. Thus only those rows that he saw in HTML should actually go to Excel like PDF exporting. Is this possible some how?? Thanks. [quoted text, click to view] "Wei Lu" wrote:
> Hi ringt, > > Thank you for your post and thanks Amarnath's solution. > > I would like to know whether the solution from Amarnath is helpful. If the > solution is not match your scenario or you still have further questions or > concern, would you please send the RDL file and the datasource to me? I > understand the information may be sensitive to you, my direct email address > is weilu@ONLINE.microsoft.com ( Please remove the ONLINE. when you send the > email), you may send the file to me directly and I will keep secure. > > Sincerely, > > Wei Lu > Microsoft Online Community Support > > ================================================== > > When responding to posts, please "Reply to Group" via your newsreader so > that others may learn and benefit from your issue. > > ================================================== > This posting is provided "AS IS" with no warranties, and confers no rights. >
Hi ringt,
Thanks for the update. I tested on my side and it just works fine. The report is all collapsed and I expand some node and then export to Excel. It just works as your request on my side on both SQL Server Reporting Services 2005 and 2000. Would you please send the rdl file to me for further troubleshoot? I understand the information may be sensitive to you, my direct email address is weilu@ONLINE.microsoft.com (Please remove the ONLINE. when you send the mail), you may send the file to me directly and I will keep secure. Sincerely, Wei Lu Microsoft Online Community Support ================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights.
Hi,
What you would have tried is normal export to excel. If your report has drill down and if you expand one or two nodes and export to excel , it is working good. But what I am saying is it should work the same way but further expand collapse feature of drill down should not be available in excel export like PDF. is this possible? Thanks. [quoted text, click to view] "Wei Lu" wrote:
> Hi ringt, > > Thanks for the update. > > I tested on my side and it just works fine. The report is all collapsed and > I expand some node and then export to Excel. It just works as your request > on my side on both SQL Server Reporting Services 2005 and 2000. > > Would you please send the rdl file to me for further troubleshoot? > > I understand the information may be sensitive to you, my direct email > address is weilu@ONLINE.microsoft.com (Please remove the ONLINE. when you > send the mail), you may send the file to me directly and I will keep secure. > > Sincerely, > > Wei Lu > Microsoft Online Community Support > > ================================================== > > When responding to posts, please "Reply to Group" via your newsreader so > that others may learn and benefit from your issue. > > ================================================== > This posting is provided "AS IS" with no warranties, and confers no rights. >
Hi ringt,
Thank you for the update. I don't think it is possible to excel render works like pdf render since PDF can not support any operation like expand collapse but excel did support this operation. The Excel render will export the collapse and the only thing you could do is to write a custom render and use this to export your report. Sincerely, Wei Lu Microsoft Online Community Support ================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights.
I dont think this can be done. Still its a funny idea, to take "Print screen"
as an image file. "It is as good as what you see is what you get" whether it is possible you will have to check. Amarnath. [quoted text, click to view] "Wei Lu" wrote:
> Hi ringt, > > Thank you for the update. > > I don't think it is possible to excel render works like pdf render since > PDF can not support any operation like expand collapse but excel did > support this operation. > > The Excel render will export the collapse and the only thing you could do > is to write a custom render and use this to export your report. > > Sincerely, > > Wei Lu > Microsoft Online Community Support > > ================================================== > > When responding to posts, please "Reply to Group" via your newsreader so > that others may learn and benefit from your issue. > > ================================================== > This posting is provided "AS IS" with no warranties, and confers no rights. >
Thank you both for helping me by sharing your knowledge.
[quoted text, click to view] "Amarnath" wrote:
> I dont think this can be done. Still its a funny idea, to take "Print screen" > as an image file. "It is as good as what you see is what you get" whether it > is possible you will have to check. > > Amarnath. > > "Wei Lu" wrote: > > > Hi ringt, > > > > Thank you for the update. > > > > I don't think it is possible to excel render works like pdf render since > > PDF can not support any operation like expand collapse but excel did > > support this operation. > > > > The Excel render will export the collapse and the only thing you could do > > is to write a custom render and use this to export your report. > > > > Sincerely, > > > > Wei Lu > > Microsoft Online Community Support > > > > ================================================== > > > > When responding to posts, please "Reply to Group" via your newsreader so > > that others may learn and benefit from your issue. > > > > ================================================== > > This posting is provided "AS IS" with no warranties, and confers no rights. > >
Hi ringt,
Thank you for the update. If you have any questions or concerns, please feel free to let me know. Sincerely, Wei Lu Microsoft Online Community Support ================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights.
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |