SSRS Reports - force table to expand to bottom of page

Question

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 1

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.

Answer 2

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:

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:

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

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:

And this is a multi page example:

Answer 3

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.

Answer 4

The basic premise of my solution was to incorporate a ‘hidden’ element into the report body. This element can grow or shrink dynamically to alter the distance between the last of the invoice lines and the payment details section, and thus appears to keep the payment details ‘fixed’ at the bottom of the report....

http://blogs.adatis.co.uk/blogs/harding/archive/2013/02/01/absolute-positioning-of-report-elements-in-ssrs.aspx