[Solved] Writing a macro to filter a report

[Liamdale]

Continuing to experiement with open office report macros.

Using the example of the Openform macro by RPG, I am attempting to adapt the code to open a filtered report from a button located inside a form

The code to date is:

Code: Select all

Sub OpenInvoice(oEvent)

' declare my constants
sReportName= "rptInvoice"
sControlName="fmtInvNumber"
sInvoiceNumber= "Numéro de Facture"


' declare my Variables
dim oButton 'get the button name
dim oForm   'get the calling form name 
dim oControl 'get the invoice number control in the form to filter the report

'get the button name in the calling form
oButton=oEvent.source.model 

'get the calling form name
oform=oButton.parent

'get the invoice number control to extract value for filter
oControl=oForm.getbyname(sControlName) 

'to obtain the content use – oControl.text
' Open the Report
oReport = ThisDatabaseDocument.reportDocuments.getbyname(sReportName).open

oReportData=oReport.drawpage.reports.getbyname(sReportName)                       '-> This line does not work !!!

'filter the report

dim sFilter
sFilter= chr(34) & sInvoiceNumber & chr(34) & CHR(61)& chr(39) & oControl.TEXT & chr(39)

oReportData.filter=sFilter
oReportData.applyfilter=true
oReportData.reload

End Sub

Question: oReport.drawpage.reports.getbyname(sReportName) does not work. Is there an equivant command for reports? Can reports be filtered directly with a macro?

To date all the reading data I have gathered does not answer these questions.

The other option (much more complicated) is to load a temp table connected to the report before I open the said report.

Liamdale

[RoryOF]

Please edit your post, select the code you list and then press the Code button on top of the edit screen, so that the code listing is nicely set off from the message.

[Villeroy]

Basic is a major annoyance, particular in the context of the Base component. The report builder extension is the second worst annoyance.
The attached example database includes a form and 2 reports. In the form you define filter criteria and get a filtered and editable record set in a table control.
Query "qReport" gets the corresponding record set for the 2 reports. One report is made by the report builder extension, the simple one is made by the plain old wizard. The same filtered record set can be used in any external Writer or Calc document as well. Simply link to qReport.
All you may want is a macro-button on the form to open the report you want. Using an external Calc sheet as report, a simple file-hyperlink will do.

Edit: Glitches:

As always, you must not edit the queries having WHERE clauses looking like
WHERE ("X" = "Y" OR "Y" IS NULL) AND ("A" = "B" OR "B" IS NULL) AND (... OR ...)AND(... OR ...)
The graphical query designer spoils these queries. Always edit these queries in SQL view and close them unedited when you stumble upon them in "design view". The query designer is a huge pile of bug fixes over bug fixes but still too primitive to deal with rather simple and comprehensible queries.

Edit: 'nother glitch

The attached document contains an unused macro, a residue from an earlier version. Just ignore the macro warning. None of my office versions is able to remove a Basic module from a database. I'm too lazy to manipulate the xml container.

[RPG]

Hello

A report can be filtered but it works in an other way. First a report is a little strange. The object for a report in design mode is a complete other object then a report finished. The finished report is a textdocument and is not more important for the database.

On this moment I'm working for a macro what can print those things you ask. But I'm not sure if I have the good method. When you can get this working then be happy otherwise you have to wait until I post a solution on the same place as where you did find the first code. You have to adjust the code for your need. I have no parameter passing on this moment. There is also no testing on errors. So you have to search for the problems your self. The code I give is also for learning to understand macros. I have on this moment no English comment and I have stripped the dutch comment.

I did see Villeroy did post a more easy solution and I think it can be usefull to study his ideas. That is working without macros and you can it adjust more easy.

Romke

Code: Select all

sub openReportLong_two
' This expect that the report is already open in design mode
dim oReportsDocuments,oController
dim aReportName


dim aSubComponents
oController=thisDatabaseDocument.currentcontroller
if not  oController.isConnected() then oController.connect

aSubComponents=oController.SubComponents
for each oSubDoc in  aSubComponents()
if  oSubDoc.Caption= aReportName then
	'For a report comes back with a report definition
oSubDoc.Filter="""Names"".""PersonName"" = 'Hilda'"   
oSubDoc.parent.ReportDocuments.getbyname(oSubDoc.Caption).open
end if
next
end sub

Code: Select all

sub openReportLong
' This is opening each time the report in design mode
dim oReportsDocuments,oController
dim aReportName

dim aryProp(0) as new com.sun.star.beans.PropertyValue
aryProp(0).Name="OpenMode"

oController=thisDatabaseDocument.currentcontroller
if not  oController.isConnected() then oController.connect

aReportName="Report1"

dim oReport
' First we open the form and ask the object
aryProp(0).Value="openDesign"
oReport=oController.loadComponentWithArguments(3,aReportName,false,aryProp())
'Now we hide
'aryProp(0).Value="hide"
'oController.loadComponentWithArguments(3,aReportName,false,aryProp())

oReport.Filter="""Names"".""PersonName"" = 'Kees'"
aryProp(0).Value="open"
oController.loadComponentWithArguments(3,aReportName,false,aryProp())
end sub

[Liamdale]

Sorry RoryOF about the code. It will not happen again.

Thanks Villeroy and RPG for the input. I've downloaded the replies and will study them closely. I realize base reports are very different from what I have worked with in the past. I will keep reading and looking.

Liamdale

[Villeroy]

Base reports are Writer documents generated one way or the other. The old "classic" method creates one text table with 2 place holder rows, one for the header and one for the formatted data cells. It expands and fills with data while loading. When the report builder is installed you get more layout options and sub-reports. The data are presented in form controls.
For my personal use I prefer Calc as reporting engine. Calc allows much better calculations and charting based on dynamically resizing import ranges while providing enough layout/formatting options for my purposes. Most importantly, Calc can generate cross table aggregates (data pilots, aka pivot tables) from database row sets.
[Tutorial] Using registered datasources in Calc

[Liamdale]

Thanks Villeroy, will study the tutorial. I have solved my problem by taking a hint from RPG. I have a report linked to a permanent query which has a where clause comparing to a number in a "lookup table". The form button does four things, captures the Invoice number, deletes the contents of the "lookup table", inserts the captured number into the "lookup table" and opens the report. The deletion and data update I've tested using the SQL tool and works well. The report output is what is expected. My current problem is learning how to use SQL statements in a macro. My past experience with Access requires the SQL statement to be placed in a string variable and using a VBA command ExecuteSQL. I assume the process is similar in OOo. Looking for documentation on the matter.

Liamdale

[RoryOF]

There is some information on using SQL in Base macros in
http://www.pitonyak.org/database/AndrewBase.pdf
I am sure that this is as near canonical as possible, knowing of Andrew Pitonyak's works on the subtleties on macro programing in OpenOffice.

[Liamdale]

Subsequent to information gathered from this site I have written code which works. I am posting it for comment. Maybe someone has a more elegant way of writing it.

Code: Select all

Sub OpenInvoice(oEvent)

' declare my constants
sReportName= "rptInvoice"
sControlName="fmtInvNumber"
sInvoiceNumber= "Numéro de Facture"


' declare my Variables
dim oForm   'get the calling form name 
dim oControl 'get the invoice number control in the form to filter the report
dim iInvoiceNumber as integer

'------------------------------------------------------------------
' Get The invoice Number
'------------------------------------------------------------------
'get the calling form name
oform=oEvent.source.model.parent

'get the invoice number control to extract value for filter
oControl=oForm.getbyname(sControlName) 

'convert text into integer
iInvoiceNumber= CInt(oControl.text)

'------------------------------------------------------------------
'load the lookup table and print report
'make sure table empty
'Insert current invoice number into InvoiceNumber Table
'------------------------------------------------------------------

Dim Context
Dim DB
Dim Conn 
Dim Stmt
Dim Result
Dim deleteSQL As String
Dim insertSQL As String
Context=CreateUnoService("com.sun.star.sdb.DatabaseContext")
DB=Context.getByName("facturation")
Conn=DB.getConnection("","")

Stmt=Conn.createStatement()	
deleteSQL="delete from ""InvoiceNumber"""
Stmt.executeUpdate(deleteSQL)
insertSQL="INSERT INTO ""InvoiceNumber"" VALUES (" & iInvoiceNumber & ")"
Stmt.executeUpdate(insertSQL)

Conn.close()

'------------------------------------------------------------------
'Open report
'------------------------------------------------------------------

oReport = ThisDatabaseDocument.reportDocuments.getbyname(sReportName).open

End Sub

Thanks to everyone who has helped

Liamdale

[Villeroy]

Search this forum or the other one for keyword "executeStatement" or "executeUpdate".
Install an object inspector. I use to recommend the MRI extension. [Base]Call MRI on forms or form controls from form document
http://extensions.services.openoffice.org/ is down right now.

[arfgh]

hey there friends!. I was testing these days with the filters and was fine because the macro that filters from a listbox user selection help us to use a single form where several same forms are required paying the db file growing its size.

But the same happen with the reports. Several same building reports just with a little difference on the executed sql query asking for the ' : P arameter' to the user. So...
Is there a way to have some links on the top of one report, in order to run a macro subroutine that change the filter ? I have seen that you all were speaking to have buttons or others to filter FROM the forms, but no, i want to do it FROM the same report document. Just because we havent controls on the reports, maybe we can do it with hyperlinks ? that's the idea.

Need some help to reduce the amount of same reports documents with different parameters on the queries.

thx in advance