Any tips for getting fields to align when making an SSRS to be exported to Excel?
(and )
Trying to get rid of the narrow “blank” columns that are introduced when fields don’t perfectly align.
I’m using the guidline that shows up, to snap the header field to the tablix column edge, but it still creates the mis-aligned fields, which create extra columns.
In the following pict I’m having a hard time aligning the three header fields (in yellow), with the tablix column (in blue).
The header fields each occupy two columns (K and L), while the tablix actually skips a coulmn (K).
“Page” is in merged cells K1…L1
“Date” is in merged cells K1…L1
“Time” is in merged cells K1…L1
Tablix column “Region” is in column J
Tablix column “Ship St” is in column L
Manually calculating where the left edge of a tablix column (by adding up all the preceding widths), and manually entering it for the location of the header field seems to work, but that’s an unrealistic way to do it.
Yeah, I would either strip out all headers, or make them the same size/position as your details.
Even then, SSRS export to Excel is hit an miss - not just for us, there are a LOT of threads out there on the net discussing the agonies associated with getting a clean Excel export out of SSRS.
Here’s a screen shot with some approaches I’ve tried… where I have gotten close but there always seems to be something not quite right.
I prefer to put everything into a tablix and have columns for each item. When I make it look like Excel, it exports to Excel very well!
This means that I don’t put textboxes floating around in a rectangle…
The excel rendering of SSRS is what is making those merged cells.
If I had three fields in the report like:
The excel file would render with 7 columns ( thru G).
Column A is from the left edge of the report to the left edge of Name
Column B is in line with the field Name
Column C is from the right edge of Name to the left edge of Date
Column D is in line with the field Date
Column E is from the right edge of Date to the left edge of Amount
Column F is in line with the field Amount
Column G is from the right edge of Amount to the left edge of of the report
If I add field Description (and it aligns perfectly with Name and Date), I still get 7 columns. With field Description spanning columns B thru D, as merged cells.
If the description field was a little too narrow, like
Its more complex than that. I would use Tablix period, dont waste your time.
But the other factor is if you want Excel to be nice stop using inches, convert everything to metric when I made everything in cm, mm, where its base 10 - I had even more success with rectangles. Then I also removed padding from the boxes. Somehow the math and division the Engine uses, loves base 10.
As for the heading, you will have to align it to consume space as the next 5-6 body boxes or so, or get rid of it, I never found a nice way to get that to work.
Try putting in your heading into a wrapper rectangle
Most of the time I use “Excel (Data Only)” and it’s not an issue at all. But some users want the page header info, as it contains fields like the date and time it was run, options selected at run time, etc…
Not 100% true, but it is too much work for sure. Expand each textbox so that is is the same width and is located with the same “left” property and it often comes out right.
Another option you could do is to put the header on one tab and the data in a second tab in the Excel file. I personally don’t think this is a good way to do it, but it would be a quick and easy way to deliver the data.
