Exporting report to excel with subreports problem

Hello,

I got a error when exporting to excel one report that has a table and in a column it has a subreport.

Is this possible to do?

What is the work around?

The error that i got it is the following.

Server Error in '/Reports' Application.
--

For more information about this error navigate to the report server on the local server machine, or enable remote errors
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Exception: For more information about this error navigate to the report server on the local server machine, or enable remote errors

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[Exception: For more information about this error navigate to the report server on the local server machine, or enable remote errors]

[Exception: An error occurred during rendering of the report.]
Microsoft.Reporting.WebForms.ServerReport.ServerUrlRequest(Boolean isAbortable, String url, Stream outputStream, String& mimeType, String& fileNameExtension) +489
Microsoft.Reporting.WebForms.ServerReport.InternalRender(Boolean isAbortable, String format, String deviceInfo, NameValueCollection urlAccessParameters, Stream reportStream, String& mimeType, String& fileNameExtension) +959
Microsoft.Reporting.WebForms.ServerReportControlSource.RenderReport(String format, String deviceInfo, NameValueCollection additionalParams, String& mimeType, String& fileExtension) +84
Microsoft.Reporting.WebForms.ExportOperation.PerformOperation(NameValueCollection urlQuery, HttpResponse response) +143
Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context) +75
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +154
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64

--

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210

Thank you

It is my understanding that the sub reports do NOT export to excel. I re-wrote many reports when I had to provide that functionality.

I realize this dosn't directly answer your question but rather than spend hours addressing the problem, you could re-do the report and get the result you want.

Tim

|||Well not what i wanted to hear, but thanks anyway

Exporting Query Analyzer data with column headers...?

Hi,

Does anyone out there know how to do this? I've been banging head for
awhile now trying to answer this seemingly simple question.

tia,

Mike[posted and mailed, please reply in news]

Ixnay (mscgloss@.yahoo.com) writes:
> Does anyone out there know how to do this? I've been banging head for
> awhile now trying to answer this seemingly simple question.

Export to what?

There are two ways that you may want to investigate. Both are found under
Tools->Options->Results.

Here you can select output to file.

You can also for output to text select which delimiter to use. For instance,
you can select tab as delimiter. Then in the results tab press CTRL-A
followed by CTRL-C, and if Excel where you want the data, you go there
and press CTRL-V in cell A1.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I had the same problem. Then I discovered that if you make the query
in Enterprise Manager and copy THOSE results, you get the column
headers.

Go figure...

On 23 Jan 2004 12:44:45 -0800, mscgloss@.yahoo.com (Ixnay) wrote:

>Hi,
>Does anyone out there know how to do this? I've been banging head for
>awhile now trying to answer this seemingly simple question.
>tia,
>Mike|||Erland, just want to thank you -- output to text with the tab
delimiter works exactly as you stated. I much prefer writing SQL in
Query Analyzer (as opposed to Enterprise Manager), so this is going to
be my method from now on! :)

On Fri, 23 Jan 2004 22:56:19 +0000 (UTC), Erland Sommarskog
<sommar@.algonet.se> wrote:

>[posted and mailed, please reply in news]
>Ixnay (mscgloss@.yahoo.com) writes:
>> Does anyone out there know how to do this? I've been banging head for
>> awhile now trying to answer this seemingly simple question.
>Export to what?
>There are two ways that you may want to investigate. Both are found under
>Tools->Options->Results.
>Here you can select output to file.
>You can also for output to text select which delimiter to use. For instance,
>you can select tab as delimiter. Then in the results tab press CTRL-A
>followed by CTRL-C, and if Excel where you want the data, you go there
>and press CTRL-V in cell A1.

Exporting PDF - Adding empty page

Hi,

I created a report using dynamic display( I hide the column using the expression in Property->Visibility).By using expression the column which is having null is hided.When I try to export the report to PDF,the empty pages are displaying along with data.Ie.,The report contyains only 5 pages but in PDF it is displaying 10 pages(5pages data with 5 empty pages).

Any Suggestions appreciated!

Thanks

Please verify that the definition width of your table (regardless of any hidden columns) is less than the width of the body.

Also, be sure that:

Body.Width < Report.PageWidth - Report.LeftMargin - Report.RightMargin

-Chris

|||

Chris Baldwin - MSFT wrote:

Please verify that the definition width of your table (regardless of any hidden columns) is less than the width of the body.

Also, be sure that:

Body.Width < Report.PageWidth - Report.LeftMargin - Report.RightMargin

-Chris

I am trying to accomplish the same thing and my problem is the Body width as mentioned by Chris.. it is too wide since my available set of columns is fairly large. Are there any known tricks to work around this limitation, using matrices, or whatever technique?

- Ron

Exporting PDF - Adding empty page

Hi,

I created a report using dynamic display( I hide the column using the expression in Property->Visibility).By using expression the column which is having null is hided.When I try to export the report to PDF,the empty pages are displaying along with data.Ie.,The report contyains only 5 pages but in PDF it is displaying 10 pages(5pages data with 5 empty pages).

Any Suggestions appreciated!

Thanks

Please verify that the definition width of your table (regardless of any hidden columns) is less than the width of the body.

Also, be sure that:

Body.Width < Report.PageWidth - Report.LeftMargin - Report.RightMargin

-Chris

|||

Chris Baldwin - MSFT wrote:

Please verify that the definition width of your table (regardless of any hidden columns) is less than the width of the body.

Also, be sure that:

Body.Width < Report.PageWidth - Report.LeftMargin - Report.RightMargin

-Chris

I am trying to accomplish the same thing and my problem is the Body width as mentioned by Chris.. it is too wide since my available set of columns is fairly large. Are there any known tricks to work around this limitation, using matrices, or whatever technique?

- Ron

Exporting Multiple tables to a single file

I need to export data from multiple tables into one single file. The big problem here is that the tables will have different column types.

I am attempting to create something that allows users to be able to send me the contents of their tables's, through either email or ftp. I would prefer to make it easier for them so they only have to deal with one file, instead of the multiple files that bcp and dts create when exporting from multiple tables.

I was thinking of using DTS or BCP and then join (append) the files (either zip them or append the files together in some fashion), but I was hoping that there was an easier method out there.

Any ideas on how I may accomplish this would be greatly appreciated.

AndyWhy not create a table that matches the final datatype/s, do an insert from all the tables that need to be exported and then dump the table to a file? At the end, drop the table or truncate it. Varchar datatype usually does wel in storing most datatypes.|||I'd have DTS:

1 Build a scratch directory,
2 Create the needed files in the scratch directory
3 Zip the entire scratch directory
4 Email the zip file
5 Delete the files and scratch directory
6 Delete the zip file if it wasn't needed any more

-PatP|||I am attempting to create something that allows users to be able to send me the contents of their tables

WAIT: Fundamental flaw in process!

I'd have a sproc perform a backup and then send the backup file (after it was zipped).|||WAIT: Fundamental flaw in process!

I'd have a sproc perform a backup and then send the backup file (after it was zipped).If what needed to be sent was over 50% of the database, then I'd agree with Brett. If you only want 50 Kb of a 30 Gb database and the users connect via dial up, I like my plan better. I guess a lot depends on the circumstances.

-PatP|||"It Depends"

My favorite answer...|||"It Depends"

My favorite answer...Darned if I don't like that one too!

-PatP|||I think I forgot to mention one detail that might complicate things. I'm planning on doing some sort of web based .net application with C#. Is it difficult to use DTS through an application like this?

I know how to do sql queries in .net but is there some way to use DTS in this type of app?|||There are actually many ways to use a DTS package from c# (http://support.microsoft.com/default.aspx?scid=kb;en-us;810581).

-PatP|||And how about BCP in this situation?

Exporting Column Names

Is it possible, when exporting data to Excel, to export the column names with the data?
Thanks!If you use DTS and export to an XLS, you can get the column names on the first run. For some reason, I can't get it to automate.|||Got it! Thanks! :-)

Exporting a table with changing column names to an excel file

I'm trying to write an SSIS package that exports a table that has changing column names to an excel file. The column names change due to the fact that the table is created by a pivot daily. the only thing I'm missing is the ability to dynamically map the tables' columns to the excel destination. Is this possible?

I read in another thread that

"It is not possible to create packages or new objects within packages using SSIS."

I also read in the books online that "The input and the input columns of the Excel destination have no custom

properties." To me this means that I cannot programmatically create or remove columns in the excel destination. Please tell me I'm wrong.

So, to summarize my research so far. In writing an SSIS package, I cannot programmatically create a new excel destination object and I can't manipulate an existing one. I hope I'm wrong. Can anyone help me? (and please correct any wrong assumptions I may have stated)

I'm interested in finding this out as well. I need to basically take a dataset and cast it out to multiple pivot tables in Excel. If you find anything out about this, or find that you can't manage it; please let me know. Feel free to email!

thx

|||

It is not possible for a package to dynamically alter its pipeline(s) at execution-time. The metadata of the pipeline is set at design-time.

There are workarounds to this - but they're difficult. I have talked a little about this subject here:

But it used to work in DTS (1) - Modifing a Package in script
(http://blogs.conchango.com/jamiethomson/archive/2006/06/28/4156.aspx)

-Jamie

sql

Exporting a table with changing column names to an excel file

I'm trying to write an SSIS package that exports a table that has changing column names to an excel file. The column names change due to the fact that the table is created by a pivot daily. the only thing I'm missing is the ability to dynamically map the tables' columns to the excel destination. Is this possible?

I read in another thread that

"It is not possible to create packages or new objects within packages using SSIS."

I also read in the books online that "The input and the input columns of the Excel destination have no custom

properties." To me this means that I cannot programmatically create or remove columns in the excel destination. Please tell me I'm wrong.

So, to summarize my research so far. In writing an SSIS package, I cannot programmatically create a new excel destination object and I can't manipulate an existing one. I hope I'm wrong. Can anyone help me? (and please correct any wrong assumptions I may have stated)

I'm interested in finding this out as well. I need to basically take a dataset and cast it out to multiple pivot tables in Excel. If you find anything out about this, or find that you can't manage it; please let me know. Feel free to email!

thx

|||

It is not possible for a package to dynamically alter its pipeline(s) at execution-time. The metadata of the pipeline is set at design-time.

There are workarounds to this - but they're difficult. I have talked a little about this subject here:

But it used to work in DTS (1) - Modifing a Package in script
(http://blogs.conchango.com/jamiethomson/archive/2006/06/28/4156.aspx)

-Jamie

Export to text file

I'm exporting data from one table (for now) to a fixed width text file.
However, the first column of the text file doesn't come from the
database. I figured using a constant would be the best way to deal with
this, but being new (thrown in) to T-SQL, I'm not sure how to get
there.
The resulting text file has 4 fields, all with fixed character
placement. The first field is an account #, which is the constant field
not in a table. The 2nd field is a 2 character placeholder that's
supposed to be blank, the third a dollar amount, and the 4th a date.
All are to be displayed as a string with no formatting and leading
zeros:
001234567899 00000050006012006
001234567899 00000004306012006...
I can manage individual statements in query analyzer (for fields 2-4),
but I'm clueless about putting it all together in a t-sql statement/dts
package. Any ideas are appreciated.
Thanks,
Not-for-long Newbie and proud of it.Here are two options for using literal values in your SELECT statements.
First choice is good in stored procedures where you don't know the constant
value in advance.
DECLARE @.ConstantValue
SELECT @.ConstantValue = Column_X
FROM MyTable
WHERE {criteria}
SELECT
@.ConstantValue
, Column2
, Column3
, Column4
FROM MyOtherTable
Second Option: (constant value is known is advance)
SELECT
'ConstantValue'
, Column1
, Column3
, Column4
FROM MyOtherTable
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
<birdbyte@.gmail.com> wrote in message news:1151430908.647285.244450@.m73g2000cwd.googlegroup
s.com...
> I'm exporting data from one table (for now) to a fixed width text file.
> However, the first column of the text file doesn't come from the
> database. I figured using a constant would be the best way to deal with
> this, but being new (thrown in) to T-SQL, I'm not sure how to get
> there.
>
> The resulting text file has 4 fields, all with fixed character
> placement. The first field is an account #, which is the constant field
> not in a table. The 2nd field is a 2 character placeholder that's
> supposed to be blank, the third a dollar amount, and the 4th a date.
> All are to be displayed as a string with no formatting and leading
> zeros:
> 001234567899 00000050006012006
> 001234567899 00000004306012006...
>
> I can manage individual statements in query analyzer (for fields 2-4),
> but I'm clueless about putting it all together in a t-sql statement/dts
> package. Any ideas are appreciated.
>
> Thanks,
> Not-for-long Newbie and proud of it.
>

Export to MS Excel carriage returns

I have a report which has multiple fields in the same column of a table. So that it formats correctly, on the stored procedure side I inserted char(10) + char(13). This allows the next field to correctly carriage return on the report. The report looks great.

But once the report is exported to MS Excel, it appears to add in extra carriage returns. This can be cleaned up by going record by record and back-spacing over the carriage returns.

Is there any other work around for this?

Just curious. Thank you for your help.

T.J.

I haven't found one as yet. The other minor annoyance is that when you open the exported report in excel the cells with carriage returns all have the same row height and hence you can't actually tell if a cell has multiple lines unless you manually autosize the rows.|||

It really appears that the report export to MS Excel is just a file for the users to work with once the data gets exported. As the data does not come over clean (too many carriage returns), or the data does not completely display (all the data arrives, but it is hidden in the column, even if wrap text is checked), or the excel spreadsheet over extends the page to the left and prints 2 pages for 1 report page.

Has anyone had any success with exporting a report to MS Excel, or is it just a way for the users to edit the data?

If anyone can offer advice, I would appreciate it.

Thank you,

T.J.

|||The other thing it can't do is export sureports.|||

Thank you for telling me that. I had no idea.

Strange. I have a sub-report that exports to Excel just fine, but a sub-report on the sub-report does not export (the original sub-report is not linked, just a straight listing of data, seperate from the rest of the report).

Oh my.

|||Reading your post I just noticed that you have the order switched for those characters. You are using a line feed (chr 10) and then a carriage return (chr 13). Micro$ likes to see them in the other order: vbcrlf - or char(13) + char(10)... and some other OS's (that shall not be named) just want to see the line feed... which is probably why Excel is adding in the extra characters (it's picking up on it as a non-ms format).

Export to MS Excel carriage returns

I have a report which has multiple fields in the same column of a table. So that it formats correctly, on the stored procedure side I inserted char(10) + char(13). This allows the next field to correctly carriage return on the report. The report looks great.

But once the report is exported to MS Excel, it appears to add in extra carriage returns. This can be cleaned up by going record by record and back-spacing over the carriage returns.

Is there any other work around for this?

Just curious. Thank you for your help.

T.J.

I haven't found one as yet. The other minor annoyance is that when you open the exported report in excel the cells with carriage returns all have the same row height and hence you can't actually tell if a cell has multiple lines unless you manually autosize the rows.|||

It really appears that the report export to MS Excel is just a file for the users to work with once the data gets exported. As the data does not come over clean (too many carriage returns), or the data does not completely display (all the data arrives, but it is hidden in the column, even if wrap text is checked), or the excel spreadsheet over extends the page to the left and prints 2 pages for 1 report page.

Has anyone had any success with exporting a report to MS Excel, or is it just a way for the users to edit the data?

If anyone can offer advice, I would appreciate it.

Thank you,

T.J.

|||The other thing it can't do is export sureports.|||

Thank you for telling me that. I had no idea.

Strange. I have a sub-report that exports to Excel just fine, but a sub-report on the sub-report does not export (the original sub-report is not linked, just a straight listing of data, seperate from the rest of the report).

Oh my.

|||Reading your post I just noticed that you have the order switched for those characters. You are using a line feed (chr 10) and then a carriage return (chr 13). Micro$ likes to see them in the other order: vbcrlf - orchar(13) + char(10)... and some other OS's (that shall not be named) just want to see the line feed... which is probably why Excel is adding in the extra characters (it's picking up on it as a non-ms format).

export to ccsv problem

hi all

I am exporting a customer table to a csv the problem is with the telephone column, in the db it is +442085390123 but in the csv files it is showing as 4.42086E+13

I have tried on converting it on export but i am still having problems ..any surgestions?

Kind Regards

rich

Are you viewing in Excel or similar?

If so, it is because the app is asuming that the value is a numeric. The data in the .csv file is probably correct, can you view it in Notepad?

export text column to csv file problem

I'm trying to find out how to export column with text datatype (which consist of HTML tags with text) to csv file. So far I have no luck. Any help appreciated.

Thanks,

Albert.

Any reason exporting XML data to .csv?

Check the following URL, it may help you...

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q307021

|||

that's clients requirements. as far for the link i don't have experience in vb.

thanks though.