Sql Reporting Table Positioning

One of the problems I encountered in creating a Sql Report was in positioning a series of tables. I had a set of 6 tables. One on the left, three in the middle and two on the right. All had different and sometimes expanding heights. The problem is they were not being positioned where I placed them or as I would expect. Sometimes with large spaces in between tables, sometimes perfectly positioned. SSRS does its best to figure out where to put them but its guessing and I think I figured out what it does.

What seems to happen is it creates a grid based on the height of tables going left to right. I had three tables on the top row, with their tops aligned. If the left most table was taller than the middle then the middle two ‘rows’ of tables would have a large blank space between them. The second of those tables appears to have been placed inside a hidden row. If I extended the leftmost table so that its bottom was predefined to be below the top of the middle’s second table then this space was not there but now that invisib le row is defined by the middle rows second interior row of tables which could cause the same sort of issue with the rightmost tables. Its as if it creates two large rectangles to define columns and merges the cells of the middle. Now the problem is as you develop the report the expectations of where things will end up can get weird if you forget or cannot anticipate some of these positioning tricks.

In the below example there appears to be an invisible row that contains the first table on the left, the two middle tables, and the top right most table. The bottom table in the middle has been told that it can leak into the first row by overlapping it with the tables above it but having its top above the leftmost tables bottom. Yeah, its complicated and I fail to carry it through to the last table on the bottom right. The bottom table of the rightmost column cannot leak/overlap and so it sits inside an invisible second row that starts at the bottom of the leftmost table.

Table Positioning

Table Positioning

How can you fix it so you get what you expect? Put in rectangles to define your own grid exactly the way you expect and to disassociate the heights of tables from defining the height of the invisible grid SSRS is using. So my five tables end up in three rectangles. One for each column and these are assumed to all be in the same invisible grid row SSRS will create to contain these rectangles. Now of course within these rectangles SSRS is going to have the same time trying to figure out placement, so you can have your own rectangles to further define positions, if necessary.

Table Positioning

Below you will see on the left the designer for SSRS and the fund fact sheet report. On the right you will see the same report represented in Crystal Reports. The tables I am going for have to be re-positioned to fit and work properly in SSRS, though strictly speaking because they were not working properly in Crystal, being cutoff and ignoring the need to expand them there.

The following shows my final layout for the tables. If you look closely you see three rectangles that define the outermost columns.

Report Table Layout

End Result

So the answer to positioning issues is to use rectangles to separate out the grid system created by various objects from one another and help to control how SSRS defines positions of tables and subreports.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s