What I ended up doing to fix this issue was creating two copies of the report. One with the report footer and the other with the group footer since between the two of them it worked correctly.
Then I created a macro to choose the correct report based on the circumstance. First, I figured out how many characters I could add to my report’s comment section (In my case: 14 Capital Ws = 1 row). Then I used: https://codepen.io/8legged/pen/qqKoKL to measure all of the characters of the font I used (Arial) and I used it to measure how many pixels the 14 Ws took up.
For each record in my report, I had my macro use this data to calculate how much space each record took up and divided that by the maximum space allotted per row (the size of the 14 Ws which is 218 pixels, in my case). This breaks each record up into rows or lines if you will. I have the macro round them up to remove the decimal places.
I added all of the rows into a master count I called RowCount. Then I accounted for the size of each row.
In my case, a record with just one row of text is 0.25 inches (I got this number from the Report Builder by selecting the details section of my report). Each new row of text in a record adds 0.20 inches to the overall size (As that is how big one line of text is). So, a single row record would be 0.25 inches while a double row record would be 0.45 inches and a triple row record would be 0.65 inches, etc.
Then I figured out how much space I could occupy with records before the footer would no longer be on the same page and glitch on the group footer page. In my case this figure was 2.95 inches. I also figured out how much space I have on a page for records. In my case it was 5.6 inches.
Using this information, this macro takes a guess at which report should be used and opens it.
Complex, but it solved my issue.
Sub Report_Selector
REM Counts the number of records that will be displayed in the report
SQL_Comment_Count = "SELECT Count(""ID"") FROM ""Work_Order_Itinerary"""
REM Selects all of the records
SQL_Report_Check = "SELECT ""Comment"" FROM ""Work_Order_Itinerary"""
oQuery = oConn.createStatement()
Comment_Count = oQuery.executeQuery(SQL_Comment_Count)
Comment_Count.next
REM Number of Records
CommentCount = Comment_Count.getInt(1)
REM Creates a list for the records
Dim Comments(CommentCount)
Report_Check = oQuery.executeQuery(SQL_Report_Check)
Report_Check.next
Count = 0
Count2 = 1
REM Loops through the records adding them to the list
Do While (Count <> CommentCount)
Comments(Count) = Report_Check.getString(1)
Count2 = 1
Total = 0
REM Selects each character in the record
Do while (Count2 <> CharacterCount+1)
Character = Mid(Comments(Count), Count2, 1)
REM Assigns each character a pixel value and adds it to the total
Select Case Character
Case ",", ".", "'", "|"
Total = Total + 2
Case "!", ";", ":"
Total = Total + 3
Case "i", "l", " ", "`", "[", "]"
Total = Total + 4
Case "I", "j", "{", "}", "^"
Total = Total + 5
Case "f", "r", "t", "(", ")", "-", "*", "/", "\", "<", ">", "+", "="
Total = Total + 6
Case "J", "s", "1"
Total = Total + 7
Case "c", "e", "o", "x", "z", "_"
Total = Total + 8
Case "a", "b", "d", "F", "g", "h", "k", "n", "p", "q", "u", "v", "y", "Z", "2", "3", "4", "5", "6", "7", "8", "9", "$", "#", "~", "?"
Total = Total + 9
Case "B", "E", "L", "P", "R", "S", "0", "&"
Total = Total + 10
Case "C", "D", "G", "K", "Q", "T", "Y", "%", "@"
Total = Total + 11
Case "A", "H", "N", "O", "X", "U", "V", "w"
Total = Total + 12
Case "M"
Total = Total + 13
Case "m"
Total = Total + 14
Case "W"
Total = Total + 16
Case Else
MsgBox "Error"
End Select
Count2 = Count2 + 1
Loop
REM Divided the total number of pixels by the number of pixels allowed in each row
Rows = Total / 218
REM Rounds this value
RowsRounded = CInt(Rows)
REM Accounts for rounding error
if (RowsRounded < Rows) then
RowsRounded = RowsRounded + 1
End if
Adds the number of rows the record had to the total number of rows in the report
RowCount = RowCount + RowsRounded
Count = Count + 1
Report_Check.next
Loop
REM Uses the total row count and the number of records to calculate how much space will be used
Offset = (RowCount * 0.20) + (CommentCount * 0.05)
REM Accounts for multiple pages
if (Offset > 5.6) then
Offset = Offset / 5.6
RoundOffset = CInt(Offset)
Offset = Offset - RoundOffset
if (Offset > 0) then
Offset = Offset * 5.6
Else
Offset = Offset + 1
Offset = Offset * 5.6
End if
End if
REM Based on the amount of space used, it will select the appropriate report
Select Case Offset
Case 0 to 2.95
ReportName = "Work_Order_Report"
Case 2.95 to 5.6
ReportName = "Work_Order_Report - Extra page"
Case Else
MsgBox "Error"
End Select
REM Opens report
ocontroller = Thisdatabasedocument.currentController
Report = Thisdatabasedocument.reportdocuments.getbyname(ReportName).open
End Sub
