excel export problem

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.
thanks.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.
> thanks.|||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.
> >
> > thanks.|||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
"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.
> > >
> > > thanks.|||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.
"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.
"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.
"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.
"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.