Dynamic report - macro to change the query

[jayArr]

LO Version: 4.4.3.2 & MAC OSX 10.10.4

I have designed a form interface to a selection of BASE reports like this:

and with this forum’s help have been able to get the form’s selection options to function.

What I want to be able to do, having generated with a macro a suitable query that replicates the users reporting options,
is to edit and change the query to the basic EXISTING designed report so that when I open it with

DoCmd.OpenReport “myExistingReportName”,2

the users reporting options e.g. Sorted in reverse chronological order, filtered between dates Date1 and Date2 will be executed.

By way of example you can do this with a form and pass a query and filter by way of arguments on the open statement.

I’d like some way of replacing that functionality with a report.

The alternative is to have 36 ( = 2 (sort) x 2 (ordered) x3 (content) x3 (filter options) ) SLIGHTLY DIFFERENT pre-designed reports which seems excessive and wasteful.

BTW

1. I know I can make the existing queries for the pre-designed reports somewhat dynamic by having parameterised queries for the Date filtering.

2. I’d be happy to have 3 reports (one for each of the different contents) so long as I could pass AT RUNTIME
   a generated query from the users selection.

What I’m asking for is the get/set macros to the internal PROPERTIES, & CONTROLS (which are hidden unlike a form) of a predesigned report; in particular it’s

• query
• sort order
• heading
• labels

so that my reports can be truly dynamic and responsive to the user demands.

[PYS]

Hi

I wrote this wiki page showing how to parameter a report with values ​​from form fields. The page is in French, but the code should be understandable. There is an example to download.

The user enters the criteria in the form, click a button to open a report that will use the fields to filter data according to criteria.

The solution presented here is to base the report on a query. The program will change the SQL query before opening the report. In this example we use the form to enter a start date and an end date. We want to run the report on the data between these dates.

The principle is as follows:

• Going from the “call” event (click on the form button) to “parent”, then to the connection then to to the query.
• The SQL query is updated to change the BETWEEN clause.
• The procedure is associated with the Execute action of the button.

[EDIT]

It is also possible to open and edit a report. I can not give an example of all that you want to modify. In the example below the label of a control is changed in the header of the detail section (replaced with time).

I advise you as a tool like Xray to find other properties.

I draw your attention to the fact that I do not guarantee the stability of this type of change. I advise you to save your database before your test. See ModifierRapports.odb

sub PysEditReport

dim Prop(2) as new com.sun.star.beans.PropertyValue
dim oConnection as object, oReports as object, oReport as object, oCtrl as object

oConnection = ThisDatabaseDocument.DataSource.getconnection("","")

Prop(0).Name = "ActiveConnection"
Prop(0).Value = oConnection
Prop(1).Name = "Hidden"
'choose true/false to open hidden or visible'
'Prop(1).Value = true'
Prop(1).Value = false
Prop(2).Name = "OpenMode"
Prop(2).Value = "openDesign"

oReports = ThisDatabaseDocument.ReportDocuments
oReport = oReports.loadComponentFromURL("Clients", "", 0, Prop())

'work on the detail section'
'for each oCtrl in oReport.Detail'

'work on the 1st "group" header. Here the detail header'
for each oCtrl in oReport.groups.getByIndex(0).header
	if oCtrl.label = "RéfClient" then
		oCtrl.label = time
	end if
next oCtrl

're-open the report visible, not design mode'
Prop(1).Value = false
Prop(2).Value = "open"

oReports.loadComponentFromURL("Clients", "", 0, Prop())

end sub

HTH. Regards

[jayArr]

Pierre-Yves, yes this does help with the dynamic SQL and works very well with my reporting - despite my complete lack of French (had to reply on Google to translate). I’m inferring from your response that access to change controls, headings, labels in a report from a macro is not possible. Is this correct?

[PYS]

It is possible to edit a report. I update my answer to attach example.

[jayArr]

Thank you again Pierre-Yves - this works really well and I’m now able to offer my users 48(!) different reports from 1(!) selection form, all fully annotated with the report’s ordering and filtering criteria. The selection form uses 3 content reports and 3 queries all 6 of which are edited by macro. This provides a great template to build on the remaining reporting I’m developing.