Call a Base report through macro in Calc

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
tmanikanda
Posts: 21
Joined: Tue May 15, 2018 9:36 am

Call a Base report through macro in Calc

Post by tmanikanda »

Hi All,
I have a report designed in Base. I want to call it from a macro that is in Calc(like to update queries i have used getquerydefinition). Any way to do it
Thanks in Advance
Apache OpenOffice 4.1.3 , Windows 10
tmanikanda
Posts: 21
Joined: Tue May 15, 2018 9:36 am

Re: Call a base report through macro in calc

Post by tmanikanda »

Hi Guys,
Please help me in this. I still haven't got clue in this

DBContext = createUnoService("com.sun.star.sdb.DatabaseContext")
COMMON_DB = DBContext.getByName("COMMON_DB")
ConnectToDB = COMMON_DB.GetConnection ("","")
ReportCtrl = ConnectToDB.ReportDocuments ' ReportCtrl = COMMON_DB.ReportDocuments

getting error. I am having a push button in the calc sheet. From this I need to print a report that is available in registered database
Apache OpenOffice 4.1.3 , Windows 10
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Call a Base report through macro in Calc

Post by RPG »

I think when you want use macros in OpenOffice then it is important to have a good knowledge of the normal working. For working with a report what is stored in a databasedocument is as far I know not a solution without a macro.

Most of the time people want open a form or a report with a button and will filter at the same time the document what they want open. For filtering this macro will not work but there are a lot of example for filtering data. For filtering your tables you have to search for a solution with normal SQL code and not with macro code. Villeroy does have given lots of examples as in the links

links for filtering
[Solved] Standard Filter data executed with Form Control box
[Solved] Macro to display SQL result in Calc

Print Report
When you want print the report a second macro is maybe better. The method loadComponentFromURL gives back an Object to the document. I think use this object for printing.


Stand-alone-forms
This macro can be used in standalone forms but also in forms which are stored in the databasedocument. But there it can be used in stand-alone-forms we assume that the databasedocument does not have a view, it is not visible on screen. The macro can also be used for old style reports as for Report Builder reports.

The macro code
The macro must be started with a button.
Points to understand
The button properties must contain the reportname which you want open. This is a hierarchical name. A hierarchicalname can contain a foldername which is part of the databasedocument.
Example: "Foldername/reportname"
The button must be part of a data-form what loads data from tables who are connected to the databasedocument in which the report is. In more technical words they must have the same connection.

Services and Interfaces
When you want write macro's for an app in OpenOffice then we have to think

Arguments for the methods
OpenOffice use real often arguments for passing information to the method you call. This arguments are some times the same as when you record a macro.

Romke

Code: Select all

sub CallForOpenReport(oEvent)
' We need this sub for a button in a standalone form
' We define here parts of the dataform
dim oButton ' Button 
dim odataForm ' This are form details

' We define here parts that we cannot see but are loaded when we 
' show data from a datasource in a doc-form.
' A doc-form contains the data-form
dim oConnection ' I think it is better to use the connection created by OpenOffice.
dim oBaseDocument ' is loaded when we see the data but not shown
dim oReports ' contains all reports
dim oReportDocument

' we use here the name data-form so we remember it is a form as
' we see in the form-navigator


oButton=oEvent.source.model

if oButton.tag = "" then
	msgbox "No report name in the button tag" & chr(13) & "This is the third from below of the property on the common tabpage"
	else ' Now we have to start to find all kind of objects
	odataForm=oButton.parent	' Each does have a connection to the database sourc
								' We use that connection. I think we must be real close to API amd normal OpenOffice
	' The connection we need for finding the database document
	oConnection=odataForm.Activeconnection
   oBaseDocument=oConnection.parent.DatabaseDocument ' We have a databasedocument what is not displayed. It is only the model
   oReports = oBaseDocument.ReportDocuments ' Goto the reportcontaine
	' Handel now the report we need
	if oReports.hasByHierarchicalName(oButton.tag)= False then
		print "wrong Name"
		 end
	end if
	dim aProp(1) as new com.sun.star.beans.PropertyValue
	aProp(0).name="ActiveConnection"
	aProp(0).value=oConnection
	aProp(1).Name = "OpenMode"
	aProp(1).Value = "open"
'	aProp(1).Value = "close"
'	aProp(1).Value = "openDesign"
'	aProp(1).Value = "preview" 'I think this is used for the databasedocument
'	aProp(1).Value = "hide" 'werkt
'	aProp(1).Value = "show" 'werkt

' The load component gives back an Object.
' You can use this object for other things.
	oReportDocument = oReports.loadComponentFromURL(oButton.tag  ,"",0,aProp())
	'oReportDocument.print(array())
end if
end sub
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
tmanikanda
Posts: 21
Joined: Tue May 15, 2018 9:36 am

Re: Call a Base report through macro in Calc

Post by tmanikanda »

Hi RPG
Thanks for your comments. I am not filtering the results in my macro. I have a report created in base based on a sql. When the user pushes the button in calc it should just open that report. I tried the below code, but facing issues
1. The openoffice application crashes most of the time. Only few times the report is getting opened successfully.
2. Every time the base application is getting opened. I just want the report to be opened.

Sub report()
DBContext = createUnoService("com.sun.star.sdb.DatabaseContext")
CALL_DB = DBContext.getByName("CALL_DB")
ConnectToDB = CALL_DB.GetConnection ("","")
url = DBContext.getDatabaseLocation("CALL_DB")
db = StarDesktop.LoadComponentFromURL(url, "_default", 0, Array())
db.CurrentController.connect()
obj = db.ReportDocuments
obj.getbyname("CallUsage").open
End Sub

Is there any other way to create a report. The user just want to view the report and take a print out if needed
Please suggest me your ideas
Apache OpenOffice 4.1.3 , Windows 10
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Call a Base report through macro in Calc

Post by RPG »

It do not understand your question. The best method to open a report is clicking on the name of the report as you see in the databasedocument. It is easy to learn this to a normal user.

When your application does not work in the way you expect then something is wrong in your application. It is better to avoid macros there macros are to difficult for most people.

Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Call a Base report through macro in Calc

Post by Villeroy »

When your application does not work in the way you expect then something is wrong in your application. It is better to avoid macros there macros are to difficult for most people.
+1

The Base related branch of the API is a nightmare.

I don't know any way to open an embedded form or report withtout opening the containing Base document first. Depending on the OS there may be ways to minimize the Base window. I don't know.

-- There are ways to convert embedded form documents into stand-alone form documents that can be called via hyperlinks or desktop links.
viewtopic.php?f=83&t=40493

-- I have seen macros which generate simple stand-alone database reports driven by macro code dumping some record set into a Writer table.
viewtopic.php?t=81229 is a rather slow macro of mine. Should be working fine with hundreds or a few thousands of values.

-- Personally, I prefer Calc as alternative report engine. Calc has sufficient formatting and layout capabilities that a well prepared spreadsheet template with cell styles, page styles, print ranges and some databsae range(s) can produce fine database reports with zero macro code or with very few macro code. Pivot tables from database records are very useful too.
viewtopic.php?f=75&t=18511 (all you can do with Calc and Base without too much coding)
The combination of forms and reports within the same tabbed spreadsheet document can be attractive too: viewtopic.php?t=88516&p=416210#p416210
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
hubert lambert
Posts: 145
Joined: Mon Jun 13, 2016 10:50 am

Re: Call a Base report through macro in Calc

Post by hubert lambert »

Hi,

This will work... if you don't use the ReportBuilder extension :

Code: Select all

    DBContext = createUnoService("com.sun.star.sdb.DatabaseContext")
    COMMON_DB = DBContext.getByName("COMMON_DB")
    ConnectToDB = COMMON_DB.GetConnection ("","")
    reports = ConnectToDB.Parent.DatabaseDocument.ReportDocuments
    report = reports.getByName("yourreport")
    report.open()
    ConnectToDB.close()
    ConnectToDB.dispose()
Regards.
AOOo 4.1.2 on Win7 | LibreOffice on various Linux systems
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Call a Base report through macro in Calc

Post by Villeroy »

report.open() throws "invalid argument" exception although the method does not take any argument. Latest LO and AOO with report builder extension.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
hubert lambert
Posts: 145
Joined: Mon Jun 13, 2016 10:50 am

Re: Call a Base report through macro in Calc

Post by hubert lambert »

hubert lambert wrote:This will work... if you don't use the ReportBuilder extension :
As I wrote.
By the way, you'll get the same error when using the reports container method "loalComponentFromURL", which is probably internally called by "report.open()" (see down page here).
AOOo 4.1.2 on Win7 | LibreOffice on various Linux systems
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Call a Base report through macro in Calc

Post by Villeroy »

I see. If you don't use the extension and if you don't use parameter queries.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
tmanikanda
Posts: 21
Joined: Tue May 15, 2018 9:36 am

Re: Call a Base report through macro in Calc

Post by tmanikanda »

Hi lambert
Thanks for the code. It did open the report as expected but with wrong data(Actually only the default template is coming. Not the actual data from base). The report is a dynamic report that will fetch data based on a query. Please let me know your thought on this.

@Villeroy
I tried to format the contents in Calc sheet like a report. But how can i print specific sheet's print ranges. I have three different sheets(three reports). defined print ranges in each sheet. based on the usage how to print them separately? If the user clicks 'Incoming usage' button then only that report needs to be printed, if the user clicks 'Outgoing usage' button then only that sheet needs to be printed. (The user will only see the buttons in home sheet. the sheets that contain data will be hidden)
Apache OpenOffice 4.1.3 , Windows 10
hubert lambert
Posts: 145
Joined: Mon Jun 13, 2016 10:50 am

Re: Call a Base report through macro in Calc

Post by hubert lambert »

You are right. It's seems finally that we can't execute a report without a controller to retrieve the report definition...
AOOo 4.1.2 on Win7 | LibreOffice on various Linux systems
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Call a Base report through macro in Calc

Post by RPG »

hubert lambert wrote:You are right. It's seems finally that we can't execute a report without a controller to retrieve the report definition...
You can open a report without a controller. I did have placed the macro code for it in this thread. But what the OP asked is much more complicated there he wants filter his data and he does understand it.

I think it is better for the OP to follow the methods of Villeroy. The method of Villeroy are for a real database or for a real spreadsheet. But I cannot explain the methods of Villeroy. I do understand them.

Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Call a Base report through macro in Calc

Post by Villeroy »

Manipulating the print areas by macro code seems to be very complicated. If you call the print dialog via File>Print, the option to print the selected sheet only is set by default. You may customize the toolbar for the document in question so the "print all" button is not availlable.
You may put the 3 reports in 3 different spreadsheet documents.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Call a Base report through macro in Calc

Post by Villeroy »

A viable, not too complicated method to print the active sheet:

Code: Select all

Sub ActiveSheet_to_NewDoc()
	sURL = ThisComponent.getURL()
	ac = ThisComponent.CurrentController.getActiveSheet()
REM adjust print ranges to the new copied sheet @ position 0
	pa = ac.getPrintAreas()
	for each addr in pa()
		addr.Sheet = 0
	next
	sLinkSheetName = ac.getName()
	oDoc = StarDesktop.loadComponentFromURL("private:factory/scalc","_default",0,Array())
	sh = oDoc.Sheets.getByIndex(0)
	REM link values only; alternative with formulas: SheetLinkMode.NORMAL
	sh.link(sURL, sLinkSheetName, "calc8", "", com.sun.star.sheet.SheetLinkMode.VALUE)
	sh.setPrintAreas(pa())
oDoc.print(Array()) ' print to default printer with default settings
oDoc.close(False)
End Sub
This requires that the source document is saved to some URL (not a new, unsaved document).

Thinking about this, you could do the same with stored documents having permanent links to the source file.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
tmanikanda
Posts: 21
Joined: Tue May 15, 2018 9:36 am

Re: Call a Base report through macro in Calc

Post by tmanikanda »

I have tried the below code(found in this forum)

Sub printreport(sSheetName1 as String)
oDoc = Thiscomponent
Dim args2(2) as New com.sun.star.beans.PropertyValue
Dim dispatcher as Object
reportSheet = oDoc.Sheets.getByName(sSheetName1)
reportSheet.IsVisible = TRUE
ThisComponent.CurrentController.ActiveSheet = reportSheet
oReport = ThisComponent.CurrentController.Frame
args2(0).Name = "Copies"
args2(0).Value = 1
args2(1).Name = "Selection"
args2(1).Value = true
args2(2).Name = "Collate"
args2(2).Value = false
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(oReport, ".uno:Print", "", 0, args2())
Msgbox("Printing")
reportSheet.IsVisible = False

End Sub
It seems to be working as i am able to save the report as PDF.
Apache OpenOffice 4.1.3 , Windows 10
tmanikanda
Posts: 21
Joined: Tue May 15, 2018 9:36 am

Re: Call a Base report through macro in Calc

Post by tmanikanda »

Hi Guys
I tried the macros to close the connection between calc and base file (to get rid of odb.lck file when using the sheet). Tried .dispose(), .close(), .flush(). .dispose seems to worked but after this I am getting disposedexception message every time even after removing dispose function. How to resolve this
Apache OpenOffice 4.1.3 , Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Call a Base report through macro in Calc

Post by Villeroy »

The lock file has nothing to do with Base. The lock file indicates that the file has been loaded for editing. It prevents that someone else can open the same file for editing. There is nothing wrong with lock files. In a normal desktop environment you don't see them.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply