SSRS Report Merging in Excel output Format
Designing play an important role while designing SSRS report to the presentation of data in excel to your users. This situation occurs when you have not designed your report properly and there will be column and row merging issue while presenting data to your user in excel output format. Due to this user will not be able perform calculation, filtration and other task on the excel sheet. Text in the columns will wrapped,On the whole we can say presentation of data is not be good.
Let’s see in example below
You have placed multiple text box , images etc in the report header and in report body you placed table ,your table column and report header control (text box , image etc )are not aligned properly then you will see the column and row merging issue. See in the below image column 1, 2, 3 red circled box here report header text box are not properly aligned with report body control i.e table, In red circled box 4 there is space between first and second report header text box .
.RDL Design 1
The output in excel for this type of designed report like below image
See Sale territory column of report occupying two column of excel A,B so forth D,E column and F,G column, row 1 and 2 is overlapped row 4 and 6 is hidden due to this it is difficult to perform filtration, calculation task on the excel to user
Now see the resolution to this situation align the report headers with report body controls properly . see image below, see boxes marked in red 1,2,3 are aligned properly with table columns, in red circled box 4 there is no space between first header control and second control so forth second header control and third header control and third header control and report body control. See .RDL design and .RDL design 2 carefully
.RDL Design 2
Now all our header control are aligned well horizontally and vertically (column, row wise )we can see the output like below image in excel and each report column is occupying one column of excel. Rows 2, 4, 6 are perfectly visible.