export .rdl report into excel 3

[NuJoizey]

I have successfully set up a report using Visual Studion/Reporting services that is grouped company name by page, and then a sub group by location. The report format looks something like:

XXXXXX (Company Name)
YYYYYYY (Location1)
ZZZZZZZZ
ZZZZZZZZ
ZZZZZZZZ

YYYYYYY (Location2)
ZZZZZZZZ


and so the report paginates at every 'XXXXXX' (Company Name) and the report might be 150 pages long.

The problem is exporting to excel - I get a excel workbook that looks great, but it has 150 tabs at the bottom labeled as Sheet1, Sheet2, Sheet 3.......Sheet150.

The requirement is for the sheets to be explitly named whatever 'XXXXXX' (Company Name) is.

Can I do this anywhere in .NET, Reporting Services, or TSQL? If so, where do I look for the objects to manipulate to get it to do this?

 

[xlbo]

You could easily do this in a macro in excel but that would have to be run manually after the event

The only other way I know of to do what you want would be to create a custom rendering assembly that reads the xml from the rdl file and builds the excel file from that - a lot more work to say the least...

AFAIK (and I am happy to be wrong here because it has caused me some issues), RS doesn't have any native functionality to name the sheets appropriately in excel

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[NuJoizey]

xlbo,

yes i thought about adding a simple macro in excel to do it, but how would that work since the report generates a new excel workbook each time - i tried creating book.xlt in C:\Documents and Settings\MyProfile\Application Data\Microsoft\Excel\XLSTART, but didn't work for me, but maybe it's b/c i just didn't try hard enough to get it to work

- is this what you are referring to?

the problem with this is that i'd have to go around to each person who might run this report and add the macro VBA to that individuals machine. what a pain.

 

[xlbo]

yes - it is a pain - possibly less of a pain than creating your own rendering assembly though !!!

A template won;t work unfortunately because the data doesn;t get exported to the template file - just to a normal excel file - don't think it is because you didn;t try hard enough....

What you can do though is create an add-in for excel that can be more easily distributed. This add-in would sit permanently with the users and they could use it to run the macro needed to do the sheet names

To be honest, I'm hoping that someone else comes onto this thread and shoes me a trick that I've been missing...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[JustinEzequiel]

saw this recently but have not tried it yet

http://www.spacefold.com/lisa/post/Changing-the-Sheet-names-in-SQL-Server-RS-Excel-QnD-XSLT.aspx

perhaps you could try it and report back status?

 

[xlbo]

Justin - that looks really useful

Going to try it out later this week as I can see an immediate application for it.....star for the info

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[NuJoizey]

thanks for the replies. The XSLT option sure sounds interesting, but after skimming the articles, i think that for me at least, there is a learning curve involved there, and I need to get a solution in place quickly.

so...I think for the moment I'm going to rely on a book.xls template containing some VBA that i'm just going to dump in my user's
C:\Documents and Settings\%UserName%\Application Data\Microsoft\Excel\XLSTART directory, and then fool with the XSLT at a future time.

I can afford do this b/c right now I don't think there are more than 2 or 3 folks who are going to run this report, so while not perfectly elegant, at least it will be a kickstart.

This thread has definitely given me a good 411 on the whole topic, so for that i am grateful and I'm doling out some stars for you guys....