Modify component (filter) of a Base report in a macro

[greb]

Hello,

I have a report in a Base file, that I would like to filter from a macro.

As many other objects in macros, I thought that the report properties could be modified…
And indeed, I don’t know why, in the structure of the report object in the macro, the before last subvariable, Component, was not Null, but contained a set of subsubvariables, in which a filter subsubvariable.

I think this one is linked to the Filter control that we can find in the Report builder (when Analyse SQL command is on Yes).

But, this is my issue, I cannot get back this filter subsubvariable, as the Component subvariable has Null as value…

What to do to be able to manipulate the Component subvariable and filter subvariable from the macro.
(the filter control in the report builder GUI works fine, but I need to handle the filter through a macro).

Thanks in advance for any help!

(LibreOffice 7 on a Debian laptop)

[Ratslinger]

Hello,

This post appears to accomplish what you are looking for → Writing a macro to filter a report

Another approach is to actually modify the SQL (query) instead. Your Query can be read in through a macro, modified and written back to the query section before running the report. This is the filtering.

For query access through a macro, see this post → How to Modify a saved query by code

[Ratslinger]

Note: Did test macro sub openReportLong in the filter link. All worked fine with exception of edited report on screen. Inserted following code at end of routine to close that:

oCurrentController = oReport.getCurrentController()
oFrame = oCurrentController.getFrame()
oFrame.close(True)

[greb]

Thanks again Rastlinger.

Indeed, the sub openReportLong macro is also working on my side.

But I followed your advise to focus on filtering query inside the macro.
The fact is that my macro wasn’t working in the report (i don’t know why… there is UNION in, maybe it’s a problem…?). What I did is so just add an other simple query (Query_Report):

SELECT * FROM Query_Report_vTemp

and used this one in the report.

I then used the piece of macro in the post you provided above (How to Modify…).

[greb]

I just needed (but it is the right way?) to activate the ApplyFilter subvariable of stQuery:

' Get access to Queries
    oDatabaseFile = ThisComponent.Parent.CurrentController.DataSource
    oQuery = oDatabaseFile.getQueryDefinitions()
' Get access to the query needed for basis all information
    stQuery = oQuery.getByName("Query_Report")
    sSQL = stQuery.Command
' Modify the ApplyFilter
    stQuery.ApplyFilter = True
    stQuery.Filter = my_filter

Do you recommand to add a deactivation of ApplyFilter, after the opening of the filtered report?

[greb]

As well, my filter is coming from another macro.
I declare it just outside before this other macro like this:

Global my_filter As String

Is that ok, or is there a proper way to do it…?

[Ratslinger]

@greb,

If you do not ‘de-activate’ the filter as you have it set, running the query directly will include the filter giving the impression of incorrect results. You can also just add a ‘Where’ clause on and write back the original when report has run. I prefer this as it leaves a what-you-see statement (no hidden filter).

The notation of the problem with a statement including ‘Union’ may be just setting Analyze SQL command in Report Builder to “No”.

As for your filter coming from another macro, it is better to stay away from Global variables when possible. Better to pass as a parameter:

[greb]

@Ratslinger,
Thanks for all you feedbacks.

• I moved to a ‘Where’ clause option better than a de/activation and filtering.
• No change with the Analyse SQL command to “No”… But having that second query calling the first one is ok (ie. the job is done well).
• how to pass a variable from a macro to another…? Actually, I have a macro which filters a control table in a form (this macro is launched when some list controls are modified). And another macro, launched by a push button, which applies the filter to the Query used by the report, and which generates and opens the report. The fact is that the second macro (report opening) is not always called by the user (one may want to see the control table updated but not to open the report).
  That’s why I had thought of a global variable (I tried a “Public” variable, just outside before the first macro, but the variable is empty during the process of the second macro).

[Ratslinger]

@greb,

With the ‘Where’ clause you should still restore to original Select when done.

With passing variable calling Sub:

my_filter = " Where statement"
apply_filter(my_filter)

Receiving:

Sub apply_filter(SQL_filter)
    sSQL = stQuery.Command
    stQuery.Command = sSQL + SQL_filter
Rem When done reset query
    stQuery.Command = sSQL
End Sub

[greb]

@Ratslinger,
Thank you for the tips.
Indeed, I restore the ‘Where’ clause when done.
The parameter passing from one macro to another is working fine, thanks!