Libre Base Report Formatting - How Can I Stop A Group Header From Showing Up On Last Page Of A Report?

Hi,
I am having an issue with the Libre Base’s report builder. I’m trying to create an itemized work order report and I want the labels to show on the top of each page. I got that working however, there is one side effect… If the total will not fit on the same page as the items, the labels will show on the next page along with the total, which looks awkward… Is there anyway to stop this?

I tried using a report footer instead of a group footer and ended up having a new page generate whether there was room for the total or not. I want the total to be on the same page as the items if there is room as this wastes paper.

This is my report layout: Report Layout.PNG.

This is the issue I notice when I generate it: Work_Order_Report.odt

This is my report layout using a report footer instead: Work_Order_Report.odt

This is the result. As you can see, it pushes it onto a new page despite it not being necessary: report footer test.PNG

Here is my ODB file: Work Order.odb

Hello,

A Report Header/Footer is the way to do this. Make certain on the Report Footer properties the item Force New Page is set to None.

If there is enough room on the page it will print at the end. If it needs a new page, the Page Header will print but not the Group header.

Thank you for your quick response. I tried that. However, it is still force the footer onto a separate page despite it having room on the previous page. I added two more attachments to my answer showing what happens when I use the report footer instead. Is there anything else I can try?

My testing shows this to work. Do not know what you are actually using in respect to settings.

Attachments of this type are of little help in many cases. What you show is the output. Need a sample of the Base file - no personal confidential information.

Edit:

With some further testing, it may be a setting in Group Header - Repeat Section = Yes causes some issues.

Interesting. I did some testing and when I deleted the group header, the report footer was on the correct page. So, it definitely is something about the group header that causes the issue. However, turning off the repeat section, interestingly enough, put the group header on the first page and the items on the second page…

Anyway, I have attached my odb file to my question, if you want to take a look at it.

There is a bug somewhere in there but have not isolated it. Not certain I will be able to either.

Have found other problems with your report. Your grouping possibly should be on Customer_ID not Product_ID (although there may be some reason for Product_ID I do not see). And not certain why you group on interval (set at 100). Keep together only works within Sorting and Grouping.

Probably can overcome your problem heading with some conditional print statements. Have done some of this in the past but it can be a headache.

Ok, thank you for your time. I have created a macro work around.

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