Prev: Reporting Services SUM(IIF(...) Problem
Next: reporting options in SQl Server
From: Linn Kubler on 19 Mar 2010 11:36
Hi,

Running Reporting Services from SQL 2005 Server and my users like to export
to Excel so that they can play with formatting, sorting and what-not. We
have run into a problem where the export process merges cells in Excel and
then you have to go through this annoying process to unmerge the cells so
you can sort the data.

Question is, is there any way to control the way RSS exports to Excel to
stop the merging of cells? My guess is they do it to preserve the column
alignments in Excel but for the most part it really isn't necessary. Is
there something different I can do with the report layout that might correct
this?

Thanks in advance,
Linn


From: Andrew Karcher [SQL] on 19 Mar 2010 12:34
Linn,

This does get better in SQL 2008 and 2008 R2, but you can still run
into some problems. There is no one solution for this, but there are
some things that you can do to minimize when this happens.

Typically the biggest thing that causes this is headers, titles, text
boxes, and footers in the report. When your headers end in the middle
of a table column below SSRS will split that Excel column. So one
thing that I typically do is to make any header items span the entire
width of the report. This way they will not split any cells in the
table.

Another thing that causes issues is if you have multiple tables in the
report. If you have multiple tables in the report and the column
edges do not match up you will get splitting in Excel. So the columns
have to line up perfectly to prevent split columns in Excel.

In some cases I have even gone to the extreme of adding a parameter to
the report that is "Format for Export" or something like that. Then I
will conditionally hide any items on the report that I do not want
exported such as headers, footers, etc. This gives you a lot of
flexibility and control, but is a bit more work.

In 2008, there seems to be less problems with the splitting of cells,
but you can still have problems. In 2008 R2, there is a new Global
Parameter called "RenderFormat" that you can interpret and do
conditional formatting based on the format that the report is being
rendered in.

Hope that Helps,
Andrew Karcher
SQL Server MVP

On Fri, 19 Mar 2010 10:36:26 -0500, "Linn Kubler"
<lkubler(a)chartwellwisc2.com> wrote:

>Hi,
>
>Running Reporting Services from SQL 2005 Server and my users like to export
>to Excel so that they can play with formatting, sorting and what-not. We
>have run into a problem where the export process merges cells in Excel and
>then you have to go through this annoying process to unmerge the cells so
>you can sort the data.
>
>Question is, is there any way to control the way RSS exports to Excel to
>stop the merging of cells? My guess is they do it to preserve the column
>alignments in Excel but for the most part it really isn't necessary. Is
>there something different I can do with the report layout that might correct
>this?
>
>Thanks in advance,
>Linn
>
From: Bruce L-C [MVP] on 22 Mar 2010 11:10
I do another design trick. I remove the title of the report and add
additional rows to the table object (above the data). Then I merge the cells
and put the title there. This does the same thing as what Andrew mentions
(aligning up) but without the headache. This takes care of the merged cell
problem in Excel export.

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

"Linn Kubler" <lkubler(a)chartwellwisc2.com> wrote in message
news:OEPoxn3xKHA.2012(a)TK2MSFTNGP04.phx.gbl...
> Hi,
>
> Running Reporting Services from SQL 2005 Server and my users like to
> export to Excel so that they can play with formatting, sorting and
> what-not. We have run into a problem where the export process merges
> cells in Excel and then you have to go through this annoying process to
> unmerge the cells so you can sort the data.
>
> Question is, is there any way to control the way RSS exports to Excel to
> stop the merging of cells? My guess is they do it to preserve the column
> alignments in Excel but for the most part it really isn't necessary. Is
> there something different I can do with the report layout that might
> correct this?
>
> Thanks in advance,
> Linn
>
>
 | 
Pages: 1
Prev: Reporting Services SUM(IIF(...) Problem
Next: reporting options in SQl Server