Tuesday, January 4, 2011

Excel merged cell problem with SSRS 2005 .

               Wishing everybody a very happy and enjoyable New Year 2011.

Recently I have got a chance to work on sql server 2005 based reports. My client has 12 countries roll-out of AX 4.0 and use SSRS 2005 for reporting. These reports are purely based on SSRS fundamentals and not the way AX 4.0 SSRS reports are build ie: report  pulls data directly from SQL server.

Now what I wanted to share is an issue that I came across. In SSRS 2005 if you export the report in excel and open. Then you may find that multiple cells overlap. This becomes a problem for end users when they try sorting. Ideally excel should render the data like one column of excel for each column in the report.
I started to resolve this issue in the most ideal way, googling. I have got two links which will help others as well. The following blog post will help understand the actual problem and why it happens. The MSDN article explains about excel rendering.

Current Scenario –

Report has page header and Body section. Check out the image

From the image it is visible that one of the columns in the body section is getting overlapped and two cells are merged. In this situation sorting on columns won’t work. I followed all the sugestions in the blog(links) except using the measurement unit as point.
1.       First I put all the objects table, taxt-boxes in a rectangle object both in header and body.
2.       I corrected the left and right margin.
3.       Since “line” was used I gave it the same length as the width of the report.
4.       Initially the report width was kept as 9.3 inches. In order to make the conversion correct(avoid loss of precision) I made it as 10 inches.Trick is round off the margin(report width).
That’s it and my report renderd perfectly. Hope it helps ….:)

1 comment: