Reporting Services may fail in AutoGrow property in Excel Rendering with Merged Cells

Microsoft Reporting Services enables developers to render their reports in different file formats such as Excel file format or PDF file format without writing any lines of code.

But from the beginning of the SQL Reporting Services, Excel rendering has always some problems which are fixed with hotfixes or service packs. For instance, Excel rendering was a problem if the file size is huge. But these are fixed now.

But we have still some problems while exporting SQL Server Reporting Services reports to Excel file format. One of them that I have experienced recently is the auto-resizing of cell heights in Excel files. So all the string information is not showed contained within the cell.

In one of the reports rdl file you can see its design layout below, I had placed the report's title in the page header of the report. Also the date when the report is generated can be seen on the right part of the page header.

I have also some information displayed on the page footer of the report layout.

The main report data is of course placed in the body part of the report. The data is displayed via a table. Just above the table, you can see the report parameters entered by the user who is running the report are displayed.

This report layout has no problems with HTML, PDF, etc file formats except EXCEL format. You can see the right outer most table column which displays a Note field. You can guess that Note field can include a long data that can not easily displayed in one line.

So we can set the CanGrow and CanShrink properties of the table cells in order to display all note information visible on the resultant report file. If you set the CanGrow property to True and then run the report, the Note field is totally displayed on the HTML file format. But when I exported this file to EXCEL, I realized that although the cell includes all information, the excel cell's height is resized and shows only a portion of the data. For example for my report, only at most three lines of note data was displayed. After rendering to Excel, in order to see all note information, or all data displayed on any row of the table, the user must manually resize the excel row heights.

You can understand how it is annoying if you have many rows of data in the resultant Excel report file.

It is interesting that CanGrow works successfully with the HTML file format. On the other hand, Excel rendering fails to display report data by auto sizing the cells or rows in order to show all data as it opens.

Reporting Services unfortunately fails with CanGrow property if there are merged columns in the table object. Also information displayed on the report header can cause the same problem.Since report footer is not rendered and displayed during Excel rendering process, it has no effect on this problem.

So, in order to summarize how I solved my problem:

First, I resized the table to cover from the left outer most of the report to the right outer most of the report.

Then, I remove all space above the table object to the table header and removed all space below the table object to the table footer.

I removed table header from the report layout. Instead of displaying report header and report process time on the header, I added a second table header row above the column names and merged cells to display report header and report process time. If you look at the below figure, you can see the name of the report on the top row. On the right side of the row there is two cells merged in to one to display expression  "=Today"

The last step is just a similar step which I used to display the report parameters information on the report to the users. You can also see that on the above picture. I had technician and date criterias for my report, and I chose to display parameters in the table header in a newly added row just as I did for the report name.

You can distinguish the report header rows from report detail records by a careful glance at the icons displayed on the left side of the table.

I find it impossible to keep report title in the header and justify the sizes and positions of the textboxes so I had to move information on the report header to the table header.

Below you can see the final report design.

And the final report output after it is exported to Excel

In short, during the Excel rendering of SQL Server Reporting Services reports, you can face problems like auto resizing of Excel cells which discards AutoGrow property of the table cells in the design layout.

To overcome this problem, re-design your reports, be careful with merged cells and report headers.

BlinkList   Del.icio.us   Digg   Furl   Simpy   Spurl   DZone   ma.gnolia   Shadows