Exporting to Excel

Hello Everyone,
I'm using SSRS 2005 on top of a sql2k database.
When exporting a report from the Report manager web interface into Excel,
the report gets formatted with the cells merged. This causes a problem with
sorting colums since Excel does not like to sort data in merged cells. I
could have my users simply unmerge all cells, but that leaves some emply
colums that have to be deleted.
Is there any way to prevent the cells from being merged after exporting from
the Report manager? I create the reports in Visual Studio 2005, is there some
setting I can change while creating them in VS05 that will prevent this?
Thanks.You've got two choices. The first I present here is from a poster in the
newsgroup. The second is what I do. I (not too surprisingly) prefer the
second method.
>>>>>>>>The Table can be the full width or slightly less
>>>>>>>>of your page. I have best results when it is less
>>>>>>>>than the full width. I don't know if you can have
>>>>>>>>multiple tables in the body and still sort ok - I
>>>>>>>>doubt it. Any/all other objects in the body (logo
>>>>>>>>images, textboxes) MUST be full width - from
>>>>>>>>leftmost edge to rightmost edge. Images can use
>>>>>>>>Sizing = FitProportional to appear ok. If any of
>>>>>>>>those objects in the body is even slightly less
>>>>>>>>than full width (such as two text boxes adjacent
>>>>>>>>to each other on the same row, or a logo in the
>>>>>>>>upper left and the title textbox in the upper
>>>>>>>>right on the same row), I've found that you will
>>>>>>>>get merged cells in Excel and cannot sort. I also
>>>>>>>>tried all kinds of careful alignments of objects
>>>>>>>>(matching the columns in the table, etc) but it
>>>>>>>>still caused merged cells. I don't believe it
>>>>>>>>matters what you have in the page header or
>>>>>>>>footer - I can put multiple textboxes on the same
>>>>>>>>row in my page footer and it works ok.
Hope that helps,
Steve Nyberg
>>>>>>>>
OK, now my method.
What I do is the following. I add additional header rows and then merge
either the whole row together of a couple of cells and then put in what I
normally would have put in a textbox. You would think that merging cells
together this way would cause a problem with merged cells in Excel but it
does not. If you select your header and your data you can sort, while if you
have exported with a textbox above the table you get the problem with merged
cells when trying to sort.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Damon Johnson" <DamonJohnson@.discussions.microsoft.com> wrote in message
news:3CEDDB9B-B8FE-4582-A88A-41A8A403D41B@.microsoft.com...
> Hello Everyone,
> I'm using SSRS 2005 on top of a sql2k database.
> When exporting a report from the Report manager web interface into Excel,
> the report gets formatted with the cells merged. This causes a problem
> with
> sorting colums since Excel does not like to sort data in merged cells. I
> could have my users simply unmerge all cells, but that leaves some emply
> colums that have to be deleted.
> Is there any way to prevent the cells from being merged after exporting
> from
> the Report manager? I create the reports in Visual Studio 2005, is there
> some
> setting I can change while creating them in VS05 that will prevent this?
> Thanks.|||Thanks Bruce. I'll give these a once over.
Damon
"Bruce L-C [MVP]" wrote:
> You've got two choices. The first I present here is from a poster in the
> newsgroup. The second is what I do. I (not too surprisingly) prefer the
> second method.
> >>>>>>>>The Table can be the full width or slightly less
> >>>>>>>>of your page. I have best results when it is less
> >>>>>>>>than the full width. I don't know if you can have
> >>>>>>>>multiple tables in the body and still sort ok - I
> >>>>>>>>doubt it. Any/all other objects in the body (logo
> >>>>>>>>images, textboxes) MUST be full width - from
> >>>>>>>>leftmost edge to rightmost edge. Images can use
> >>>>>>>>Sizing = FitProportional to appear ok. If any of
> >>>>>>>>those objects in the body is even slightly less
> >>>>>>>>than full width (such as two text boxes adjacent
> >>>>>>>>to each other on the same row, or a logo in the
> >>>>>>>>upper left and the title textbox in the upper
> >>>>>>>>right on the same row), I've found that you will
> >>>>>>>>get merged cells in Excel and cannot sort. I also
> >>>>>>>>tried all kinds of careful alignments of objects
> >>>>>>>>(matching the columns in the table, etc) but it
> >>>>>>>>still caused merged cells. I don't believe it
> >>>>>>>>matters what you have in the page header or
> >>>>>>>>footer - I can put multiple textboxes on the same
> >>>>>>>>row in my page footer and it works ok.
> Hope that helps,
> Steve Nyberg
> >>>>>>>>
> OK, now my method.
> What I do is the following. I add additional header rows and then merge
> either the whole row together of a couple of cells and then put in what I
> normally would have put in a textbox. You would think that merging cells
> together this way would cause a problem with merged cells in Excel but it
> does not. If you select your header and your data you can sort, while if you
> have exported with a textbox above the table you get the problem with merged
> cells when trying to sort.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Damon Johnson" <DamonJohnson@.discussions.microsoft.com> wrote in message
> news:3CEDDB9B-B8FE-4582-A88A-41A8A403D41B@.microsoft.com...
> > Hello Everyone,
> > I'm using SSRS 2005 on top of a sql2k database.
> >
> > When exporting a report from the Report manager web interface into Excel,
> > the report gets formatted with the cells merged. This causes a problem
> > with
> > sorting colums since Excel does not like to sort data in merged cells. I
> > could have my users simply unmerge all cells, but that leaves some emply
> > colums that have to be deleted.
> >
> > Is there any way to prevent the cells from being merged after exporting
> > from
> > the Report manager? I create the reports in Visual Studio 2005, is there
> > some
> > setting I can change while creating them in VS05 that will prevent this?
> >
> > Thanks.
>
>