Hello @ajlittoz,
This is in answer to your request for an automated script. Well, again Report Builder is not without its’ problems or I am just missing something. But at this point what I have works with its’ own limitations.
As you know, Report Builder outputs a Writer document in Read Only status. The setting you need to change is in the Writer document and I don’t see a way to access it before creation. So in order to have a macro change the necessary settings, the Writer document must be opened. Kind of a catch 22. The solution I came up with is to actually open the report within the macro and then the changes could be made. This means you need to run the macro via a button on a form or even an item on the toolbar or menu and thus generate the report.
Sub SetHeaderAutofit
Dim ocontroller As Object
Dim document As Object
Dim dispatcher As Object
Dim oDoc As Object
Dim oStyleFamilies As Object
Dim oPage As Object
Dim oStandard As Object
ocontroller = Thisdatabasedocument.currentController
if not ocontroller.isconnected then ocontroller.connect
oDoc = Thisdatabasedocument.reportdocuments.getbyname("YOUR_REPORT_NAME").open
document = oDoc.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(document, ".uno:EditDoc", "", 0, Array()) 'Turn off edit mode'
oStyleFamilies = oDoc.getStyleFamilies()
oPage = oStyleFamilies.getByName("PageStyles")
oStandard = oPage.getByName("Standard")
oStandard.HeaderIsDynamicHeight = True 'Sets AutoFit height'
oStandard.FooterIsDynamicHeight = True 'Sets AutoFit height'
oDoc.setModified(0) 'Above settings set document to modified so this turns it off'
End Sub
Now I don’t have this problem on my system so I am not positive it does what you need. I do know it sets AutoFit height correctly.
Edit 12/04/2017:
Almost gave up on this but have a working model for your automated process. You just need to finish the way you want. First, because of automating, the code in the macro must change:
Option Explicit
Sub SetHeaderAutofit
Dim oContext As Object
Dim oRegistered As Object
Dim dbForms As Object
Dim oAConnection As Object
Dim document As Object
Dim dispatcher As Object
Dim oDoc As Object
Dim oStyleFamilies As Object
Dim oPage As Object
Dim oStandard As Object
Dim sReportName As String
oContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")
oRegistered = oContext.getRegisteredObject("REGISTERED_DATABASE_NAME_HERE")
dbForms = oRegistered.DatabaseDocument.ReportDocuments
oAConnection = oRegistered.getConnection("","")
Dim pProp(1) As New com.sun.star.beans.PropertyValue
pProp(0).Name = "ActiveConnection"
pProp(0).Value = oAConnection
pProp(1).Name = "OpenMode"
pProp(1).Value = "open"
sReportName = "REPORT_NAME_HERE"
oDoc = dbForms.loadComponentFromURL(sReportName, "_blank", 8, pProp())
document = oDoc.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(document, ".uno:EditDoc", "", 0, Array())
oStyleFamilies = oDoc.getStyleFamilies()
oPage = oStyleFamilies.getByName("PageStyles")
oStandard = oPage.getByName("Standard")
oStandard.HeaderIsDynamicHeight = True
oStandard.FooterIsDynamicHeight = True
oDoc.setModified(0)
End Sub
You will notice you need to register the database and use the name in the code. You also need to fill in your report name. Another difference is this macro now needs to go in “My Macros.Standard.Module1”. This is because of the execution and the fact the .odb isn’t really open (something to access ThisDatabaseDocument or ThisComponent).
Now the only thing remaining is the actual running. With the above set as stated this works for me in terminal (Mint 18.3):
libreoffice5.4 "macro:///Standard.Module1.SetHeaderAutofit"
That will start LO and run the macro which in turn runs the report with correct Autofit settings. 
