SSRS Reports - force table to expand to bottom of page SSRS Reports - force table to expand to bottom of page sql sql

SSRS Reports - force table to expand to bottom of page

All of this can be achieved in the report design itself. Here's what you know:

  • The height of the page (P)
  • The height of your static data (S)
  • The height of your header (H)
  • The height of your data row (R)
  • The count of data rows (C)
  • The height of your footer (F)

So you can work out how big the remaining space is on the page: P - ((C*R) + S + F + H)

When I was working through my problem, I realised that I needed two 'spacers' for when the space remaining on the page was too small to fit the footer; spacer one filled in the remainder of the page 1 whilst spacer 2 was the available space on page 2 - P - (S + F + H).

So, you'll need hidden sections in the report to do the height calculations and two detail rows for spacers beneath any other detail rows.

There's a demo solution I created on GitHub which shows this 'pin-to-bottom' feature in action as well as resetting page numbers for groups, data-driven headers/footers, label translation and international formatting (page size, number formats, etc).

For this look at Sales Invoice 5.

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:


And for the "continued" field:


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

You could add the "totals row" and terms to the footer and display the grid if it's the last page of the report as seen here: SSRS show value only on last page in body of report by toggling the "hidden" attribute.