Create a new Report Document using a Macro

English
,
#1

Greetings everyone,

I’m coming to you with a quick, though perhaps complex question.

I am wondering if there is a method to insert a new Report document into a HSQLDB embedded database document in LibreOffice (24.2.2.2) using a Macro? What I am looking for is something similar to what Andrew Pitonyak demonstrated in his “AndrewBase” book, page 21, section 2.3.2, where he shows how to create a new Form document in a Database document using a Macro.

I attempted the same method to insert a Report document, however, unlike a Form, a Report is not a Writer document internally. A Form is a Writer document both in Design mode, and viewing mode. Whereas a Report is its own type of document, apparently a “application/vnd.sun.xml.report”, [according to “ContentType” in DatabaseDoc>ReportDocuments>getByIndex(0).ContentType], or a “com.sun.star.comp.report.OReportDefinition” according to the Report Document’s Implementation Name.

Either way, I can’t seem to find a way to create and save this type of document to the Database.

I tried just inserting a “com.sun.star.sdb.DocumentDefinition” service, which from what I understand from the OpenOffice Dev. guide, 3.1.0, Pg 1215 (1209 PDF), is supposed to insert a Form or a Report, depending on the container it is inserted into,

“The interface com.sun.star.lang.XMultiServiceFactory is used to create new forms or reports. The method createInstanceWithArguments() of XMultiServiceFactory creates a new document definition. Whether the document is a form or a report depends on the container where this object is inserted.”

Sub Main

Dim sURL

Dim sReportName

Dim oReportDocs

Dim oDocDef

Dim oConn

Dim oDB

Dim oProps(1) as new com.sun.star.beans.PropertyValue

oReportDocs = ThisComponent.getReportDocuments()

sURL = ThisComponent.URL()

oDBContext = createUnoService( "com.sun.star.sdb.DatabaseContext" )

oDB = oDBContext.getByName(sURL)

oConn = oDB.getConnection("", "")

sReportName = "New_Report2"

oProps(0).Name = "Name"

oProps(0).Value = sReportName

oProps(1).Name = "ActiveConnection"

oProps(1).Value = oConn

oDocDef = oReportDocs.createInstanceWithArguments("com.sun.star.sdb.DocumentDefinition", oProps())

oReportDocs.insertbyName(sReportName, oDocDef)

End Sub

However when I inserted the “DocumentDefinition” service, the result was a new entry as a Report without “ContentType” set, (empty string), which when opened, behaves as a Graphics document. I can create a new Report Document, opened and ready to be modified using a UNO Dispatch command (“.uno:DBNewReport”), but I can’t find a way to then save/insert it into the Database document’s storage.
I have also been able to successfully insert a new Report using the above Macro by copying an existing Report and adding the property EmbeddedObject to the array of properties. But this won’t completely be an answer if, for example there are no pre-existing reports to copy. Not to mention one would also have to wipe out all copied data to make the new Report be blank like a truly newly created one.

I am not looking to accomplish anything in particular beyond this specific request, i.e. creating a new Report Document using a Macro. Or to at least ascertain if it is even possible currently? I and a friend are working on allowing LibreOffice to be automated using AutoIt, and one of the things we are adding is modification of Reports and the like. Not that I am requesting help with the AutoIt portion of the code, simply some guidance on what needs created, and what needs to be inserted, or better yet, a Macro that can be run in Basic which would be easy to swap to work in AutoIt. What I am trying to say is I know a user can manually create a Report just as, or even easier, but if for some reason they wish to be able to automate creating a Report using AutoIt, I am hoping to be able to offer this ability.

Thank you for any guidance you may be able to offer,
Best regards,

#2

Sounds like fun… But I would not try this with the report designer, wich is often not very stable/reliable. (In composing the reports, not running actual pre-createdbreports).
.
Sorry I can’t help with your project, but can give you one hint: The report designer is an extension, wich is incorporated to LibreOffice. For OpenOffice years ago I needed to install the extension by Oracle ourselves.) So I don’t expect it integrated with an API to LO.
You will find there an adittional hint: “The Oracle Report Builder uses the Pentaho Reporting Flow Engine of Pentaho BI.”
https://extensions.openoffice.org/en/project/oracle-report-builder
.
I find it often much easier to create Writer-Documents bound to datasources for mail-merge or linking Calc-Sheets to queries for some of my customized “reports”.

2 Likes
Who destroyed base?
#3

I can certainly vouche for this, i’ve ran into several strange crashes already.

Thanks very much for the hints. I wasn’t aware Reports was merely an extension, so that would definitely explain why there seems to be a roadblock in this direction.
You have definitely helped with the project, even if explaining why it isn’t advisable to go forward in this direction.
I’ll do some research on your hints, and see what I find.
Thanks again,
Best regards

#4

Source code:
http://hg.services.openoffice.org/DEV300/file/2ebd15d9e8a6/reportbuilder - 404 Not Found

1 Like
#5

Thanks flywire, I took a look at the source code for the wizard, and, considering I don’t know a stitch of JavaScript Java, as best as I can tell, it would seem the Wizard makes a new ReportDesigner, then when it comes time to save it to the Database, it seems to store any changes to the Document Definition, then open the Report in Viewing mode, saves that as a Writer Document temporarily, creates a new DocumentDefinition service with URL Parameter set to the Temporary Writer file, and inserts that into the Database Document.
Attempting to do the same, or similar, using Basic results in a new entry being created, but it still only opens as a Writer document, both in Edit, and Viewing “modes”. I’m suspecting something isn’t quite working as it should, or else I am mixed up on how things should work. It would seem somehow the new entry should be “converted” to a ReportDefinition type from a Writer document. Which is what it seems to indicate in the OO Developer’s guide quoted in my first post.
I may just take Wanderer’s advice and drop the idea of working with the ReportDesigner to create a new Report. It is seeming to be a bit of a headache.
Thanks for the help and advice, I’m not sure whose to mark as the solution in this case, both helped immensely.
Best regards

#6

There is no solution yet so you should leave this question open. Java code is meaningless to me too although AI is starting to prove useful. Can you attach the basic code with your attempts? It would be worth uploading to https://stackoverflow.com/ too.
.
The regular LibreOffice macro sources don’t cover this part of Base:

It is worth looing through https://wiki.openoffice.org/w/images/d/d9/DevelopersGuide_OOo3.1.0.pdf#page=1187 and there might be something useful in OpenOffice.org Database Explained.
.
Perhaps someone from the community will jump in with how to manipulate Base and reports in code.

1 Like
#7

And then there is the little problem of Java not being Javascript. I’d say quite different environments.

1 Like
#8

Ah yes, I did make that mistake, didn’t I? Thanks for the correction. Yes, both are very different from what I hear. :slightly_smiling_face:

I’ll work on cleaning it up a bit and post as soon as possible. I’m not skilled in Basic so it’ll still be a bit of a mess. I’ll look at posting to Stack overflow also, thanks.
I’ve looked at Pitonyak’s “AndrewBase” document already, but could check again, it seems he didn’t cover reports at all in there (I think he wrote it before Reports was Added to OOo.). But I did find his example of creating and inserting a Form Document, which is essentially what these recent attempts look like. And essentially the same steps the Wizard seems to follow.

1 Like
#9

The report wizard is the macro which creates a report according to the given specs. A most simple report requires:

  1. Right-click on a query > Report Wizard…
  2. Select all columns.
  3. Button [Finish]
1 Like
#10

@flywire

Testing_Reports.odb (8.7 KB)
Report10.odt (10.6 KB)

Here are the two macros in the attached Report Document, Macro “Testing_1” doesn’t complete, it reports a Wrapped Target exception/ No Such Element when trying to open the Report in Viewing mode. So I took a shortcut and executed the report myself and saved it to the desktop (Report10.odt), and attempted to just insert that, as they seem to do in the Wizard, (Macro Testing_2). Macro Testing_2 completes, but the file opens a Writer Doc when selecting “Edit”, instead of the Report Designer.

If I understand correctly, good point Villeroy. Perhaps it is simpler to leave Report creation to the user, especially when dealing with incomplete functionality.

#11

I don’t understand the workflow for documents inside a Base document, especially using write documents.

Can you update the database to include a report as a basis for comparing the expected file contents with what the macro generates?

#12

Sure, I can’t edit my last post, so here is the new Database.
Testing_Reports.odb (29.4 KB)

edit: I just modified Testing_1 Macro, it now completes successfully, but the resulting report is still wrong when opened in Design mode.
edit2: Uploaded a new version of the Database with a new Macro, Testing_3, which demonstrates copying an existing Report. This results in a new, real Report that can be opened in Design mode, but it is a copy of the existing report, thus not empty, and wouldn’t work if there wasn’t an already existing report to copy.

Sub Testing_3
Dim oReportDocs As Object
Dim oRepDef As Object
Dim oDocDef As Object
Dim oConn As Object
Dim oDB As Object
Dim oProps(2) as new com.sun.star.beans.PropertyValue

Dim sURL As String
Dim sReportName As String
Dim  sDBName As String
Dim s$
Dim sRepURL As String

sReportName = "New_Report3"

oReportDocs = ThisComponent.getReportDocuments()
sURL = ThisComponent.URL()

oDBContext = createUnoService( "com.sun.star.sdb.DatabaseContext" )
oDB = oDBContext.getByName(sURL)

oConn = oDB.getConnection("", "")

If ( Not ThisDatabaseDocument.CurrentController.isConnected() ) Then
  ThisDatabaseDocument.CurrentController.connect()
End If

oRepDef = oReportDocs.getByName("Expected_Report")

oProps(0).Name = "Name"
oProps(0).Value = sReportName
 oProps(1).Name = "EmbeddedObject"
 oProps(1).Value = oRepDef
oProps(2).Name = "ActiveConnection"
oProps(2).Value = oConn

oDocDef = oReportDocs.createInstanceWithArguments("com.sun.star.sdb.DocumentDefinition", oProps()) 

oReportDocs.insertByHierarchicalName(sReportName, oDocDef)

End Sub
#13

How can you bind the placeholders to actual database fields?
.
What this macro does:

  • Creates a new report in your Base file named Table1_Report
  • Inserts a table with headers and placeholders for ID and Value1
  • You must open the report in Report Builder and bind the placeholders to actual database fields
Sub CreateReportBuilderShellForTable1
    Dim oBaseDoc As Object
    Dim oReports As Object
    Dim oNewReport As Object
    Dim oText As Object
    Dim oCursor As Object
    Dim sBaseURL As String
    Dim sReportName As String
    Dim oReportDef As Object
    Dim oProps(2) As New com.sun.star.beans.PropertyValue

    ' ---- User configuration ----
    sBaseURL = "file:///C:/path/to/your/database.odb"  ' Change this!
    sReportName = "Table1_Report"
    ' ----------------------------

    ' Open the Base document
    oBaseDoc = StarDesktop.loadComponentFromURL(sBaseURL, "_blank", 0, Array())
    If IsNull(oBaseDoc) Then
        MsgBox "Could not open Base document."
        Exit Sub
    End If

    ' Get the Reports container
    oReports = oBaseDoc.getReportDocuments()

    ' Remove existing report if it exists
    If oReports.hasByName(sReportName) Then
        oReports.removeByName(sReportName)
    End If

    ' Create a new Writer document for the report
    Dim aArgs(0) As New com.sun.star.beans.PropertyValue
    aArgs(0).Name = "Hidden"
    aArgs(0).Value = False
    oNewReport = StarDesktop.loadComponentFromURL("private:factory/swriter", "_blank", 0, aArgs())

    ' Insert a table with 2 columns (ID, Value1) and 2 rows (headers + one data row)
    oText = oNewReport.getText()
    oCursor = oText.createTextCursor()
    oText.insertString(oCursor, "Report: Table1" & Chr(13), False)
    oNewReport.Text.insertControlCharacter(oCursor, com.sun.star.text.ControlCharacter.PARAGRAPH_BREAK, False)
    Dim oTable As Object
    oTable = oNewReport.createInstance("com.sun.star.text.TextTable")
    oTable.initialize(2, 2)
    oText.insertTextContent(oCursor, oTable, False)
    ' Set headers
    oTable.getCellByName("A1").setString("ID")
    oTable.getCellByName("B1").setString("Value1")
    ' Set placeholders for database fields
    oTable.getCellByName("A2").setString("<Table1.ID>")
    oTable.getCellByName("B2").setString("<Table1.Value1>")

    ' Save the new report to a temporary location
    Dim sTempReportURL As String
    sTempReportURL = "file:///C:/path/to/temp_report.odt"  ' Change this!
    oNewReport.storeAsURL(sTempReportURL, Array())
    oNewReport.close(True)

    ' Insert the new report into the Base document
    oProps(0).Name = "Name"
    oProps(0).Value = sReportName
    oProps(1).Name = "Parent"
    oProps(1).Value = oReports
    oProps(2).Name = "URL"
    oProps(2).Value = sTempReportURL

    oReportDef = oReports.createInstanceWithArguments("com.sun.star.sdb.DocumentDefinition", oProps())
    oReports.insertByName(sReportName, oReportDef)
    oBaseDoc.store()

    MsgBox "Report '" & sReportName & "' created in Base. Open it in Report Builder to bind fields to the database."

    ' Clean up
    oBaseDoc.close(True)
End Sub

Edit:

#14

Greetings Flywire,

Thanks for the Macro.

To bind the placeholders to actual database values, you would have to create a new form and set the Datasource as the Database, in the Writer Document, and then insert a couple of Formatted Field controls into the Table cells where the placeholders are. Pitonyak demonstrates this, sort of, when he shows how to create and insert a form document into a Database document, in his “AndrewBase”, document, page 21, section 2.3.2.

I ran your Macro, and it does indeed insert the Writer Document into the Database, (I had to add a “store()” to the Database document before it closes it, otherwise the Report wasn’t saved), however the same problem I mentioned in my first post is still present, when opening the inserted Document in what is supposed to be Report Designer, it simply opens as a Writer Document still, though it is editable. If you look at the “ContentType” value of a real report and this report, you would see the Real report has a type of: “application/vnd.sun.xml.report” whereas the Writer Document report has the type of: “application/vnd.oasis.opendocument.text”

Thanks to your Macro however, I ran across the fact that I should have been including a FilterName when saving the file, as I found that the temp doc your Macro was creating wasn’t opening correctly on its own (It claimed it was damaged). This led me to tinker some more, and finally to find the solution I was hoping to find for inserting a new Report.

The solution is actually simple, I should of really clued in before. When inserting a Form Document, you create a Writer Document, because in both Design mode and viewing mode a Form is a Writer Document, however a Report is a Report Document in Design mode. This led me to create a new Database document (as if I was making an entirely new Database Doc), save it temporarily (I used a Filter name I found for Reports (I think), “StarOffice XML (Base) Report”, instead of “StarOffice XML (Base)”, but it doesn’t seem to matter, either way.) then follow the same steps in your Macro to insert it into the Database, this results in a new, completely blank Report Document. The only difference is it doesn’t have Page header/Footer enabled already, and is not connected to a DataSource yet, i.e. Table1 in this case, which is simple once you open it. Inserting a Report this way also means until you set a DataSource, you cannot open the Report in viewing mode (which would be pointless anyway), but rather only get an execution error.

I will have to write up a proper Macro in the next few days and I will post it, as an answer, I guess?

Thank you very much for your help, it has been much appreciated.

Best regards

#15

Here is a working Macro that does what I’m looking for. It’s not pretty, but works. Thanks to Flywire for the Macro template.
I’ll mark this as the solution in a few days, unless someone has any improvements they want to make.

Sub CreateNewReport
    Dim oBaseDoc As Object
    Dim oReports As Object
    Dim oNewReport As Object
    Dim oText As Object
    Dim oCursor As Object
    Dim sBaseURL As String
    Dim sReportName As String
    Dim oReportDef As Object
    Dim oProps(2) As New com.sun.star.beans.PropertyValue
    Dim sTempReportURL As String
    Dim oSimpleFileAccess
    
    ' ---- User configuration ----
	sBaseURL = "file:///C:/Users/User/Desktop/Test_Report.odb"  ' Change this!
    sReportName = "Table1_Report"
	sTempReportURL =  "file:///C:/Users/User/Desktop/temp_report.odb"  ' Change this!
    ' ----------------------------
    
    oSimpleFileAccess = createUnoService("com.sun.star.ucb.SimpleFileAccess") 
    
    REM Make sure paths exist
     If Not FileExists(sBaseURL) Then
    	MsgBox "BaseURL does not exist. " & sBaseURL
    	Exit Sub
  	End If
  	    
    ' Open the Base document
    oBaseDoc = StarDesktop.loadComponentFromURL(sBaseURL, "_blank", 0, Array())
    If IsNull(oBaseDoc) Then
        MsgBox "Could not open Base document."
        Exit Sub
    End If

    ' Get the Reports container
    oReports = oBaseDoc.getReportDocuments()

    ' Remove existing report if it exists
    If oReports.hasByName(sReportName) Then
        oReports.removeByName(sReportName)
    End If

    ' Create a new Database document for the report
    Dim aArgs(0) As New com.sun.star.beans.PropertyValue
    aArgs(0).Name = "Hidden"
    aArgs(0).Value = False
    oNewReport = StarDesktop.loadComponentFromURL("private:factory/sdatabase", "_blank", 0, aArgs())

     ' Save the new report to a temporary location
    Dim aArray(0) As New com.sun.star.beans.PropertyValue 
    aArray(0).Name = "FilterName"
    aArray(0).Value = "StarOffice XML (Base) Report"

    oNewReport.storeAsURL(sTempReportURL, aArray())
    oNewReport.close(True)

    ' Insert the new report into the Base document
    oProps(0).Name = "Name"
    oProps(0).Value = sReportName
    oProps(1).Name = "Parent"
    oProps(1).Value = oReports
    oProps(2).Name = "URL"
    oProps(2).Value = sTempReportURL

    oReportDef = oReports.createInstanceWithArguments("com.sun.star.sdb.DocumentDefinition", oProps())
    oReports.insertByName(sReportName, oReportDef)

	REM Delete the temporary file
    If  oSimpleFileAccess.exists(sTempReportURL) Then  oSimpleFileAccess.kill(sTempReportURL) 

	Dim oRepDoc As Object
	Dim sURL As String
	Dim oDBContext
	Dim oDB As Object
	Dim oConn As Object
	
	REM Make a Connection so I can open the Report.
	sURL = oBaseDoc.URL()
	oDBContext = createUnoService( "com.sun.star.sdb.DatabaseContext" )
	oDB = oDBContext.getByName(sURL)
	oConn = oDB.getConnection("", "")

	If ( Not oBaseDoc.CurrentController.isConnected() ) Then
		oBaseDoc.CurrentController.connect()
	End If
	
	REM Open the new Report in Design mode.
	oRepDoc = oReports.getByName(sReportName).openDesign()
    
 	REM Remote prevents the Add Field dialog from coming up when changing "Command" and "CommandType".
  	REM  Method found in "ReportBuilderImplementation.java" file, line 160, function "switchOffAddFieldWindow"
	oRepDoc.CurrentController.Frame.Controller.Mode = "remote"
	
	REM Set Data source stuff so Report can be opened in View mode.
	oRepDoc.CommandType = com.sun.star.sdb.CommandType.TABLE 
	oRepDoc.Command = "Table1"

	REM Save my changes to the report.
	oReports.getByName(sReportName).Store()
    oReports.getByName(sReportName).Close()
    
	REM Save my changes to the Database.
	oBaseDoc.store()
	
    oBaseDoc.close(True)
	
    MsgBox "Report named '" & sReportName & "' created in Base."

End Sub
#16

@DonH1, thank you for the interesting research you have done.
I think the following macro is more in line with the topic title (the report creation technology is the same):

' lang:en
' Creates a new report for a Base document.
' If document contains a report sReportName, it is replaced.
Sub CreateNewReport2(ByVal oBaseDoc As Object, Byval sReportName As String)
    Dim oReports As Object
    Dim oReportDef As Object
    Dim oProps(2) As New com.sun.star.beans.PropertyValue
  	    
    oReports = oBaseDoc.getReportDocuments()
    If oReports.hasByName(sReportName) Then oReports.removeByName(sReportName)

    oProps(0).Name = "Name"
    oProps(0).Value = sReportName
    oProps(1).Name = "Parent"
    oProps(1).Value = oReports
    oProps(2).Name = "URL"
    oProps(2).Value = oBaseDoc.URL

    oReportDef = oReports.createInstanceWithArguments("com.sun.star.sdb.DocumentDefinition", oProps())
    oReports.insertByName(sReportName, oReportDef)
End Sub

Sub TestCreateNewReport2()
  CreateNewReport2 ThisComponent, "Report24"
End Sub
1 Like
#17

Very nice @sokol92! I never thought of that method actually working. I will mark your answer as the solution, that works WAY faster, and is perfect. Thank you very much for the additional help.
Best regards,

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.