How do I reset an advanced filter in a macro?

English
, ,
#1

OutputPosition of AdvancedFilter is set.
When I reset the filter manually via the menu, all data is copied to the output range automatically, as if i called UnoResetFilter. How can I do this without a dispatcher?

<...>
	Dim oFilterDsc As Object  'filter descriptor
	oFilterDsc = oCriteriaRange.createFilterDescriptorByObject(oDataRange)
	With oFilterDsc
		.ContainsHeader = True

		.CopyOutputData = True
		.OutputPosition = oOutputPositionCell.CellAddress
		.SaveOutputPosition = True
	End With
	oDataRange.filter(oFilterDsc)
<...>

Rem The sheet must be active. The active cell must be within the filtered range (data source).
Sub UnoResetFilter()
	Dim document As Object, dispatcher As Object
	document = ThisComponent.CurrentController.Frame
	dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
	dispatcher.executeDispatch(document, ".uno:DataFilterRemoveFilter", "", 0, Array())
End Sub

<...>
	oFilterDsc = oCriteriaRange.createFilterDescriptor(True)  'bEmpty:=True
	oDataRange.filter(oFilterDsc)
<...>

The last 2 lines reset the filter (menu item: ‘Data - More Filters - Reset Filer’ turns gray), but all data is not copied to the output range.

During testing, the filter can be applied manually. You just need to reset it programmatically, but so that all data is copied to the output range.

UPDATED:
Or does the UNO method do this work, and I have to copy the data to the output range manually?
oSheet.copyRange(oOutputPositionCell.CellAddress, oDataRange.RangeAddress)
But resetting the filter also clears the results of the previous output. This means that I will have to do this myself. Do I understand correctly?
Then it is probably easier to use the UNO method…

#2

Since you ask for easy methods, using a database would be by far the easiest.

#4

Attaching an example (data, range of advanced settings, alternative pieces of code, comments) might be a good idea!
As you did it, a helper may waste hours stabbing in the dark.

Did your oOutputPositionCell have oOutputPositionCell.Row>0 ?

#5

oOutputPositionCell is a single cell.
@Lupp, I wrote: “during testing, the filter can be applied manually . You just need to reset it programmatically, but so that all data is copied to the output range”.
The snippets demonstrate the idea, but do not need to be reproduced.

I did it differently. Previously, if criteria were not specified, I would copy all records to the output range myself and would conflict with the advanced filter, which considered it empty. After applying the filter, it did not clear the output range from previous records. An important nuance.
Then I just did not bother to intrude into the work of the advanced filter and re-connected the pivot tables to the source range if the criteria were not specified.
It turned out to be simple.

Sub ConnectPivotTables(oRangeAddress As Object)
<...>
	For Each oTable In oTables
		oTable.SourceRange = oRangeAddress
	Next

There was no need to reset the filter. An empty result is also a filter result. Re-connecting will refresh the pivot tables. And there is no need to store the second complete dataset on another sheet if criteria are not specified. That is, if the filter is applied, we connect to the output range, and if not, then to the data source. And pivot tables always display complete data. The data is sliced ​​at the input.
This is a workaround. In this case, my problem was gone.

UPDATED:
The problem arose while solving this problem.
By the way, is there any literature on creating dashboards in LibreOffice?

Excel Dashboards and Reports
Excel Dashboards and Reports1000×1000 53.2 KB

#6

Impossible to answer without any Excel dashboard in my Downloads folder.

#7

Of course.

I’m a bit tired, and trying to understand your explanations.doesn’t help with this issue. And in Dashboards I’m not too much interested.

But in the afternoon I considered the advanced-filter-by-usercode-case, and made a demo. That’s the background of my question concerning oOutputPositionCell.Row>0 (not “Rows”).
If you play with the attached (raw) toy, first select F1:K4, and run the macro. Then reload the sheet, replace N1 with N2 in G3, select again as told, and run the macro again.
Now tell me for what reason it doesn’t work in the second case.
If you judge this to be far off your problem, simply ignore this post.

tryAdvaFi.ods (12.8 KB)

(I’ll be back no sooner than tomorrow in the late afternoon.)

#9

@Lupp, I’ll be offline until Saturday. I will definitely take a look.

#3

This should remove any kind of filter from a database range:

    dbr = ThisComponent.DatabaseRanges.getByName("MyDBR")
    rg = dbr.getReferredCells()
	fdsc = rg.createFilterDescriptor(True)
	rg.filter(fdsc)

Since people never use anything with “database” in the name, variable rg can be any range object having the current filter descriptor to be overridden.

#8

t70822.ods (44.7 KB) (second version)

#10

@Villeroy, you perfectly understood the original problem and demonstrated it. After displaying all records (copyRange) by you, the advanced filter does not clear the output range before the next output and the result is incorrect.

#11

I have to apologize to the downloaders of my previous attachment. I linked the dummy data to a data source instead of making a copy. This raises an error on refresh unless you have registered my “PowerFilter” database from the other topic about linked pivot tables.
https://ask.libreoffice.org/uploads/short-url/lgqzVYxfezjGJsu0mTLT8xZgrgm.ods (second version)

Personally, I would delete the filtered data when removing the filter. To me it makes no sense having a copy of the same list.

#12

This is the code which removes the filter settings and clears the output range:

REM  *****  BASIC  *****

Sub Main
  dbr = ThisComponent.DatabaseRanges.getByName("Import1")
	if uBound(dbr.FilterDescriptor.FilterFields)= -1 then exit sub
	c = dbr.FilterDescriptor.OutputPosition
	sh = ThisComponent.Sheets(c.Sheet)
    rg = dbr.getReferredCells()
	fdsc = rg.createFilterDescriptor(True)
	rg.filter(fdsc)
	crg = getCurrentRegion(sh.getCellByPosition(c.Column, c.Row))
	crg.clearContents(1023)	
End Sub

Function getCurrentRegion(oRange)
Dim oCursor
	oCursor = oRange.getSpreadSheet.createCursorByRange(oRange)
	oCursor.collapseToCurrentRegion
	getCurrentRegion = oCursor
End Function

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