MS SQL Server Reporting Services - RE: Missing column header row when exporting to Excel

Author

RE: Missing column header row when exporting to Excel

Sophie Guo [MSFT]

2005-03-30, 9:42 am

Hello Sherman,

I have tested the issue on my side. The results is the table header appears
once on the first page of the Excel sheet whether I have one group or two
groups.

Can you reproduce the issue on the SampleReports which reporting services
has installed? If so, please post here the detailed steps to reproduce the
issue and I will check it on my side. Also, please send the generated Excel
file to me for research at v-sguo@Microsoft.com

For your reference, I have tested the issue on the SampleReports by
performing the following steps:

1. Create a new blank report in the SampleReports project in Visual Studio
.NET 2003
2. Create a new dataset using the following SQL statement in Data tab:

SELECT AddressID, AddressLine1, City, PostalCode, Phone, StateProvinceID
FROM Address
WHERE (AddressID BETWEEN 1 AND 150)

3. Create a new table in the body of the report in the Layout tab window.
Drag and place the field of the dataset into the table.

4. Open the table properties window and select "Repeat header rows on each
page".

5. Preview the report to verify that the table header appears on each page.

6. Right-click the table Detail icon and insert a new Group. Select
"=Fields!City.Value" from the drop down list in the "Group on" section in
the "Grouping and Sorting Properties" window to group by the City.

7. Place the city field in the Group header row.

8. Preview the report and export the report to an Excel file.

The result is the table header appears once on the first page.

9. Create another Group to group by PostalCode.

10. Preview the report and export the report to an Excel file.

The result is the table header appears once on the first page.

In addition, after adding a page break to a grouping in a table, you can
export report to multiple worksheets in Excel. However, only the first
sheet has the table header. There is no resolution for this issue at
present.

I look forward to hearing from you.

Sophie Guo
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security

====================
====================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================
====================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Sherman In Denver

2005-03-30, 7:04 pm

Hi Sophie,
Thanks again for responding. Fortunately, I believe I've fixed the problem.
Hopefully, I can explain this in a manner that makes sense.

My report was setup with a Page Header, Page Footer and 1 table. In addition
to the Table Header and Footer rows, the table also had 2 groups both with
headers and footers.

Now in order to get the page header to appear on every page I used the
hidden table method where you place the page header information in the table
header and that, in turn, references text boxes in the page header.

This is what was causing the table header row to be missing in the excel
spreadsheet. Now usually I would put the hidden page header tables in the
tableheader row but with these particular reports I had to put them in the
first group header row so that the page header information (which was an
account name / number) would change at the end of every account. The first
group was setup to group by account name and number. The second group was
setup to group by a security or holding within the account.

To fix the problem, I removed the pageheader tables from the first group
header (account name / number) row and pasted them in the second group header
(holding / security) row. Then I deleted the first group header row and ran
the report. Problem fixed.

I wish I could give you a logical reason why this fixed the problem but I
can't. But it appears that there is a way to get the table header to print
on multiple sheets when exporting to Excel.

I will send you the excel document for your review.

"Sophie Guo [MSFT]" wrote:

> Hello Sherman,
>
> I have tested the issue on my side. The results is the table header appears
> once on the first page of the Excel sheet whether I have one group or two
> groups.
>
> Can you reproduce the issue on the SampleReports which reporting services
> has installed? If so, please post here the detailed steps to reproduce the
> issue and I will check it on my side. Also, please send the generated Excel
> file to me for research at v-sguo@Microsoft.com
>
> For your reference, I have tested the issue on the SampleReports by
> performing the following steps:
>
> 1. Create a new blank report in the SampleReports project in Visual Studio
> .NET 2003
> 2. Create a new dataset using the following SQL statement in Data tab:
>
> SELECT AddressID, AddressLine1, City, PostalCode, Phone, StateProvinceID
> FROM Address
> WHERE (AddressID BETWEEN 1 AND 150)
>
> 3. Create a new table in the body of the report in the Layout tab window.
> Drag and place the field of the dataset into the table.
>
> 4. Open the table properties window and select "Repeat header rows on each
> page".
>
> 5. Preview the report to verify that the table header appears on each page.
>
> 6. Right-click the table Detail icon and insert a new Group. Select
> "=Fields!City.Value" from the drop down list in the "Group on" section in
> the "Grouping and Sorting Properties" window to group by the City.
>
> 7. Place the city field in the Group header row.
>
> 8. Preview the report and export the report to an Excel file.
>
> The result is the table header appears once on the first page.
>
> 9. Create another Group to group by PostalCode.
>
> 10. Preview the report and export the report to an Excel file.
>
> The result is the table header appears once on the first page.
>
> In addition, after adding a page break to a grouping in a table, you can
> export report to multiple worksheets in Excel. However, only the first
> sheet has the table header. There is no resolution for this issue at
> present.
>
> I look forward to hearing from you.
>
>
> Sophie Guo
> Microsoft Online Partner Support
>
> Get Secure! - www.microsoft.com/security
>
> ====================
====================
=============
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ====================
====================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>

Sophie Guo [MSFT]

2005-03-31, 7:02 am

Hello Kevin,

Thanks for your reply and the additional information.

However, based on the information you provided, I am unable to reproduce
the issue on my side. Can you post here the detailed steps to reproduce the
issue on the SampleReports project?

If possible, please use adventure2000 database as data source and send us a
sample report/dataset definition

I look forward to hearing from you.

Sophie Guo

Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security

====================
====================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================
====================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Sponsored Links