Hanho Hanho - 5 months ago 121
SQL Question

SSRS Reports - force table to expand to bottom of page

I'm trying to create a invoice type report where i have a header, main body with a table (which includes a totals section) and footer.

The problem im getting is, the table height in the main body depends on how many rows are returned from my SPROC, hence if there is not much data, the table will take up a small portion on the middle of the page with the "totals" and "disclaimer" ending nowhere near the bottom of the page (ideally, want to put it just above the footer).

I have seen guides to get around this problem, with the general method to add blank (null) lines in the SPROC, forcing the table to be bigger than expected and thus forcing the totals and disclaimer row to be near the bottom of the page.

I have implemented this solution but there are a few problems with this.

However the problem with this method is that the logic only works assuming each line in the table only takes up 1 line (i.e. a short description so the line does not overflow to the next line). Once the row has multiple lines, the height of the row changes, and since the height is not a multiple of 1 line (i.e. single row is size 1, double row should be size 2 but is instead, size 1.5 or something), i cant take into account how many rows i should add in the SPROC.

I have tried another method where the rows i need are separated from the main table (which gives me problems in itself - cant calculate totals in the report but i guess i could go around this by calculating the total in the SPROC itself) and are hidden until the last page.

This method would be good except that with this method, the blank space is always showing up, stopping the table from expanding to that area.

I have included a link to imgur to describe my current problem which should be easier to understand.




There must be an easier way to just force the table to take up the whole space.

Answer

In the end I've settled for a solution which is very close to what I need and involves in using hidden elements. (similar to what Dan Andrews suggested - but catered to what I needed)

So first of all, I have included the totals in the footer of the report so that it stays at the bottom all the time.

This is shown below:

What the report looks like

Within the subtotals footer, I have placed a message "Continued on next page" which is also a hidden field - this is so I can show this message on any reports that have more than 1 page showing (hence the user knows there's more than 1 page for the report and so the blank space doesn't look as bad).

To hide the totals field, I have the following expression in the "hidden" property:

=iif(Globals!PageNumber=Globals!TotalPages,false,true)

And for the "continued" field:

=iif(Globals!PageNumber=Globals!TotalPages,true,false)

Now the problem with this is that the footer does not know what the tallied up values are from the table due to the footer not having access to the table in the report body.

To get around this issue, I have created a "totals" section which is part of the table that does all the calculation I need to show on the footer.

I put a name for each of the text boxes that I need access to in the footer like so: Setting names for text boxes

And on the corresponding footer element, I have the expression like so: Setting up the expression

Now that the footer contains the totals, the totals field is always shown at the bottom regardless of how big the table grows (which was my initial problem - the footer being placed wherever it wanted to go) with a small trade off of having a blank space on any pages that's not the last. I have put in a "continued on next page" message there instead which shows that there are more pages to the report and so it looks like the white space is being used.

This is a single page example:

Single page example

And this is a multi page example:

Multi page example