I have noticed that after my report exported to excel, some of the cells are
merged.
For example, I have report as following
ID Project Name Author Description
1 xxxx dfdf fdfdfdfd
after export in the excel, the data shows correctly, but on the top of the
excel file, it may show something like this
A B C D E F
ID Project Name Author Description
1 xxxx dfdf fdfdfdfd
The problem is some of the columns has one column data but excel treat them
as two columns.
Does anyone have any idea? thanks.The Excel renderer will merge cells in order to preserve the report item
alignment you defined when you designed the report.
Merging usually happens when there are report items either below or above
the table whose left or right edges do not exactly match that of the table
cells below.
A classic example of this is a single textbox above a table that functions
as a header. Make sure this textbox starts and ends at the border of one of
the below table's cells.
Best,
-Chris
SQL Server Reporting Services
"Helen" <Helen@.discussions.microsoft.com> wrote in message
news:8EDC7FB7-BC51-473E-A29B-4CDB47E9C4F4@.microsoft.com...
>I have noticed that after my report exported to excel, some of the cells
>are
> merged.
> For example, I have report as following
> ID Project Name Author Description
> 1 xxxx dfdf fdfdfdfd
> after export in the excel, the data shows correctly, but on the top of the
> excel file, it may show something like this
> A B C D E F
> ID Project Name Author Description
> 1 xxxx dfdf fdfdfdfd
> The problem is some of the columns has one column data but excel treat
> them
> as two columns.
> Does anyone have any idea? thanks.
>
Showing posts with label merged. Show all posts
Showing posts with label merged. Show all posts
Sunday, February 26, 2012
Export to Excel causes merged cells?
I have a report that is a table of data... the users export this report to
excel and then pivot the data in the table in order to cut and slice the data
to do analysis. When they go to insert a pivot table off of this report
Excel throws an error saying that the data source is not valid. What I've
noticed is that when reporting services exports the report to excel it
created one very narrow column and merges that column with the the next and
merges the cells in each of the rows below it' Also, even if I remove the
merged or hidden columns I still can't seem to pivot the data unless I do a
copy paste special values into a new workbook... it won't work if I just
paste into a new worksheet in the same workbook'
Any help you could provide would be greatly appreciated!On Mar 15, 12:54 pm, mr.letni <mrle...@.discussions.microsoft.com>
wrote:
> I have a report that is a table of data... the users export this report to
> excel and then pivot the data in the table in order to cut and slice the data
> to do analysis. When they go to insert a pivot table off of this report
> Excel throws an error saying that the data source is not valid. What I've
> noticed is that when reporting services exports the report to excel it
> created one very narrow column and merges that column with the the next and
> merges the cells in each of the rows below it' Also, even if I remove the
> merged or hidden columns I still can't seem to pivot the data unless I do a
> copy paste special values into a new workbook... it won't work if I just
> paste into a new worksheet in the same workbook'
> Any help you could provide would be greatly appreciated!
If you are using multiple tables (one-above-the-other) in a single
column report, I would suggest making sure that the column widths
match exactly between the multiple tables (that are one-above-the-
other). Also, make sure that your report title/independent textboxes
and table widths are the exact same width (i.e., don't have a report
title textbox smaller in width than the table(s) width). Other similar
kinds of nuances should apply as well. It depends on which axis you
see the merged cells on as far as which items need to be expanded to
match widths, etc. Also, make sure that there is no open space below
all items in the report to the bottom report border and no open space
to the right of the report items to the right border of the report.
I've had some very picky clients in regards to merged cell exports to
Excel and this was how I alleviated the merged cells for them. Hope
this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||The main issue I have seen is when I use a textbox at the top of my report
to put my title in. Instead, add additional header rows, then merge the
header row cells that you want to put your title in. When you use a text box
you end up with merged fields that then prevents sorting (or other
operations) on the data in Excel.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"mr.letni" <mrletni@.discussions.microsoft.com> wrote in message
news:4DD288E7-2C95-4A53-BB29-A86283B1C962@.microsoft.com...
>I have a report that is a table of data... the users export this report to
> excel and then pivot the data in the table in order to cut and slice the
> data
> to do analysis. When they go to insert a pivot table off of this report
> Excel throws an error saying that the data source is not valid. What I've
> noticed is that when reporting services exports the report to excel it
> created one very narrow column and merges that column with the the next
> and
> merges the cells in each of the rows below it' Also, even if I remove
> the
> merged or hidden columns I still can't seem to pivot the data unless I do
> a
> copy paste special values into a new workbook... it won't work if I just
> paste into a new worksheet in the same workbook'
> Any help you could provide would be greatly appreciated!
excel and then pivot the data in the table in order to cut and slice the data
to do analysis. When they go to insert a pivot table off of this report
Excel throws an error saying that the data source is not valid. What I've
noticed is that when reporting services exports the report to excel it
created one very narrow column and merges that column with the the next and
merges the cells in each of the rows below it' Also, even if I remove the
merged or hidden columns I still can't seem to pivot the data unless I do a
copy paste special values into a new workbook... it won't work if I just
paste into a new worksheet in the same workbook'
Any help you could provide would be greatly appreciated!On Mar 15, 12:54 pm, mr.letni <mrle...@.discussions.microsoft.com>
wrote:
> I have a report that is a table of data... the users export this report to
> excel and then pivot the data in the table in order to cut and slice the data
> to do analysis. When they go to insert a pivot table off of this report
> Excel throws an error saying that the data source is not valid. What I've
> noticed is that when reporting services exports the report to excel it
> created one very narrow column and merges that column with the the next and
> merges the cells in each of the rows below it' Also, even if I remove the
> merged or hidden columns I still can't seem to pivot the data unless I do a
> copy paste special values into a new workbook... it won't work if I just
> paste into a new worksheet in the same workbook'
> Any help you could provide would be greatly appreciated!
If you are using multiple tables (one-above-the-other) in a single
column report, I would suggest making sure that the column widths
match exactly between the multiple tables (that are one-above-the-
other). Also, make sure that your report title/independent textboxes
and table widths are the exact same width (i.e., don't have a report
title textbox smaller in width than the table(s) width). Other similar
kinds of nuances should apply as well. It depends on which axis you
see the merged cells on as far as which items need to be expanded to
match widths, etc. Also, make sure that there is no open space below
all items in the report to the bottom report border and no open space
to the right of the report items to the right border of the report.
I've had some very picky clients in regards to merged cell exports to
Excel and this was how I alleviated the merged cells for them. Hope
this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||The main issue I have seen is when I use a textbox at the top of my report
to put my title in. Instead, add additional header rows, then merge the
header row cells that you want to put your title in. When you use a text box
you end up with merged fields that then prevents sorting (or other
operations) on the data in Excel.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"mr.letni" <mrletni@.discussions.microsoft.com> wrote in message
news:4DD288E7-2C95-4A53-BB29-A86283B1C962@.microsoft.com...
>I have a report that is a table of data... the users export this report to
> excel and then pivot the data in the table in order to cut and slice the
> data
> to do analysis. When they go to insert a pivot table off of this report
> Excel throws an error saying that the data source is not valid. What I've
> noticed is that when reporting services exports the report to excel it
> created one very narrow column and merges that column with the the next
> and
> merges the cells in each of the rows below it' Also, even if I remove
> the
> merged or hidden columns I still can't seem to pivot the data unless I do
> a
> copy paste special values into a new workbook... it won't work if I just
> paste into a new worksheet in the same workbook'
> Any help you could provide would be greatly appreciated!
Subscribe to:
Posts (Atom)