Exporting report to multiple PDF's

Is it possile for a report to be exported to multiple PDF files, each pdf file would be for a group in the report and would ideally use field information for the group to construct the pdf filename?

The purpose of this is to provide a separate PDF for access over the web, so for example we have a scheduled report that generates a report grouped by sales region we would end up with SalesRegionA.PDF, SalesRegionB.PDF etc.

Hi Hugh,

What you can do is have a parameter in your report which will have the groups...ie you can have a parameter for Sales Region, then what you can do is run batch script, which has a command that calls a .rss file passing in some values -which will export your values to pdf...

you can create a batch script that calls the .rss file like below...

rs -i RunReports.rss -s http:..localhost/reportserver -1 0 -v directory="%1 -v reportname= "Name of report" -v filename="Name of file.pdf" -v format="pdf" -v params="this is were the parameter value goes" -v debug.

rs -i RunReports.rss -s http:..localhost/reportserver -1 0 -v directory="%1 -v reportname= "Name of report 2" -v filename="Name of file.pdf 2" -v format="pdf" -v params="this is were the parameter 2 value goes" -v debug.

(note you can have mutiple statements, allowing you to pass different values in the reportname, params section...

Your .rss file will use these values to render through the reports and export the files, i beleive there are examples on the Web of this.

Or what you can do is use a data driven subscription which is much easier (you will have to create the parameter in your report still). You have to create a table in your SQL Server DB, to hold the parameters ie all the Sales Region and use the Subscription to look at this tables..to do this go to subscriptions in report manager (when viewing the report), then go to data driven subscriptions, then follow the steps which are pretty simple...

Hope this helps.

|||Thans for your suggestions, I'd sort of moved in the direction of a a data driven subscription anyway but I think there's still quite a bit more to do, especially working out how to trigger the subscription from an application - I've started looking at the fireevent api.

Exporting report into multiple worksheet

I have a requirement to export the report into multiple excel worksheet. Is
that possible? Can someone please help me with that?
Thanks
--
Message posted via http://www.sqlmonster.comYes, it's possible.
You need to insert "page breaks".
See "Reporting Services Book Online" - article "How to add a page break
(Report Designer)".sql

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 multiple tables as flat files

Hi.

I've tried to create a SSIS package to simply export a bunch of tables as flat files, and am having troubles because when the for each loop hits the second table the column mappings in the flat file destination are not synchronised with its schema.

I created a for each loop with an enumerator that returns the table names and sets a user variable.

I created a data flow task which dynamically connects to the table name variable.

In the Flat File Destination there is a column mapping property, but I don't know how to reset these mappings on each iteration.

Any ideas?

Are all the tables you are trying to export have exactly same column metadata? If not, you can't do this using a for loop. This is because SSIS cannot dynamically update the column mapping/schema. If the tables have different metadata, you need to create separate data flow tasks for them.|||Thanks.

exporting multiple rows

I'm trying to set up a row that has 5 rows below it and when you click on the + it'll expand to show the five lines.

They're all set up to toggle based on the one row. It works online, but when I try to export it to anything, it only picks up the first detail row and leaves off the rest. I'm doing this for a bunch of reports, and they're all being emailed using subscriptions.

I've moved the detail rows around but each time it's just the first detail row that is exported.

Any ideas?

thx,

M@.

mattcushing@.gmail.com(noSpam)

Hello,

What formats are you trying to export to? Toggleable visibility is only supported in a few rendering extensions (HTML, Winforms, and in some cases Excel).

When you export to PDF or Image, those renderers honor the toggle state at the time the file is exported. So, if you have all of your toggleable items collapsed and then export to PDF you will only see the collapsed representation.

-Chris

sql

Exporting multiple Reports to one file... URGENT

Hello frendz.
I want to export multiple reports to one file i.e. in one text file , pdf file, excel file
can anyone guide me .. how i can do that.. ?
thanks
DhavalYou can export one report in one file. Export reports in different files and combine them as a whole. I dont think there is an option to export multiple reports to single file

exporting multiple reports into one Excel Book

Hello,
Is there a way to export multiple reports(lets say 2 different reports) into
one Excel book at the same time'
please help
--
Saptagiri Tangudu
PNT Marketing ServicesOne way to do it would be to add these reports to a parent report as
subreports and export the parent report.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Saptagiri" <saptagiri@.gmail.com> wrote in message
news:5A547A03-455A-4B65-894B-EEBF3A41FBDC@.microsoft.com...
> Hello,
> Is there a way to export multiple reports(lets say 2 different reports)
into
> one Excel book at the same time'
> please help
> --
> Saptagiri Tangudu
> PNT Marketing Services|||The sub report seems to have limitations, and also does not render as a
seperate sheet in an Excel book.
We produce serveral Excel reports to be distributed to various branches of a
major bank. I created the reports in the Visual studio editor, and am using a
C# program which supplies the branch name as a parameter and procudes the
excel book reports. now I want to be able to produce several reports into one
excel book at the same time.
Is there a way i can produce these excel books with multiple reports other
than using the sub report technique'
thanks a bunch...
Saptagiri Tangudu
PNT Marketing Services
"Ravi Mumulla (Microsoft)" wrote:
> One way to do it would be to add these reports to a parent report as
> subreports and export the parent report.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Saptagiri" <saptagiri@.gmail.com> wrote in message
> news:5A547A03-455A-4B65-894B-EEBF3A41FBDC@.microsoft.com...
> > Hello,
> >
> > Is there a way to export multiple reports(lets say 2 different reports)
> into
> > one Excel book at the same time'
> >
> > please help
> >
> > --
> > Saptagiri Tangudu
> > PNT Marketing Services
>
>|||After the reports are generated, you could use Excel object model to merge
the files into one file.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Saptagiri" <saptagiri@.gmail.com> wrote in message
news:CD494DE2-7107-4892-94A9-97A126EA175D@.microsoft.com...
> The sub report seems to have limitations, and also does not render as a
> seperate sheet in an Excel book.
> We produce serveral Excel reports to be distributed to various branches of
a
> major bank. I created the reports in the Visual studio editor, and am
using a
> C# program which supplies the branch name as a parameter and procudes the
> excel book reports. now I want to be able to produce several reports into
one
> excel book at the same time.
> Is there a way i can produce these excel books with multiple reports other
> than using the sub report technique'
> thanks a bunch...
> Saptagiri Tangudu
> PNT Marketing Services
> "Ravi Mumulla (Microsoft)" wrote:
> > One way to do it would be to add these reports to a parent report as
> > subreports and export the parent report.
> >
> > --
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > "Saptagiri" <saptagiri@.gmail.com> wrote in message
> > news:5A547A03-455A-4B65-894B-EEBF3A41FBDC@.microsoft.com...
> > > Hello,
> > >
> > > Is there a way to export multiple reports(lets say 2 different
reports)
> > into
> > > one Excel book at the same time'
> > >
> > > please help
> > >
> > > --
> > > Saptagiri Tangudu
> > > PNT Marketing Services
> >
> >
> >|||Thanks...
Saptagiri Tangudu
PNT Marketing Services
"Ravi Mumulla (Microsoft)" wrote:
> After the reports are generated, you could use Excel object model to merge
> the files into one file.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Saptagiri" <saptagiri@.gmail.com> wrote in message
> news:CD494DE2-7107-4892-94A9-97A126EA175D@.microsoft.com...
> > The sub report seems to have limitations, and also does not render as a
> > seperate sheet in an Excel book.
> >
> > We produce serveral Excel reports to be distributed to various branches of
> a
> > major bank. I created the reports in the Visual studio editor, and am
> using a
> > C# program which supplies the branch name as a parameter and procudes the
> > excel book reports. now I want to be able to produce several reports into
> one
> > excel book at the same time.
> >
> > Is there a way i can produce these excel books with multiple reports other
> > than using the sub report technique'
> >
> > thanks a bunch...
> > Saptagiri Tangudu
> > PNT Marketing Services
> >
> > "Ravi Mumulla (Microsoft)" wrote:
> >
> > > One way to do it would be to add these reports to a parent report as
> > > subreports and export the parent report.
> > >
> > > --
> > > Ravi Mumulla (Microsoft)
> > > SQL Server Reporting Services
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > > "Saptagiri" <saptagiri@.gmail.com> wrote in message
> > > news:5A547A03-455A-4B65-894B-EEBF3A41FBDC@.microsoft.com...
> > > > Hello,
> > > >
> > > > Is there a way to export multiple reports(lets say 2 different
> reports)
> > > into
> > > > one Excel book at the same time'
> > > >
> > > > please help
> > > >
> > > > --
> > > > Saptagiri Tangudu
> > > > PNT Marketing Services
> > >
> > >
> > >
>
>|||Hi ,
Can you please let me know How to use Excel object maodel and where to use it?
Please guide me.
TIA
"Ravi Mumulla (Microsoft)" wrote:
> After the reports are generated, you could use Excel object model to merge
> the files into one file.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Saptagiri" <saptagiri@.gmail.com> wrote in message
> news:CD494DE2-7107-4892-94A9-97A126EA175D@.microsoft.com...
> > The sub report seems to have limitations, and also does not render as a
> > seperate sheet in an Excel book.
> >
> > We produce serveral Excel reports to be distributed to various branches of
> a
> > major bank. I created the reports in the Visual studio editor, and am
> using a
> > C# program which supplies the branch name as a parameter and procudes the
> > excel book reports. now I want to be able to produce several reports into
> one
> > excel book at the same time.
> >
> > Is there a way i can produce these excel books with multiple reports other
> > than using the sub report technique'
> >
> > thanks a bunch...
> > Saptagiri Tangudu
> > PNT Marketing Services
> >
> > "Ravi Mumulla (Microsoft)" wrote:
> >
> > > One way to do it would be to add these reports to a parent report as
> > > subreports and export the parent report.
> > >
> > > --
> > > Ravi Mumulla (Microsoft)
> > > SQL Server Reporting Services
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > > "Saptagiri" <saptagiri@.gmail.com> wrote in message
> > > news:5A547A03-455A-4B65-894B-EEBF3A41FBDC@.microsoft.com...
> > > > Hello,
> > > >
> > > > Is there a way to export multiple reports(lets say 2 different
> reports)
> > > into
> > > > one Excel book at the same time'
> > > >
> > > > please help
> > > >
> > > > --
> > > > Saptagiri Tangudu
> > > > PNT Marketing Services
> > >
> > >
> > >
>
>

Exporting DataSets from multiple datasources

Is their away to have reporting services call a dataset from an access
database and export it into a sql server table through automation in a
project with 2 datasources. One datasource from access and the other from
sql server. I can do this with a DTS, but could I do it another way to work
around a DTS.
RandyUrghh, i don´t think that the way reporting services should be used. If you
really really really wanna do this, set up an linked server on sql server to
point to access and write a procedure in which you do your importing stuff.
Return from the procedure some kind of return code to display this in your
report.
But (BTW) is a really nasty way to use RS :(
HTH (either), Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Randy" <Randy@.discussions.microsoft.com> schrieb im Newsbeitrag
news:5218EBE9-A92E-4528-B381-9E835A72A978@.microsoft.com...
> Is their away to have reporting services call a dataset from an access
> database and export it into a sql server table through automation in a
> project with 2 datasources. One datasource from access and the other from
> sql server. I can do this with a DTS, but could I do it another way to
> work
> around a DTS.
> Randy|||Let me add my voice to what Jens is saying. This is definitely NOT a good
idea.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:ebfg4dERFHA.904@.tk2msftngp13.phx.gbl...
> Urghh, i don´t think that the way reporting services should be used. If
you
> really really really wanna do this, set up an linked server on sql server
to
> point to access and write a procedure in which you do your importing
stuff.
> Return from the procedure some kind of return code to display this in your
> report.
> But (BTW) is a really nasty way to use RS :(
> HTH (either), Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Randy" <Randy@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:5218EBE9-A92E-4528-B381-9E835A72A978@.microsoft.com...
> > Is their away to have reporting services call a dataset from an access
> > database and export it into a sql server table through automation in a
> > project with 2 datasources. One datasource from access and the other
from
> > sql server. I can do this with a DTS, but could I do it another way to
> > work
> > around a DTS.
> >
> > Randy
>

Exporting Data from SDF Database to CSV file

Hi guys,

Would any of you be able to provide some guide on how am I going to export the selected data (multiple rows and columns) into Excel or CSV file?

Or at least export into Text file, which I can later on save the file name as .CSV, so it become a CSV file after saving.

Thanks.

Regards,

Jenson

In which context is the data "selected". You can always enumerate your data and use the System.IO namespace to create the CSV file in code?|||

Hi Erik,

Yes Erik, that's what I intend to do, do you have any guide for me to follow, or any sample codes to refer to? I need to refer to them and write my own one, as I don't really think what I wanted to do is the same, I just need to structure and check how they do it, and what are the various ways to achieve the same thing.

Thanks.

Regards,

Jenson

|||

Hope this sample is what you are looking for:

Code Snippet

using System;

using System.Collections.Generic;

using System.Text;

using System.Data.SqlServerCe;

namespace ExportSDF

{

class Program

{

static void Main(string[] args)

{

SqlCeConnection conn = null;

SqlCeCommand cmd = null;

SqlCeDataReader rdr = null;

try

{

// Based on this sample: http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlcedatareader.aspx

// Open the connection and create a SQL command

//

conn = new SqlCeConnection(@."Data Source = C:\Program Files\Microsoft SQL Server Compact Edition\v3.1\SDK\Samples\Northwind.sdf;max database size=256");

conn.Open();

cmd = new SqlCeCommand("SELECT * FROM Customers", conn);

rdr = cmd.ExecuteReader();

System.IO.TextWriter stm = new System.IO.StreamWriter(new System.IO.FileStream(@."C:\customers.csv", System.IO.FileMode.Create), Encoding.Default);

// Iterate through the results

//

while (rdr.Read())

{

// Write all fields except the last one...

for (int i = 0; i < rdr.FieldCount-2; i++)

{

if (rdr[i] != null)

{

stm.Write(rdr[i].ToString());

stm.Write(";");

}

else

{

stm.Write(";");

}

}

if (rdr[rdr.FieldCount-1] != null)

{

stm.Write(rdr[0].ToString());

}

stm.Write(System.Environment.NewLine);

}

// Always dispose data readers and commands as soon as practicable

//

stm.Close();

rdr.Close();

cmd.Dispose();

}

finally

{

// Close the connection when no longer needed

//

conn.Close();

}

}

}

}

Happy coding!

|||Hi Erik,

Thanks for the great reply and sorry for the late reply. The reason behind is I have finished the workaround for this requirements and it's working just fine. By looking at your code, I find that this code is even easier to understand!

I will try it out later as I'm currently busy with other stuff. Thanks for the great codes, Erik!

Have marked your answer as answer =)

Exporting Data from Reporting Services into Multiple Excel Worksheets

Hi,

Please help me.

I need to export around 1 million records to excel. As we know that there is a limit of approx 65,000 rows in one worksheet so the exporting functionality is failing. One way is that I should be able to configure the Reporting Service in such a way so that once 65,000 records gets populated in the first worksheet, then the rest 35,000 records gets popultaed in the second worksheet.

I am not able to find out how this can be achieved in Reporting Services. Please help me in this.

Thanks in advance.

Regards

Raman

Hello Raman,

Take a look at this link, in the 'Page Breaks' section, it may help you.

http://msdn2.microsoft.com/en-us/library/ms157328.aspx

It requires that you set a group with a group expression on X number of rows, then you page break on the group.

Hope this helps.

Jarret

Export to multiple PDFs on-demand?

Is there an easy way to export a single report to multiple PDF files
on demand? I know that I can use a Data-Driven Subscription to
generate multiple files, but if I want to give this ability to a user
to look at the report, decide that it's good, and then save as PDF to
multiple files, can I do that?
Scenario: We have an invoicing system. The report generates invoices
for all of our customers at one time (we have the option to do
individual, but as the number of customers grows, this could get
tedious). After verifying that we've applied all credits or factored
in extra charges, we want to generate the reports. Currently, we
export to a PDF and manually split out each invoice into its own
file. I figure there's got to be an easier way to do this on demand,
but don't quite know where to start.
We're running SQL RS 2005 - SP2+hotfixes at this point. Any
suggestions on how to move forward would be appreciated.
Thanks.
-Pete SchottOn May 10, 12:53 pm, paschott <pasch...@.gmail.com> wrote:
> Is there an easy way to export a single report to multiple PDF files
> on demand? I know that I can use a Data-Driven Subscription to
> generate multiple files, but if I want to give this ability to a user
> to look at the report, decide that it's good, and then save as PDF to
> multiple files, can I do that?
> Scenario: We have an invoicing system. The report generates invoices
> for all of our customers at one time (we have the option to do
> individual, but as the number of customers grows, this could get
> tedious). After verifying that we've applied all credits or factored
> in extra charges, we want to generate the reports. Currently, we
> export to a PDF and manually split out each invoice into its own
> file. I figure there's got to be an easier way to do this on demand,
> but don't quite know where to start.
> We're running SQL RS 2005 - SP2+hotfixes at this point. Any
> suggestions on how to move forward would be appreciated.
> Thanks.
> -Pete Schott
As far as I know, this functionality does not exist (outside a custom
application). That said, you could group by customer and select 'Page
break at end' as part of the grouping properties of table/matrix
report controls. Also, you could include a subreport based on the
customer; however, these options only create separate pages. Sorry
that I could not be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Export to Multiple Excel Sheets

I saw a post with this same subject line, posted in July of 2006, but with no replies. I am now having precisely the same problem.

I am importing data from an OLE DB source. I want to directly store this data in an Excel file. There are far more than 65,536 rows in the DB table, but the version of Excel I have only tolerates a maximum of that many. My solution is to divide the data into separate worksheets within the same Excel file. At any given time, I do not know exactly how many rows are moving from the database to the Excel file, so is there a way to dynamically create a new worksheet every time I reach 65,536 rows?

Thank you.

Hi Allen,

there is no an easy way to do this.

I can give you a couple of not-so-easy options though:

1. Build a script or custom destination that will know how to split data, create and load sheets. You will need to talk to the OLE DB JET provider directly.

2. Do it in two passes:

- First pass; partiotion data by adding a new column to identify divided partitions and stage the new data in a table or flat file.

- Second pass; Build a package to find out how many partitions there is, create the destination sheets, and run a loop with number of iterations equal to number of partitions created in the previous step. In each iteration, run a child package that can filter data for its partition ans load it into an appropriate Excel sheet.

I am sure there are additional approaches to implement this. It depends on restrictions you are bound with: are you willing to write code, can you stage or preprocess data, etc.

Thanks.

|||Actually I've already gotten the data divided up through a script. Currently, I have 65,000 rows being sent to one Excel file, and all the excess rows offset to a temporary flat file. Then, I reiterate and draw another 65,000 lines out of that flat file and store it into a new Excel file. So basically I have everything set up, and I have multiple Excel file outputs, but I would prefer to combine all of this into a single Excel file with multiple worksheets. Is there an easy way to do this?

Thank you for the help.
|||

That should not be too hard: instead of generating new Excel files, per iteration, create sheets in the existing file. Use Execute SQL Task to create those sheets (DDL example: create table `<sheet_name>` (`<col1> VarChar[50], ...) ).

Let me know if you need more assistance.

|||

Hi Allen,

Myself facing similar problem wants to know how you achieved this task. Had you been successful achieving this task. Is it possible to post me lines of code or atleast provide the url which has:

1) Code that estimates the size of the data and divide the data based on size

2) Code that talks to Jet Provider for Excel directly to create and populate the worksheets using loop.

Many thanks

Subhash Subramanyam

|||It took me a few days of playing around in SSIS before I modified the process for my personal situation, but most of the methodology I got from here:
http://blogs.conchango.com/jamiethomson/archive/2005/12/04/SSIS-Nugget_3A00_-Splitting-a-file-into-multiple-files.aspx

I have no idea what Jet Provider is. Sorry I'm still very new to data manipulation.

If you have any other specific questions about how I modified the process for my own situation, feel free to ask after checking out that blog entry.

Export to Multiple Excel Sheets

I saw a post with this same subject line, posted in July of 2006, but with no replies. I am now having precisely the same problem.

I am importing data from an OLE DB source. I want to directly store this data in an Excel file. There are far more than 65,536 rows in the DB table, but the version of Excel I have only tolerates a maximum of that many. My solution is to divide the data into separate worksheets within the same Excel file. At any given time, I do not know exactly how many rows are moving from the database to the Excel file, so is there a way to dynamically create a new worksheet every time I reach 65,536 rows?

Thank you.

Hi Allen,

there is no an easy way to do this.

I can give you a couple of not-so-easy options though:

1. Build a script or custom destination that will know how to split data, create and load sheets. You will need to talk to the OLE DB JET provider directly.

2. Do it in two passes:

- First pass; partiotion data by adding a new column to identify divided partitions and stage the new data in a table or flat file.

- Second pass; Build a package to find out how many partitions there is, create the destination sheets, and run a loop with number of iterations equal to number of partitions created in the previous step. In each iteration, run a child package that can filter data for its partition ans load it into an appropriate Excel sheet.

I am sure there are additional approaches to implement this. It depends on restrictions you are bound with: are you willing to write code, can you stage or preprocess data, etc.

Thanks.

|||Actually I've already gotten the data divided up through a script. Currently, I have 65,000 rows being sent to one Excel file, and all the excess rows offset to a temporary flat file. Then, I reiterate and draw another 65,000 lines out of that flat file and store it into a new Excel file. So basically I have everything set up, and I have multiple Excel file outputs, but I would prefer to combine all of this into a single Excel file with multiple worksheets. Is there an easy way to do this?

Thank you for the help.
|||

That should not be too hard: instead of generating new Excel files, per iteration, create sheets in the existing file. Use Execute SQL Task to create those sheets (DDL example: create table `<sheet_name>` (`<col1> VarChar[50], ...) ).

Let me know if you need more assistance.

|||

Hi Allen,

Myself facing similar problem wants to know how you achieved this task. Had you been successful achieving this task. Is it possible to post me lines of code or atleast provide the url which has:

1) Code that estimates the size of the data and divide the data based on size

2) Code that talks to Jet Provider for Excel directly to create and populate the worksheets using loop.

Many thanks

Subhash Subramanyam

|||It took me a few days of playing around in SSIS before I modified the process for my personal situation, but most of the methodology I got from here:
http://blogs.conchango.com/jamiethomson/archive/2005/12/04/SSIS-Nugget_3A00_-Splitting-a-file-into-multiple-files.aspx

I have no idea what Jet Provider is. Sorry I'm still very new to data manipulation.

If you have any other specific questions about how I modified the process for my own situation, feel free to ask after checking out that blog entry.

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 Excel, naming of worksheet

I have a report, which when I do Export to excel it comes in excel in multiple worksheets(because I ahve page breaks in my report), Now I want to name each worksheet programatically and not manually, Any idea how we can name the worksheet through reporting services, so that on export to excel I will get the work sheets with my preferred names

This is an often requested feature however it's not supported by RS.

The only time you get a named worksheet is when you export a single page report the worksheet name will be the report name.

|||Are there any round abouts or alternatives for this?|||

Short Answer is: No.

Long Answer is:

For a solution integrated into RS UI you can write your own rendering extension in which you would have to re-code all of Microsofts logic for export to excel and extend it to apply your desired worksheet names.

For an offline solution you could just write some code that calls the web service to export your report to excel and then modifies the xls to have your desirede worksheet names

|||

I can't believe someone just happened to ask this yesterday. I've been using 2005 reporting services for nearly a year now, and TODAY for the first time (after having created hundreds of reports) I just happen to need to create a report that will run every Monday from now on that populated the worksheet tabs with identifying labels.

Looks like I'll be typing them in manually for a while.

Export to Excel, naming of worksheet

I have a report, which when I do Export to excel it comes in excel in multiple worksheets(because I ahve page breaks in my report), Now I want to name each worksheet programatically and not manually, Any idea how we can name the worksheet through reporting services, so that on export to excel I will get the work sheets with my preferred names

This is an often requested feature however it's not supported by RS.

The only time you get a named worksheet is when you export a single page report the worksheet name will be the report name.

|||Are there any round abouts or alternatives for this?|||

Short Answer is: No.

Long Answer is:

For a solution integrated into RS UI you can write your own rendering extension in which you would have to re-code all of Microsofts logic for export to excel and extend it to apply your desired worksheet names.

For an offline solution you could just write some code that calls the web service to export your report to excel and then modifies the xls to have your desirede worksheet names

|||

I can't believe someone just happened to ask this yesterday. I've been using 2005 reporting services for nearly a year now, and TODAY for the first time (after having created hundreds of reports) I just happen to need to create a report that will run every Monday from now on that populated the worksheet tabs with identifying labels.

Looks like I'll be typing them in manually for a while.

Export to Excel -multiple sheets

Hi,

I am using .rdlc report in ASP.NET 2.0.

My report consists of 10 subreport and 1 main report. I want to export each subreport in separate sheets like Shee1 is Subreport1 and Sheet2 is Subreport2 etc... How can i do this.

Thanks.

The Excel renderer creates new worksheet tabs for each page in your report. To get a new page for each Subreport, put each of them inside a Rectangle and set PageBreakAtStart on the Rectangle to True.

-Chris

|||

I haven't been able to get the sub reports to show when exported to excel. Is there a trick to this?

Dwayne

|||

This works for me. However, what does not work, is using a subreport in a cell of a table and export that to Excel.

Regards, Jeroen

Export to Excel -multiple sheets

Hi,

I am using .rdlc report in ASP.NET 2.0.

My report consists of 10 subreport and 1 main report. I want to export each subreport in separate sheets like Shee1 is Subreport1 and Sheet2 is Subreport2 etc... How can i do this.

Thanks.

The Excel renderer creates new worksheet tabs for each page in your report. To get a new page for each Subreport, put each of them inside a Rectangle and set PageBreakAtStart on the Rectangle to True.

-Chris

|||

I haven't been able to get the sub reports to show when exported to excel. Is there a trick to this?

Dwayne

|||

This works for me. However, what does not work, is using a subreport in a cell of a table and export that to Excel.

Regards, Jeroen

|||Hi, related to Jeroen's Question...
"This works for me. However, what does not work, is using a subreport in a cell of a table and export that to Excel."

As a solution for this problem, we can have a rectangle within the table and place the subreport inside it, it allows to export. There is again few limitaions further but at least we can export the data of the subreport. Hope this helps.

Export to Excel -multiple sheets

Hi,

I am using .rdlc report in ASP.NET 2.0.

My report consists of 10 subreport and 1 main report. I want to export each subreport in separate sheets like Shee1 is Subreport1 and Sheet2 is Subreport2 etc... How can i do this.

Thanks.

The Excel renderer creates new worksheet tabs for each page in your report. To get a new page for each Subreport, put each of them inside a Rectangle and set PageBreakAtStart on the Rectangle to True.

-Chris

|||

I haven't been able to get the sub reports to show when exported to excel. Is there a trick to this?

Dwayne

|||

This works for me. However, what does not work, is using a subreport in a cell of a table and export that to Excel.

Regards, Jeroen