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:
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):
And how many we need to add to push the payment details section to the bottom of the next page (r2 + r3):
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…..
How Artificial Intelligence and Data Add Value to Businesses
Knowledge is power. And the data that you collect in the course of your business
May
Databricks Vs Synapse Spark Pools – What, When and Where?
Databricks or Synapse seems to be the question on everyone’s lips, whether its people asking
1 Comment
May
Power BI to Power AI – Part 2
This post is the second part of a blog series on the AI features of
Apr
Geospatial Sample architecture overview
The first blog ‘Part 1 – Introduction to Geospatial data’ gave an overview into geospatial
Apr
Data Lakehouses for Dummies
When we are thinking about data platforms, there are many different services and architectures that
Apr
Enable Smart Facility Management with Azure Digital Twins
Before I started writing this blog, I went to Google and searched for the keywords
Apr
Migrating On-Prem SSIS workload to Azure
Goal of this blog There can be scenario where organization wants to migrate there existing
Mar
Send B2B data with Azure Logic Apps and Enterprise Integration Pack
After creating an integration account that has partners and agreements, we are ready to create
Mar