Jonathan

Jonathan Harding's Blog

Absolute Positioning of Report Elements in SSRS

A client recently asked for an invoice document to be designed using SSRS.  The layout was pretty standard but for one important caveat:  The payment details section had to be in a fixed position at the bottom of the last page of the report, so a 3-page invoice should look like this example:

InvPg1 InvPg2 InvPg3

This could be achieved by putting the ‘Payment Details Section’ fields in the report footer, and setting their visibility based on the report page number. Unfortunately in SSRS there is only one footer for every page and it’s height cannot be varied between pages.  The client did not want to waste space in the report body, which is a side-effect of this method.

So, how to fix the position of a report element to the bottom of the report?

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.  To do this, we need to know how many rows of data can fit on the first page of the report (r1):

InvEq1 

And how many we need to add to push the payment details section to the bottom of the next page (r2 + r3):

InvEq2

Once we have values for r1, r2 and r3, we need to add (r2+r3) additional rows to the invoice lines table on our layout.  These rows must be outside of all groupings on the table as we don’t want them repeated with the groups, we just want them to appear at the foot of the table.  To get the desired effect, we need to control the visibility on these additional rows such that given the total number of data rows in the table, enough additional rows are visible to ‘push’ the payment details section to the bottom of the last page.

To control the visibility of the additional rows, we need to add some code to the report:

   1:  Public Function RowHidden(countRows As Integer, thisRow As Integer) As Boolean
   2:   
   3:          Dim maxRowsPageOne, additionalRows As Integer
   4:          Const TOTALADDITIONALROWS As Integer = 35
   5:          maxRowsPageOne = 12
   6:   
   7:          If (countRows <= maxRowsPageOne) Then
   8:              If (thisRow <= (maxRowsPageOne - countRows)) Then 'Current row is inside visible range for first page
   9:                  Return False 'Show the row
  10:              Else
  11:                  Return True 'Hide the row
  12:              End If
  13:          Else
  14:              additionalRows = TOTALADDITIONALROWS - ((countRows - maxRowsPageOne) Mod TOTALADDITIONALROWS) 'This is key, determines count of rows to make visible
  15:              If additionalRows < TOTALADDITIONALROWS Then
  16:                  If thisRow <= additionalRows Then
  17:                      Return False 'Show the row
  18:                  Else
  19:                      Return True 'Hide the row
  20:                  End If
  21:              Else
  22:                  Return True 'Hide all extra columns
  23:              End If
  24:          End If
  25:   
  26:      End Function

This code needs to be called by every additional row in the invoice lines table using =Code.RowHidden(CountRows(), 1) where ‘1’ represents the number of the particular row, starting from the first additional row after the data rows.

And that’s all there is to it!  It should be noted that this method means the cells in the invoice line table cannot be allowed to grow, or the calculations for the number of additional rows to show will not work.  You also need to be very careful with any future changes to the layout of the report, as these could also affect those calculations.  It’s certainly worth seeing if there can be some compromise on the positioning of report elements to something that SSRS can handle better, but if not, then I hope this is useful!

 

Until next time…..

Comments (2) -

  • Anonymous

    8/30/2013 9:48:07 AM | Reply

    This is not applicable if row can grow, means row height is increase.
    What we can do if height of some rows are increase?

  • jonathan harding

    8/30/2013 10:06:13 AM | Reply

    Hi Kishor,

    You're correct, as noted in the post.  

    If you do have variable row height it's difficult to achieve.  One option would be to work out the length of the cell contents and from that how much height the row will gain. You could then use that information to adjust the calculation for the number of hidden rows to add.

    Thanks for reading!

Loading